cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mikerwolf
Engaged Sweeper
Could I possibly receive a report that shows software installed, how many PC's it is installed on by IP location?
4 REPLIES 4
mfoster
Engaged Sweeper
I've just come across this report and found it incredibly useful. Can anyone suggest how to rework it to list by OU instead of IP Location though?
MikeMc
Champion Sweeper II
I tweaked the query to include a further breakdown by software name and version. You can change the software you're looking for on line 16.

Select Top 1000000 T1.IPLocation,
T1.softwareName As Software,
T1.softwareVersion As Version,
Count(T1.IPLocation) As SoftwareCount
From (Select Top 1000000 (Case
When tsysIPLocations.IPLocation Is Not Null Then
tsysIPLocations.IPLocation Else 'Undefined' End) As IPLocation,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Adobe%' And tblAssetCustom.State =
1) T1
Group By T1.IPLocation,
T1.softwareName,
T1.softwareVersion
Order By T1.IPLocation,
Software,
Version
mikerwolf
Engaged Sweeper
Thank you for the reply! How would I bring in the software name? Ultimately I would like a report for each of our 4 IP locations displaying the name of the software installed on the computer for that location and how many instances there are of the software for that location.
MikeMc
Champion Sweeper II
You can change what you software you are looking for on line 12.
Select Top 1000000 T1.IPLocation,
Count(T1.IPLocation) As SoftwareCount
From (Select Top 1000000 (Case
When tsysIPLocations.IPLocation Is Not Null Then
tsysIPLocations.IPLocation Else 'Undefined' End) As IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Like '%Acrobat%')
T1
Group By T1.IPLocation