→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎12-06-2023 09:52 PM
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
Solved! Go to Solution.
‎12-07-2023 05:53 AM
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
‎12-07-2023 05:53 AM
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
‎12-07-2023 03:24 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now