Microsoft CIDC Audit

Select Top 1000000 tblState.Statename As [Lansweeper Asset State],
  tblAssets.AssetName As [Lansweeper Assetname],
  tblAssets.Domain,
  Null As [Server Farm Name],
  Null As [Cluster Fully Qualified Name],
  Coalesce(Case When tHyperVHost.FQDN = '' Then Null Else tHyperVHost.FQDN
  End, Case When tVMwareHost.FQDN = '' Then Null Else tVMwareHost.FQDN End, Case
    When tblAssets.FQDN = '' Then Null Else tblAssets.FQDN
  End, 'not scanned') As [Physical Machine Fully Qualified Name],
  tblAssets.Username As [Primary User],
  tblAssetCustom.Manufacturer As [Machine Manufacturer],
  tblAssetCustom.Model As [Machine Model],
  tBIOS.[BIOS serialnumber] As [BIOS Serial Number],
  tBIOS.ReleaseDate As [BIOS Release Date],
  Coalesce(CPUCountHyperV.[Count physical CPUs],
  CPUCountVMware.[Count physical CPUs], Case
    When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then Null
    Else CPUCount.[Count physical CPUs]
  End, 0) As [Physical Processor Total Count],
  Coalesce(tHyperVHost.Processor, tVMwareHost.Processor, Case
    When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then Null
    Else tblAssets.Processor End, '') As [Processor Model],
  Coalesce(CPUCountHyperV.[Count CPU cores], CPUCountVMware.[Count CPU cores],
  Case When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then Null
    Else CPUCount.[Count CPU cores] End, 0) As [Physical Cores Total Count],
  Case
    When Coalesce(tblAssetCustom.Manufacturer, '') =
    '' Then 'Unknown/Not scanned' Else Case
      When tblAssetCustom.Manufacturer Like '%vmware%' Or
      tblAssetCustom.Model Like '%virtual%' Or
      tblAssetCustom.Serialnumber Like '%virtual%' Then 'Virtual' Else Case
        When Exists(Select tblVmwareGuest.AssetID From tblVmwareGuest
        Where tblVmwareGuest.AssetID = tblAssets.AssetID) Or
        Exists(Select tblHyperVGuest.AssetID From tblHyperVGuest
        Where tblHyperVGuest.AssetID = tblAssets.AssetID) Then 'Host'
        Else 'Physical' End End End As [Machine Type Virtualization],
  Case When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then Case
      When Coalesce(tblAssets.FQDN, '') <> '' Then tblAssets.FQDN
      Else 'not scanned' End Else ''
  End As [Virtual Machine Fully Qualified Name],
  Case When tblAssetCustom.Manufacturer Like '%vmware%' Or
    tblAssetCustom.Model Like '%virtual%' Or
    tblAssetCustom.Serialnumber Like '%virtual%' Then
    CPUCount.[Count logical CPUs] Else Null
  End As [Virtual Processor Count Total],
  'virtual CPU' As [Virtual Processor Naming Type],
  tblAssets.Lastseen As [Inventory Date],
  tblOperatingsystem.InstallDate As [Install Date],
  Case
    When Coalesce(tRegistryEdition.Value, '') <> '' Then 'Windows ' +
    Replace(Case
      When SubString(tRegistryEdition.Value, Len(tRegistryEdition.Value),
      Len(tRegistryEdition.Value)) = 'N' Then SubString(tRegistryEdition.Value,
      1, Len(tRegistryEdition.Value) - 1) Else tRegistryEdition.Value
    End, 'Server', 'Server - ') Else '* ' + tblOperatingsystem.Caption
  End As [Operating System Family Name],
  Case
    When tRegistryEdition.Value Like 'server%' Then Case tRegistryVersion.Value
      When '5.2' Then '2003' When '6.0' Then '2008'
      When '6.1' Then '2008 Release 2' When '6.2' Then '2012'
      When '6.3' Then '2012 Release 2' Else tRegistryVersion.Value End
    When Coalesce(tRegistryEdition.Value, '') <> '' Then Case
      tRegistryVersion.Value When '5.0' Then '2000' When '5.1' Then 'XP'
      When '5.2' Then 'XP' When '6.0' Then 'Vista' When '6.1' Then '7'
      When '6.2' Then '8' When '6.3' Then '8.1' Else tRegistryVersion.Value End
    Else Case tRegistryVersion.Value When '5.0' Then '2000' When '5.1' Then 'XP'
      Else 'no registry value' End End As [Operating System Version Name],
  Null As [Installation Media Chanel],
  Case When tblOperatingsystem.SerialNumber Like '%OEM%' Then 'yes'
    Else 'no/unknown' End As [OEM install confirmed via BIOS],
  Null As [Licensing Product Family Name],
  Null As [Licensing Product Version Name],
  Null As [License Quantity Required],
  Null As [Active SA Quantity Required],
  Null As [Active SA Assigned],
  Null As [License Program Group Assigned],
  Null As [License Model Assigned],
  Null As [Environment Type],
  Null As [External Connector Licensing Required],
  Null As [License Mobility Flag],
  tblAssetCustom.Department As Division,
  'Lansweeper' As [Primary Discovery Tool],
  Null As Notes
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join (Select Max(tblBIOS.Lastchanged) As lastchanged,
    tblBIOS.Caption As Description,
    tblBIOS.Manufacturer,
    tblBIOS.ReleaseDate,
    tblBIOS.SerialNumber As [BIOS serialnumber],
    tblBIOS.SMBIOSBIOSVersion As Version,
    tblBIOS.AssetID
  From tblBIOS
  Group By tblBIOS.Caption,
    tblBIOS.Manufacturer,
    tblBIOS.ReleaseDate,
    tblBIOS.SerialNumber,
    tblBIOS.SMBIOSBIOSVersion,
    tblBIOS.AssetID) tBIOS On tblAssets.AssetID = tBIOS.AssetID
  Inner Join tblState On tblAssetCustom.State = tblState.State
  Left Join (Select tblProcessor.AssetID,
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Count logical CPUs],
    Sum(tblProcessor.NumberOfCores) As [Count CPU cores],
    Count(tblProcessor.Caption) As [Count physical CPUs]
  From tblProcessor
  Group By tblProcessor.AssetID) CPUCount On tblAssets.AssetID =
    CPUCount.AssetID
  Left Join (Select Case
      When Len(TblHyperVGuestNetwork.MacAddress) < 4 Then '***'
      Else TblHyperVGuestNetwork.MacAddress End As MacAddress,
    TblHyperVGuestNetwork.HyperVGuestID
  From TblHyperVGuestNetwork) tHyperVGuestNetwork
    On tHyperVGuestNetwork.MacAddress = tblAssets.Mac
  Left Join tblHyperVGuest On tHyperVGuestNetwork.HyperVGuestID =
    tblHyperVGuest.hypervguestID
  Left Join tblAssets tHyperVHost On tHyperVHost.AssetID =
    tblHyperVGuest.AssetID
  Left Join (Select tblProcessor.AssetID,
    Sum(tblProcessor.NumberOfLogicalProcessors) As [Count logical CPUs],
    Sum(tblProcessor.NumberOfCores) As [Count CPU cores],
    Count(tblProcessor.Caption) As [Count physical CPUs]
  From tblProcessor
  Group By tblProcessor.AssetID) CPUCountHyperV On tHyperVHost.AssetID =
    CPUCountHyperV.AssetID
  Left Join (Select tblVmwareGuestNetwork.GuestID,
    Case When Len(tblVmwareGuestNetwork.MacAddress) < 4 Then '***'
      Else tblVmwareGuestNetwork.MacAddress End As MacAddress
  From tblVmwareGuestNetwork) tVMwareguestnetwork
    On tVMwareguestnetwork.MacAddress = tblAssets.Mac
  Left Join tblVmwareGuest On tVMwareguestnetwork.GuestID =
    tblVmwareGuest.GuestID
  Left Join tblAssets tVMwareHost On tVMwareHost.AssetID =
    tblVmwareGuest.AssetID
  Left Join (Select tblVmwareInfo.AssetID,
    tblVmwareInfo.numCpuThreads As [Count logical CPUs],
    tblVmwareInfo.numCpuCores As [Count CPU cores],
    tblVmwareInfo.numCpuPkgs As [Count physical CPUs]
  From tblVmwareInfo) CPUCountVMware On tVMwareHost.AssetID =
    CPUCountVMware.AssetID
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like '%\Windows NT\CurrentVersion' And
    tblRegistry.Valuename Like 'CurrentVersion') tRegistryVersion
    On tRegistryVersion.AssetID = tblAssets.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like '%\Windows NT\CurrentVersion' And
    tblRegistry.Valuename Like 'EditionID') tRegistryEdition
    On tRegistryEdition.AssetID = tblAssets.AssetID
Where tblAssets.Assettype = -1
Order By [Lansweeper Asset State],
  [Lansweeper Assetname]