cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Cpappas
Engaged Sweeper
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
8 REPLIES 8
Cpappas
Engaged Sweeper
Thank you Andy. You did it.

Cpappas
Engaged Sweeper
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
Champion Sweeper III
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
Engaged Sweeper
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
Champion Sweeper III