→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
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]
5 REPLIES 5
Mukesh
Engaged Sweeper
Hi,


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

Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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
Engaged Sweeper
This is awesome! Thanks for creating this! Do you guys have any plans to create queries for sheets B-F of the CIDC?