Modify Built-in report to include Assetname and IPLocation

[Hogester]

Hi,

I am looking for a report, similar to the built-in Lansweeper v5 report 'Software: All Microsoft software'.

It needs to tell me how many computers at each IPLocation have each type of Microsoft Software installed. I'm not too worried about the 'Versions' of each software.

I have other reports that I have modified/created that I have used the IPLocation query with successfully, but I can't get my head around this one.

I've spent hours...

Please help me

Hogester.

[Hemoco · Accepted solution]

Please use the query below.

Select Top 1000000 tblSoftwareUni.softwareName As software,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysIPLocations.IPLocation,
Count(tblAssets.AssetID) As Installations
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tsysIPLocations.IPLocation
Having tblSoftwareUni.SoftwarePublisher Like '%microsoft%'
Order By tblSoftwareUni.softwareName,
tsysIPLocations.IPLocation