Software per AD group

[Cpappas]

i am trying to create a report to show the software per AD Group name. I have create the below but it doesn't seem to work. I might miss something. Any help is appreciated.

Select Top 900000000 tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
count(*) As [Total Host]
From tblAssets
Inner Join lansweeperdb.dbo.tblSoftware On tblAssets.AssetID =
tblSoftware.AssetID
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
tblAssetCustom.AssetID,
lansweeperdb.dbo.tblADGroups
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher

[Cpappas]

Thank you Andy. You did it.

[Cpappas]

Sorry for the inconvenient. Let me give you an example.
I have the AD group "Group-Test", after the query i would like to have the below.

For AD group "Group-Test" :

Adobe - 35 total host on Group-Test that have Abode installed.
Teams - 20 total host on Group-Test that have Teams installed
Java- 10 total host on Group-Test that have Java installed

I hope this help you.
Thank you.

[Andy_Sismey]

No Problem 🙂

Give this example a try:

Select Top 1000000 Count(tblAssets.AssetID) As Count,
  tblADGroups.Name As ADGroupName,
  Query1.softwareName
From tblAssets
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADMembership On tblADMembership.ChildAdObjectID =
    tblADComputers.ADObjectID
  Left Join tblADGroups On tblADMembership.ParentAdObjectID =
    tblADGroups.ADObjectID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Right Join (Select tblSoftwareUni.softwareName,
        tblSoftware.AssetID
      From tblSoftwareUni
        Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID)
  Query1 On Query1.AssetID = tblAssets.AssetID
Where tblADGroups.Name = '**AD GROUP NAME**'
Group By tblADGroups.Name,
  Query1.softwareName
Order By ADGroupName

[Cpappas]

Hello,

Thank you Andy. The report is working but is not exactly what i was looking for and that is because i didn't explant correct. Let me tell you again, i want to search with AD group and get as result the list of softwares that are installed and the number of installations to this AD group. I hope now it is better to understand.
Thank you again for your time and effort on this.

[Andy_Sismey]

So for example you have an AD Group "Group-Adobe-Reader" and you want to know all the assets that have "Adobe Reader" Installed and which of these assets are in the "Group-Adobe-Reader" AD Group ?

So:

Asset Software In AD Group
PC1 - Adobe Reader - Yes
PC2 - Adobe Reader - No

[Andy_Sismey]

Hi,

Not sure but is this what your after, this will give you a list of all assets with Adobe Acrobat Reader and a count of what AD Groups they are in ?

Select Top 1000000 Count(tblAssets.AssetID) As Count,
  tblADGroups.Name As ADGroupName,
  Query1.softwareName
From tblAssets
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADMembership On tblADMembership.ChildAdObjectID =
    tblADComputers.ADObjectID
  Left Join tblADGroups On tblADMembership.ParentAdObjectID =
    tblADGroups.ADObjectID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Right Join (Select tblSoftwareUni.softwareName,
        tblSoftware.AssetID
      From tblSoftwareUni
        Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
      Where tblSoftwareUni.softwareName Like '%Adobe Acrobat Reader%') Query1 On
    Query1.AssetID = tblAssets.AssetID
Group By tblADGroups.Name,
  Query1.softwareName
Order By ADGroupName

[Cpappas]

Hello,

Thank you Andy for your reply. I want to know how many workstations on a specific AD group has the application for example flash player. Sorry if i am not explain in a proper way.

Thanks.

[Andy_Sismey]

Hi,

So what is the relationship between Software and the AD Group do you deploy your software via AD Group / GPO , So you have an AD group something like "SW-Adobe-Reader" and you want to see how many assets have Adobe Reader and are in the AD Group ?

A