Microsoft CIDC Audit
Hardware Hardware ComponentsLists All Assets in a Format for the Microsoft CIDC
Get data to populate the Microsoft Clean Inventory Data Contract template, sheet Hardware and Operating Systems. The audit is meant to help people enter data into the CIDC but will not provide final data for submission. It is recommended to scan both your Active Directory and the registry keys below before running the audit.
Microsoft CIDC Query
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]