Report for total number of active assets.

[jbryant]

I have spent almost the entirety of my day searching the Lansweeper forums for any information on getting a total count of all assets. By "all assets", that strictly includes just Workstations, Laptops, Servers, Domain Controllers and Printers. I just need a report that will be able to email monthly automatically (I got that part figured out), and just say that the total number of active assets is, for example, 2,555. Right now I can get that number from looking at the dynamic groups I have setup and adding them together manually, but we need a report to be emailed automatically every month. Any help would be greatly appreciated.

[MikeMiller]

I am looking for a report just like this one, but i cant seem to get it to work. Is there an updated version of this report?

[jbryant]

Mike, I wish I could give you 5 thumbs up! This worked terrifically!!

Very much appreciated,
James

[MikeMc · Accepted solution]

This report will show the total number of workstations, laptops, servers, domain controllers, and printers.

Select Sum(T2.AssetTotal) As AssetTotal
From (Select Top 1000000 'Workstations' As AssetType,
    Sum(T1.AssetCount) As AssetTotal
  From tblAssets
    Inner Join (Select Max(tblSystemEnclosure.AssetID) As AssetID,
      Count(tblSystemEnclosure.ChassisTypes) As AssetCount
    From tblSystemEnclosure
      Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
        tblSystemEnclosure.ChassisTypes
      Inner Join tblAssetCustom On tblAssetCustom.AssetID =
        tblSystemEnclosure.AssetID
      Inner Join tblComputersystem On tblComputersystem.AssetID =
        tblSystemEnclosure.AssetID
    Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2 And
      TsysChassisTypes.ChassisName <> 'Laptop' And
      TsysChassisTypes.ChassisName <> 'Portable' And
      TsysChassisTypes.ChassisName <> 'Notebook' And
      TsysChassisTypes.ChassisName <> 'Docking Station'
    Group By tblSystemEnclosure.ChassisTypes) T1 On T1.AssetID =
      tblAssets.AssetID
  Union All
  Select Top 1000000 'Laptops' As AssetType,
    Sum(T1.AssetCount) As AssetTotal
  From tblAssets
    Inner Join (Select Max(tblSystemEnclosure.AssetID) As AssetID,
      Count(tblSystemEnclosure.ChassisTypes) As AssetCount
    From tblSystemEnclosure
      Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
        tblSystemEnclosure.ChassisTypes
      Inner Join tblAssetCustom On tblAssetCustom.AssetID =
        tblSystemEnclosure.AssetID
    Where tblAssetCustom.State = 1 And (TsysChassisTypes.ChassisName = 'Laptop'
        Or TsysChassisTypes.ChassisName = 'Portable' Or
        TsysChassisTypes.ChassisName = 'Notebook')
    Group By tblSystemEnclosure.ChassisTypes) T1 On T1.AssetID =
      tblAssets.AssetID
  Union All
  Select Top 1000000 'Servers' As AssetType,
    Sum(T1.AssetCount) As AssetTotal
  From tblAssets
    Inner Join (Select tblComputersystem.AssetID,
      Count(tblComputersystem.AssetID) As AssetCount
    From tblComputersystem
      Inner Join tblAssetCustom On tblAssetCustom.AssetID =
        tblComputersystem.AssetID
    Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole = 3
    Group By tblComputersystem.AssetID) T1 On T1.AssetID = tblAssets.AssetID
  Union All
  Select Top 1000000 'Domain Controllers' As AssetType,
    Sum(T1.AssetCount) As AssetTotal
  From tblAssets
    Inner Join (Select tblComputersystem.AssetID,
      Count(tblComputersystem.AssetID) As AssetCount
    From tblComputersystem
      Inner Join tblAssetCustom On tblAssetCustom.AssetID =
        tblComputersystem.AssetID
    Where tblAssetCustom.State = 1 And (tblComputersystem.Domainrole = 4 Or
        tblComputersystem.Domainrole = 5)
    Group By tblComputersystem.AssetID) T1 On T1.AssetID = tblAssets.AssetID
  Union All
  Select Top 1000000 'Printers' As AssetType,
    Sum(T1.AssetCount) As AssetTotal
  From tblAssets
    Inner Join (Select Max(tblAssets.AssetID) As AssetID,
      Count(tblAssets.AssetID) As AssetCount
    From tblAssets
      Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
      Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
        tblAssets.Assettype
    Where tblAssetCustom.State = 1 And tsysAssetTypes.AssetTypename = 'Printer'
    Group By tblAssets.AssetID) T1 On T1.AssetID = tblAssets.AssetID) T2