Report: Show users in a AD group that do not have a specific software installed

[Cobra7]

First off some appologies. My SQL skills were moderate and it's been so long since I used them that I don't remember the tricks. Also if I am posting in the wrong place I appologize.

Pretty much my company is using AD groups to validate which user is licensed for Box. Now they want to use LS to find which PC's a user has logged into that does not have one or more of the 3 Box apps installed.

I created a report that can show which PC's don't have Box installed, and I can build a report with all PC's a user in the AD has logged into, but I cannot combine them. This is the closest I have. Any help would be much appreciated.

Select Top 1000000 tblADusers.Username,
tblADusers.Displayname,
tblADusers.email,
tblADGroups.Name As ADGroupName,
tblAssets.AssetUnique,
tblAssets.AssetID,
tblSoftwareUni.softwareName
From tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Inner Join tblAssets On tblADusers.Username = tblAssets.Username
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblADGroups.Name = 'BoxGen' And tblAssets.AssetID Not In
(Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Where tblSoftwareUni.softwareName Like '%Box%')
Order By tblADusers.Username,
ADGroupName

[Mister_Nobody · Accepted solution]

I think you have correct query but you used extra data

Try this

Select Top 1000000 tblADusers.Username,
tblADusers.Displayname,
tblADusers.email,
tblADGroups.Name As ADGroupName,
tblAssets.AssetUnique,
tblAssets.AssetID
From tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Inner Join tblAssets On tblADusers.Username = tblAssets.Username
Where tblADGroups.Name = 'BoxGen' And tblAssets.AssetID Not In
(Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Where tblSoftwareUni.softwareName Like '%Box%')
Order By tblADusers.Username,
ADGroupName

[Cobra7]

This worked great! Thank you very much for the help.

For anyone following, I made one more small change to filter out servers, otherwise it's the exact same:

Select Top 1000000 tblADusers.Username,
tblADusers.Displayname,
tblADusers.email,
tblADGroups.Name As ADGroupName,
tblAssets.AssetUnique,
tblAssets.AssetID
From tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Inner Join tblAssets On tblADusers.Username = tblAssets.Username
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblADGroups.Name = 'BoxGen' And tblAssets.AssetID Not In
(Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Box%') And
tblComputersystem.Domainrole = 1
Order By tblADusers.Username,
ADGroupName