→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
WING987
Engaged Sweeper II
I am trying to create a report but I am having problems doing so.

I am looking for a total count of computers excluding servers, sorted by site, and by type (laptop or desktop) that are older than 3 years old from the current date.

This should be a simple total count:

Site 1
Laptops: 32
Desktops: 18

Site 2
Laptops: 5
Desktops: 41

and so on, for each site (based on IPLocation).

This report would help with predicting the need to replace assets based on age. I have tried and succeeded in separating desktops and laptops based on if it has a battery or not, but the instant I attempt to group them in any fashion the report fails, and the same is true when I simply attempt to count them. And I need to group these twice and get a count.

Edit:
*Assume a Windows environment.
*Assume that active directory is NOT a factor in this count as not all systems are on AD
*Assume that we do not care about serial numbers, OS, users or models
*Assume that we are using the date set in tblassets.firstseen as the age of the computer
1 REPLY 1
RCorbeil
Honored Sweeper II
Select Top 1000000
tsysIPLocations.IPLocation,
Case
When tblPortableBattery.AssetID Is Null
Then 'Desktop'
Else 'Laptop'
End As class,
Count(*) As count
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputerSystem ON tblAssets.AssetID = tblComputerSystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Where
tblAssetCustom.State = 1 -- active
And tblAssets.Assettype = -1 -- Windows
And tblComputerSystem.DomainRole <= 1 -- not server
And DateDiff(dd, tblAssets.Firstseen, GetDate()) / 365.25 >= 3
Group By
tsysIPLocations.IPLocation,
Case
When tblPortableBattery.AssetID Is Null Then 'Desktop'
Else 'Laptop'
End
Order By
tsysIPLocations.IPLocation,
Case
When tblPortableBattery.AssetID Is Null Then 'Desktop'
Else 'Laptop'
End