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