Notification

Icon
Error

Software per AD group - report with softwares per AD group

Posted: Tuesday, July 27, 2021 9:57:06 AM(UTC)
CPappas

CPappas

Member Original PosterPosts: 5
0
Like
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
Andy.S
#1Andy.S Member Posts: 109  
posted: 7/28/2021 9:41:43 AM(UTC)
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
CPappas
#2CPappas Member Original PosterPosts: 5  
posted: 7/28/2021 10:11:56 AM(UTC)
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.S
#3Andy.S Member Posts: 109  
posted: 7/29/2021 3:03:44 PM(UTC)
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 ?


Code:
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
#4CPappas Member Original PosterPosts: 5  
posted: 7/29/2021 4:00:40 PM(UTC)
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.S
#5Andy.S Member Posts: 109  
posted: 8/2/2021 10:10:03 AM(UTC)
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

CPappas
#6CPappas Member Original PosterPosts: 5  
posted: 8/2/2021 1:22:40 PM(UTC)
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.S
#7Andy.S Member Posts: 109  
posted: 8/2/2021 2:39:49 PM(UTC)
No Problem :-)

Give this example a try:

Code:
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
#8CPappas Member Original PosterPosts: 5  
posted: 8/3/2021 10:52:48 AM(UTC)
Thank you Andy. You did it.

Active Discussions

Lansweeper Performance from remote machines
by  Ilsa Loving   Go to last post Go to first unread
Last post: Today at 5:40:49 PM(UTC)
Lansweeper Need help with a deployment.
by  Mr Bass  
Go to last post Go to first unread
Last post: Today at 4:33:43 PM(UTC)
Lansweeper BrightSign Device
by  MRAlp   Go to last post Go to first unread
Last post: Today at 3:49:24 PM(UTC)
Lansweeper scan error on servers
by  Max90  
Go to last post Go to first unread
Last post: Today at 2:21:52 PM(UTC)
Lansweeper Ability to pause the SLA timer
by  Bernie   Go to last post Go to first unread
Last post: Yesterday at 5:44:37 AM(UTC)
Lansweeper Windows Encryption Method
by  RKCar  
Go to last post Go to first unread
Last post: 10/22/2021 8:42:13 PM(UTC)
Lansweeper Upgrade Win 10 build to version 2004
by  CyberCitizen  
Go to last post Go to first unread
Last post: 10/22/2021 12:11:55 AM(UTC)