→ 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: 
Hogester
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Hemoco
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now