Inventory summary report

[Carl527]

I'm trying to create a summary report for hardware. I'm looking for the count by IP location for desktops, laptops and servers if possible. A server can be determined by Windows Server OS if needed.
Something like:
IP Location
Desktop - # count
Laptop - # count
Servers - # count

And including 'servers' is not required if it helps.

Thanks everyone.

[Carl527]

Thanks!!! Exactly what I needed.

[Daniel_B · Accepted solution]

With a subquery you can get a count working on this. In the subquery, check tblComputersystem.Domainrole. If this is > 1, the asset is a server. If the Asset has an entry in tblPortableBattery, it likely is a laptop.

Select Top 1000000 tsysIPLocations.IPLocation,
  tType.Type,
  Count(tblAssets.AssetID) As Number
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Inner Join (Select tblComputersystem.AssetID,
    Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
      From tblPortableBattery) Then 'Laptop' Else Case
        When tblComputersystem.Domainrole > 1 Then 'Server' Else 'Desktop' End
    End As Type
  From tblAssets
    Inner Join tblComputersystem
      On tblAssets.AssetID = tblComputersystem.AssetID) tType
    On tType.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation,
  tType.Type