Number of software installed

[MR1371]

I'm trying to figure out a report that shows a certain amount of software installed (in my case 1) based on the IPaddress location.

So the report will show 3 PC's with a certain IP adress location that have 1 specific type of software installed that all the other PC's in that IP address location do not have.

[Daniel_B · Accepted solution]

The following report uses two subqueries. One counts the number of assets per location and the other one counts the number of software installations per location. The report lists software installations which are not present on all machines.

Select Top 1000000 tSoftwarePerLocation.IPLocation,
  tSoftwarePerLocation.softwareName,
  tSoftwarePerLocation.SoftwarePublisher,
  tSoftwarePerLocation.Number
From (Select Count(tblAssets.AssetID) As numberAssets,
    tsysIPLocations.IPLocation
  From tblAssets
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tsysIPLocations
      On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
      tsysIPLocations.EndIP
  Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
  Group By tsysIPLocations.IPLocation) tAssetsPerLocation
  Inner Join (Select Count(tblSoftware.AssetID) As Number,
    tblSoftwareUni.softwareName,
    tblSoftwareUni.SoftwarePublisher,
    tsysIPLocations.IPLocation
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
    Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
    Inner Join tsysIPLocations
      On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
      tsysIPLocations.EndIP
  Group By tblSoftwareUni.softwareName,
    tblSoftwareUni.SoftwarePublisher,
    tsysIPLocations.IPLocation) tSoftwarePerLocation
    On tSoftwarePerLocation.IPLocation = tAssetsPerLocation.IPLocation
Where tAssetsPerLocation.numberAssets > tSoftwarePerLocation.Number
Order By tSoftwarePerLocation.IPLocation,
  tSoftwarePerLocation.softwareName

[MR1371]

I explained this terribly, my apologies. I want to have a report that displays PC's that have a software installed on them that no other PC's have in that specific location. Sort of a crude method to see if some PC's have any suspect software installed on them.

[Daniel_B]

IP locations are stored in tsysIPlocations. You can add IP locations to a report by following the instructions on this post. If you only would like to list certain software installations, you need to use a subquery to only list relevant entries from tblSoftware and tblSoftwareUni. Please find an example below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tSoftware.softwareName,
  tSoftware.softwareVersion
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Inner Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName In ('Lansweeper',
    'Microsoft SQL Server 2012')) tSoftware On tSoftware.AssetID =
    tblAssets.AssetID
Where tblAssetCustom.State = 1 And tsysIPLocations.IPLocation =
  'Example IP location'