Microsoft SAM / CIDC - Hardware and Operating Systems

[Daniel_B]

This report lists all assets which were scanned by Lansweeper in the format which is required by the Microsoft CIDC (Clean Inventory Data Contract) template, sheet Hardware and Operating Systems.

License related data are not filled into this report. It only lists scanned hardware and operating system configurations.

Before executing the report, it is recommended to perform a full IP range scan and Scheduled computer scans for all Active Directory OU's which contain computer accounts. Be sure to meet the scanning requirements for Windows domain or Windows workgroup networks.

In order to list the OS version and edition according to the Microsoft WorkSpace taxonomy, Custom Registry scanning needs to be set up for the following two values:
- Key HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion, Value CurrentVersion
- Key HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion, Value EditionID
After setting this up, rescan all your Windows computers.

Note: This report is meant to assist in Microsoft audits or help preparing these, not necessarily to provide final data for the audit.

Meets the following criteria:
- Windows asset

Sorted on:
- Asset state, Asset name

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]

[Mukesh]

Hi,


Please can you provide me configure step, for how can apply this code

[Daniel_B]

Slightly modified the first post in order to use the Microsoft WorkSpace taxonomy for product family names and product version names. This requires setting up custom registry scanning.

[keys_it]

Would have loved to have this last year in February. We had a SAM audit and this would have cut down on me creating manual reports to get some of the info we needed. Keep up the great work Lansweeper!

[Daniel_B]

We are looking at this. The report is a bit long because it needs to be compatible to SQL compact and with the report editor.

[snappeddragon]

This is awesome! Thanks for creating this! Do you guys have any plans to create queries for sheets B-F of the CIDC?