cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
bmbarnett
Engaged Sweeper III
Trying to find the best way (easiest) to sort a report that is sorted by asset type to keep servers together, workstations, and maybe even laptops/tablets/notebooks. I do not know of a way to create a server asset type.
1 REPLY 1
RCorbeil
Honored Sweeper II
The server or workstation class is already collected. You can use tblComputersystem.DomainRole to obtain a numeric representation of the machine's role. Link in tblDomainRoles if you want a description.
SELECT
...
Case
When tblComputersystem.Domainrole <= 1
Then 'Not server'
Else 'Server'
End As isServer,
tbldomainroles.Domainrolename, -- if you want to know more than "is/isn't server"
...
FROM
...
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
-- optional: if you want to know more than "is/isn't server"
Inner Join tbldomainroles On tbldomainroles.Domainrole = tblComputersystem.Domainrole
...

As far as identifying laptops/notebooks, you can use the chassis type, but that's a bit of a mish-mash. From my own inventory, some are identified as notebooks, some as laptops, some as docking stations... it's a bit of a mess. I found it easier to do a left join against tblPortableBattery and check whether or not there's a battery associated with the asset.
SELECT
...
Case
When tblPortableBattery.Name Is Not Null
Then 'Laptop'
Else 'Not laptop'
End As isLaptop,
TsysChassisTypes.ChassisName -- if you want to know the chassis type
...
FROM
...
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
-- optional: if you want to know the chassis type
Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
...