Problems with report creation

[S_Kunimünch]

Hello everyone,

I am currently creating a report that should show me all users who are not in a specific AD group and have a specific program installed. Unfortunately, I'm not that good at SQL, so I just can't get it right.
Here's what I've got so far

Select Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.email,
  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 tblSoftwareUni.softwareName Like 'XXX Software%' And
  tblADGroups.Name <> 'XXX Group'
Group By tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.email,
  tblSoftwareUni.softwareName
Order By tblADusers.Userdomain,
  tblADusers.Username

If I do that, all users who have installed the software are displayed to me.

Does anyone have an idea where I have to adjust something to make it work.

Thanks in advance for your help

Sascha

[Mister_Nobody · Accepted solution]

query for local groups

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.OScode,
tblAssets.Userdomain,
tblAssets.Username,
tblAssets.Lastseen,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select tblUsersInGroup.AssetID
From tblUsersInGroup
Where tblUsersInGroup.Groupname = N'%Configuration Manager') And
tblSoftwareUni.softwareName Like '%firefox%'
Order By tblAssets.AssetName

[Mister_Nobody]

P.S. the query does not take nested groups into account

[S_Kunimünch]

OK, thats a good and useful information. Is it possible with nested groups?

[Mister_Nobody]

You have to create subquery to remove group membershipping.

Example of such type query (Win 7 without KB2871997):

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.OScode,
F.lastUpdateDate
From tblAssets
Left Join (Select tblQuickFixEngineering.AssetID,
Max(tblQuickFixEngineering.Lastchanged) lastUpdateDate
From tblQuickFixEngineering
Group By tblQuickFixEngineering.AssetID) F On tblAssets.AssetID = F.AssetID
Where tblAssets.OScode = '6.1.7601' And
'KB2871997' Not In (Select tblQuickFixEngineeringUni.HotFixID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On
tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineering.AssetID = tblAssets.AssetID)
Order By tblAssets.Domain,
tblAssets.AssetName

[S_Kunimünch]

I tested it immediately, unfortunately the same result. The problem is that there are other groups among the users, so the result for each group is still displayed to me.

Here a screenshot of what I mean.

Bye, Sascha

[Mister_Nobody · Accepted solution]

query for local groups

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.OScode,
tblAssets.Userdomain,
tblAssets.Username,
tblAssets.Lastseen,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select tblUsersInGroup.AssetID
From tblUsersInGroup
Where tblUsersInGroup.Groupname = N'%Configuration Manager') And
tblSoftwareUni.softwareName Like '%firefox%'
Order By tblAssets.AssetName

[S_Kunimünch · Accepted solution]

Awesome, thanks, that helped me a lot. I have now converted this to the AD groups and to username, not asset and I already have everything I need.
Here my code.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.OScode,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.Username Not In (Select tblADusers.Username
    From tblADusers Left Join tblADMembership On tblADMembership.ChildAdObjectID
          = tblADusers.ADObjectID Left Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
    Where tblADGroups.Name Like 'AD_Group_Name' Group By
      tblADusers.Username,
      tblADusers.Firstname,
      tblADusers.Lastname) And tblSoftwareUni.softwareName Like '%firefox%'
Order By tblAssets.AssetName

Bye, Sascha 

[Mister_Nobody]

One more query for AD users

Select Top 1000000 tblADusers.Userdomain,
tblADusers.Username,
tblADusers.Name,
tblADusers.OU,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.OScode,
tblSoftwareUni.softwareName,
tblAssets.Lastseen
From tblADusers
Inner Join tblAssets On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Domain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%yandex%' And
tblADusers.ADObjectID Not In (Select tblADMembership.ChildAdObjectID
From tblADMembership Inner Join tblADGroups On tblADGroups.ADObjectID =
tblADMembership.ParentAdObjectID
Where tblADGroups.Name = 'domain users')