Asset power consumption

[Daniel_B]

Estimation of asset specific power consumption during normal operation.

Note: Several asset types exist in very different scales. An adaption of power consumptions may be necessary


Meets all the following criteria:
- Active asset

Sorted on:
- Estimated power consumption

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  Case When Coalesce(tblComputersystem.AssetID, '') <> '' Then Case
      When tblAssetCustom.Model Like '%virtual%' Then 20 +
      tblComputersystem.NumberOfProcessors * 10 Else Case
        When tblComputersystem.Domainrole > 1 Then 250 +
        Coalesce(tblComputersystem.NumberOfProcessors, 0) * 100 +
        Coalesce(countDiskdrives.numberDiskdrives, 0) * 10
        Else 150 + Coalesce(tblComputersystem.NumberOfProcessors, 0) *
        100 + Coalesce(countDiskdrives.numberDiskdrives, 0) * 10 End End
    Else Case tsysAssetTypes.AssetTypename When 'Monitor' Then 30
      When 'Printer' Then 40 When 'Switch' Then Case
        When Coalesce(countPorts.numberPorts, '') <> '' Then Case
          When countPorts.numberPorts > 48 Then countPorts.numberPorts * 6
          Else countPorts.numberPorts * 12 End Else 300 End
      When 'Router' Then 100 When 'Hub' Then 50 When 'Bridge' Then 50
      When 'Cable modem' Then 50 When 'DSLAM device' Then 100
      When 'xDSL router' Then 30 When 'xDSL modem' Then 30
      When 'Router' Then 100 When 'Firewall' Then 100
      When 'Network device' Then 100 When 'Network device' Then 100
      When 'NAS' Then 100 When 'SAN' Then 250 When 'Tape device' Then 20
      When 'Telephone system' Then 100 When 'Video device' Then 30
      When 'VOIP phone' Then 5 When 'Fax' Then 5 When 'Webserver' Then 150
      When 'Webserver' Then 150 When 'Wireless Access point' Then 25
      When 'Fibre switch' Then 50 When 'UPS' Then 10 When 'KVM switch' Then 10
      When 'Windows CE' Then 5 When 'Handheld' Then 5 When 'Android' Then 3
      When 'Cell phone' Then 3 When 'iPad' Then 3 When 'iPhone' Then 3
      When 'Tablet' Then 3 When 'E-reader' Then 1 When 'Badge reader' Then 5
      When 'Camera' Then 5 When 'Alarm system' Then 10
      When 'DNS server' Then 450 When 'FTP server' Then 450
      When 'Xen server' Then 450 When 'Mail server' Then 450
      When 'Proxy server' Then 450 When 'Blade server' Then 450
      When 'Terminal' Then 50 When 'VPN device' Then 100
      When 'SSL/VPN device' Then 100 When 'Environment monitor' Then 3
      When 'Remote Access Controller' Then 2 When 'Linux' Then Case
        When tblLinuxEnclosure.Manufacturer = 'No Enclosure' Then 30
        Else 200 + Coalesce(countLinuxProcessors.numberLinuxProcessors, 1) * 100
      End When 'Unix' Then Case
        When tblLinuxEnclosure.Manufacturer = 'No Enclosure' Then 30
        Else 200 + Coalesce(countLinuxProcessors.numberLinuxProcessors, 1) * 100
      End When 'Apple Mac' Then 350 When 'Windows' Then 350
      When 'Vmware server' Then 30 When 'Music system' Then 20 Else 0 End
  End As [Estim. power consumption in W]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join (Select tblFloppy.AssetID,
    Count(tblFloppy.floppyID) As numberDiskdrives
  From tblFloppy
  Group By tblFloppy.AssetID) countDiskdrives On countDiskdrives.AssetID =
    tblAssets.AssetID
  Left Join (Select tblSNMPInfo.AssetID,
    Count(tblSNMPInfo.IfIndex) As numberPorts
  From tblSNMPInfo
  Where tblSNMPInfo.IfOperstatus = 1
  Group By tblSNMPInfo.AssetID) countPorts On countPorts.AssetID =
    tblAssets.AssetID
  Left Join tblLinuxEnclosure On tblLinuxEnclosure.AssetID = tblAssets.AssetID
  Left Join (Select LinuxProcID.AssetID,
    Count(LinuxProcID.ID) As numberLinuxProcessors
  From (Select Distinct tblLinuxProcessors.AssetID,
      tblLinuxProcessors.ID
    From tblLinuxProcessors) LinuxProcID
  Group By LinuxProcID.AssetID) countLinuxProcessors
    On countLinuxProcessors.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By [Estim. power consumption in W] Desc