advanced audit report, match active directory group

[svap]

hi

with 5.1 its possible to use active directory data in reports which ist one of the best news 2014 so far, thanks lansweeper!

we have groups for visio and project at the user object in active directory. (GLOBAL-AG-Visio)
we would like to match our report which lists approved software (visio/project) to that group to find out unapproved installs. (visio installed but no group member...)

please help, give example for group usage in reports

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Userdomain,
tblSoftwareUni.softwareName,
tblAssets.Lastseen,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Company,
tblAssets.Description
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where (tblSoftwareUni.softwareName = 'Microsoft Project Professional 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Project Professional 2013' Or
tblSoftwareUni.softwareName = 'Microsoft Project Standard 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Premium 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Standard 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Professional 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Professional 2013')
And tblAssets.Lastseen > GetDate() - 30
Order By tblAssets.AssetUnique

br, pete

[Hemoco · Accepted solution]

A good way to check the membership of different AD groups is to use a subquery per group:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Userdomain,
tblSoftwareUni.softwareName,
tblAssets.Lastseen,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Company,
tblAssets.Description,
VisioMember.Visio,
ProjectMember.Project
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join (Select Top 1000000 tblADMembership.ChildAdObjectID,
tblADGroups.Name As Visio
From tblADGroups
Inner Join tblADMembership On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'VISIO-GROUP') VisioMember
On tblADusers.ADObjectID = VisioMember.ChildAdObjectID
Left Join (Select Top 1000000 tblADMembership.ChildAdObjectID,
tblADGroups.Name As Project
From tblADGroups
Inner Join tblADMembership On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'PROJECT-GROUP') ProjectMember
On tblADusers.ADObjectID = ProjectMember.ChildAdObjectID
Where (tblSoftwareUni.softwareName = 'Microsoft Project Professional 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Project Professional 2013' Or
tblSoftwareUni.softwareName = 'Microsoft Project Standard 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Premium 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Standard 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Professional 2010' Or
tblSoftwareUni.softwareName = 'Microsoft Visio Professional 2013')
And tblAssets.Lastseen > GetDate() - 30
Order By tblAssets.AssetUnique