ADM-Get-Assoc

#   ----------------------------------------------------------------------------------------------------
#   This script will let you pull a list of applications in App-V based on who has access to them.
#   The list is of applications, not packages, because individual applications can have different
#   rights.
#
#   The script will output the name of the application and the AD-Group the permissions came from.
#   Which can be handy when you know a user has rights, but aren't sure which group conveys it.
#
#   Usage is straightforward. This script is provided as is, and it intended for testing purposes only,
#   do not use in a production environment, do not use if you don't know what it is doing. And lost
#   data or damage caused as a result of using this script is your responsibility, and yours alone.
#
#   Don't forget to update the $srv and $db vars to reflect your actual environment, and check the
#   AD Group filter near the bottom.
#   ----------------------------------------------------------------------------------------------------


Import-Module ActiveDirectory

function Get-Assoc($strGroup) {
    # Setup SQL query and values.
    $srv = "<sqlserver>"
    $db = "AppVDB"
    $conTime = 30
    $qryTime = 120
    $query = "SELECT dbo.APPLICATIONS.name,dbo.APPLICATION_ASSIGNMENTS.group_ref FROM dbo.APPLICATIONS INNER JOIN dbo.APPLICATION_ASSIGNMENTS ON dbo.APPLICATIONS.app_id = dbo.APPLICATION_ASSIGNMENTS.app_id WHERE group_ref ='$strGroup'"

    # Setup and open SQL connection.
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conStr = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $srv,$db,$conTime
    $conn.ConnectionString = $conStr
    $conn.Open()

    # Setup SQL command.
    $cmd = New-Object System.Data.SqlClient.SqlCommand($query, $conn)
    $cmd.CommandTimeout = $qryStr

    # Execute SL comand, adapt results to System.Data.DataSet and close connection.
    $ds = New-Object System.Data.DataSet
    $da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.Fill($ds)
    $conn.Close()
    foreach($i in $ds.Tables)
    {
        foreach($row in $i)
        {
            "Application: {0}`r`n--Assignment: {1}`r`n" -f $row.name,(Get-ADGroup $row.group_ref).SamAccountName
        }
    }
}
while($mode -ne 1 -and $mode -ne 2) { $mode; $mode = Read-Host "[1]: Find Users Software`r`n[2]: Find Groups Software`r`nSelection" }

if($mode -eq "1")
{
    $user = Read-Host "User to search for"
    $groups = Get-ADUser $user -Properties MemberOf
    "Results:"
    foreach($i in $groups.MemberOf){
        # Check to make sure the group name matches corp. convention. Remove this check if it
        # does not apply to you. This is done to only use software distribution groups and
        # ignore things like security groups, etc. Saves time and can clean up the output.
        if($i.Contains("USa_") -eq $true){
            $long = (Get-ADGroup -Identity "$i").SID
            Get-Assoc $long
        }
    }
} elseif($mode -eq "2") {
    $search = Read-Host "AD Group to search for"
    $long = (Get-ADGroup $search).SID
    "Results:"
    Get-Assoc $long
}