view software installed by location

[mikerwolf]

Could I possibly receive a report that shows software installed, how many PC's it is installed on by IP location?

[mfoster]

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]

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]

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]

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