List of software by IPLocation

[sstegh]

I've found the report called "List of Software by Computer" and I'd like to try to create something similar, I just don't know how...

I need a report that lists all installed software by IP Location. We have 30+ locations and this would help a lot when tracking down software that isn't suppose to be at a particular location. I've never used the report builder before so any help would be appreciated.

[Esben_D · Accepted solution]

It is always good to look for built in report you can modify. Using the report you mentioned, we added the tsysIPLocations table and removed some asset fields since those are not needed. Then we put a count on tblSoftware.AssetID to count the amount of times this software has been installed and group the other fields. The only thing left to do is specify your name of the IPLocation. You can do this by replacing the highlighted code in the SQL syntax below.

Instructions for adding this report to your Lansweeper installation can be found here: https://www.lansweeper.com/Forum/yaf_postst9882_How-to-run-a-report.aspx#post38309
If you are interested in building or modifying reports, we do recommend:
Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial: https://www.w3schools.com/sql/
Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here: https://www.lansweeper.com/Forum/yaf_postst9870_Lansweeper-database-dictionary.aspx#post38296

Select Top 1000000 tsysIPLocations.IPLocation,
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 tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
Having tsysIPLocations.IPLocation = 'MyIPLocation'
Order By tsysIPLocations.IPLocation,
Software