→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
hitechgreg
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
Susan_A
Lansweeper Alumni
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
Lansweeper Alumni
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
Engaged Sweeper III
Thanks Daniel! I tried to add the tsysIPLocations but it kept throwing up errors.
Daniel_B
Lansweeper Alumni
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