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

Action Powershell Remote Shutdown with Different Creds
by  Wealthyreltub   Go to last post Go to first unread
Last post: 9/14/2021 9:43:09 PM(UTC)
Action Find lost space the easy way (spacesniffer.exe)
by  tomscott2340  
Go to last post Go to first unread
Last post: 9/1/2021 8:22:48 PM(UTC)
Action Remote Uninstaller
by  hirogen   Go to last post Go to first unread
Last post: 7/15/2021 2:09:47 PM(UTC)
Lansweeper uVNC Portable (trying to replace LSRemote)
by  CyberCitizen  
Go to last post Go to first unread
Last post: 6/15/2021 11:40:21 PM(UTC)
Action Password Status
by  steveb   Go to last post Go to first unread
Last post: 5/20/2021 5:24:13 PM(UTC)
Lansweeper Verify a successful copy to user computer
by  Brandon  
Go to last post Go to first unread
Last post: 4/22/2021 9:09:50 PM(UTC)
Lansweeper Pablo
by  Pablo   Go to last post Go to first unread
Last post: 4/20/2021 8:05:07 PM(UTC)
Lansweeper Can Actions work when using when using a Ubuntu machine
by  mzipperer  
Go to last post Go to first unread
Last post: 4/1/2021 10:16:34 PM(UTC)