Help Request: Windows PC count by IP Location

[rjs]

Hi, just getting started with Lansweeper 5 report builder, but having trouble with a report showing a Windows PC count (i.e. AD Domain member running Windows desktop O/S) for each IP location (tsysIPLocations.IPLocation).

I'd then like a second report with the same but breaking it down by PC Make and Model.

Any help appreciated.

[acantu]

Would it be possible to build a report similar to this but with Linux Machines included??

[Hemoco]

acantu wrote:
Would it be possible to build a report similar to this but with Linux Machines included??

Replace this...

Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1

... with this.

Where tblAssetCustom.State = 1 And (tblAssets.Assettype = -1 Or tblAssets.Assettype = 11)

[rjs]

Great, needed to filter the results a little further, but your input helped me resolve the syntax issues I had been having.

Many thanks.

[Hemoco · Accepted solution]

Please use one of the reports below.

Windows computer count per IP location

Select Top 1000000 tsysIPLocations.IPLocation,
Count(tblAssets.AssetID) As Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tsysIPLocations.IPLocation

Windows computer count per manufacturer/model

Select Top 1000000 tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Count(tblAssets.AssetID) As Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tblAssetCustom.Manufacturer,
tblAssetCustom.Model

Windows computer count per manufacturer/model/IP location

Select Top 1000000 tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Count(tblAssets.AssetID) As Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model