‎09-13-2016 10:12 PM
Solved! Go to Solution.
‎09-15-2016 09:06 PM
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
‎08-14-2017 04:53 PM
‎09-16-2016 08:15 PM
‎09-15-2016 09:06 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now