Software : All installed software - for specific site IDs

[hitechgreg]

I need to view software installed for a department that has multiple SITE ID locations. The default Lansweeper report "Software: All installed software" is all I need, but I need to make it for those locations.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
Count(tblSoftware.AssetID) As Total
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
Order By Total Desc

[Susan_A]

We haven't heard back from you, so we assume this issue has been resolved. Make sure you link tsysIPLocations correctly, as in the report we posted. The report we provided should actually already do what you need.

[Daniel_B]

Could you send a mail to support@lansweeper.com containing

- the link to this forum topic
- a screenshot of the Configuration\Asset Groups page
- the SQL query of your report
- the errors which you received with your report

We will follow up via e-mail then

[hitechgreg]

Thanks Daniel! I tried to add the tsysIPLocations but it kept throwing up errors.

[Daniel_B · Accepted solution]

The IP locations defined under Configuration\Asset Groups are stored in tsysIPLocations. You can add a join to this table to the Installed Software report and filter on the IP locations you are interested in:

Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Count(tblSoftware.AssetID) As Total
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  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
   tsysIPLocations.IPLocation In ('IP Location 1', 'IP Location 2')
Group By tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher
Order By Total Desc