→ 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: 
rjs
Engaged Sweeper
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.

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

View solution in original post

4 REPLIES 4
acantu
Engaged Sweeper
Would it be possible to build a report similar to this but with Linux Machines included??
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

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

Try Now