cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
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]
0 REPLIES 0