Microsoft SAM / CIDC - Instance level details for SQL Servers

[Daniel_B]

This report lists all SQL server installations which were scanned by Lansweeper in the format which is required by the Microsoft CIDC (Clean Inventory Data Contract) template, sheet Instance level details for SQL Servers.

License related data are not filled into this report.

Please note that in order to scan SQL server details, you need Lansweeper Professional or higher edition. 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.

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,
  Coalesce(Case When tblAssets.FQDN = '' Then Null Else tblAssets.FQDN
  End, 'not scanned') As [Machine Fully Qualified Name],
  tblAssets.Lastseen As [Inventory Date],
  tInstallDate.InstallDate As [Install date],
  Replace(tblSqlServers.serviceName, 'MSSQL$', '') As [Instance name],
  'SQL Server - ' + Replace(Replace(tblSqlServers.skuName, ' (64-bit)', ''),
  ' Edition', '') As [Inventory Product Family Name],
  Case When SubString(tblSqlServers.version, 1, 1) = '8' Then '2000'
    When SubString(tblSqlServers.version, 1, 1) = '9' Then '2005'
    When SubString(tblSqlServers.version, 1, 2) = '10' And
    SubString(tblSqlServers.version, 4, 1) <> '5' Then '2008'
    When SubString(tblSqlServers.version, 1, 2) = '10' And
    SubString(tblSqlServers.version, 4, 1) = '5' Then '2008 Release 2'
    When SubString(tblSqlServers.version, 1, 2) = '11' Then '2012'
    When SubString(tblSqlServers.version, 1, 2) = '12' Then '2014'
    Else 'Unknown' End As [Inventory Product Version Name],
  'Yes' As [SQL Server Engine],
  Null As [SQL Server Reporting Services],
  Null As [SQL Server Analysis Services],
  Null As [SQL Server Integration Services],
  Null As [SQL Server - Other Components],
  Null As [Installation Media Chanel],
  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 [SQL Database Engine Service State],
  Null As [External Connector Licensing Required],
  Null As [SQL Virtual Cluster Name],
  Null As Division,
  Null As Notes
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join (Select tblSoftware.AssetID,
    Min(tblSoftware.Installdate) As InstallDate
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like '%sql server 20%'
  Group By tblSoftware.AssetID) tInstallDate On tInstallDate.AssetID =
    tblAssets.AssetID
Where tblAssets.Assettype = -1
Order By [Lansweeper Asset State],
  [Lansweeper Assetname]