cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
olipop
Engaged Sweeper II
Hi All,

I am wondering of anyone can help create a report showing the install date of a SQL server?
I am preparing for a MS audit and would like to see all new SQL servers in our environment. I can sort these by date later in the Excel report.

It would be great to include:
- Server name, OS, SQL server edition, Install Date, and if possible but not madatory CPU/Cores.

Thank You in advance!
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
In this case, use tblOperatingsystem.Installdate for the install date.

Select Top 1000000 tblState.Statename As [Lansweeper Asset State],
tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.Domain,
tblAssets.FQDN As [Machine Fully Qualified Name],
tblAssets.Lastseen As [Inventory Date],
tblOperatingsystem.Caption As OS,
tblSqlServers.serviceName As [SQL service name],
tSQLSoftware.softwareName As [SQL Version],
tblSqlServers.skuName As [SQL edition],
tblOperatingsystem.InstallDate As [Install date (OS)],
tSQLSoftware.Lastchanged As [Last software change date],
CPUCount.[Count physical CPUs],
CPUCount.[Count CPU cores],
CPUCount.[Count logical CPUs]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Left Join tblSqlServers On tblSqlServers.AssetID = tblAssets.AssetID
Inner Join (Select Top 1000000 tSQLSoftware.AssetID,
tSQLSoftware.softwareName,
tSQLSoftware.softwareVersion,
tSQLSoftware.Lastchanged,
tSQLSoftware.Installdate
From (Select Max(Case
When CharIndex('(', tblSoftwareUni.softwareName) >
0 Then SubString(tblSoftwareUni.softwareName, 1, CharIndex('(',
tblSoftwareUni.softwareName) - 1) Else tblSoftwareUni.softwareName
End) As softwarename,
tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName In ('Microsoft SQL Server 2000',
'Microsoft SQL Server 2005', 'Microsoft SQL Server 2008',
'Microsoft SQL Server 2008 R2', 'Microsoft SQL Server 2008 (64-bit)',
'Microsoft SQL Server 2008 R2 (64-bit)', 'Microsoft SQL Server 2012',
'Microsoft SQL Server 2014', 'Microsoft SQL Server 2012 (64-bit)',
'Microsoft SQL Server 2014 (64-bit)')
Group By tblSoftware.AssetID) tSQLHighest
Inner Join (Select tblSoftware.Lastchanged,
tblSoftware.Installdate,
tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID)
tSQLSoftware On tSQLHighest.AssetID = tSQLSoftware.AssetID And
Replace(tSQLHighest.softwarename, ' ', '*') = Replace(Case
When CharIndex('(', tSQLSoftware.softwareName) >
0 Then SubString(tSQLSoftware.softwareName, 1, CharIndex('(',
tSQLSoftware.softwareName) - 1) Else tSQLSoftware.softwareName
End, ' ', '*')) tSQLSoftware On tSQLSoftware.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.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) CPUCount On tblAssets.AssetID =
CPUCount.AssetID
Where tblAssets.Assettype = -1
Order By [Lansweeper Asset State],
tblAssets.AssetName,
[SQL edition]

View solution in original post

4 REPLIES 4
olipop
Engaged Sweeper II
Thank You very much for solving this, it works great! Greatly appreciated!
This community rocks!

Brgds,
Oliver P.
Daniel_B
Lansweeper Alumni
In this case, use tblOperatingsystem.Installdate for the install date.

Select Top 1000000 tblState.Statename As [Lansweeper Asset State],
tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.Domain,
tblAssets.FQDN As [Machine Fully Qualified Name],
tblAssets.Lastseen As [Inventory Date],
tblOperatingsystem.Caption As OS,
tblSqlServers.serviceName As [SQL service name],
tSQLSoftware.softwareName As [SQL Version],
tblSqlServers.skuName As [SQL edition],
tblOperatingsystem.InstallDate As [Install date (OS)],
tSQLSoftware.Lastchanged As [Last software change date],
CPUCount.[Count physical CPUs],
CPUCount.[Count CPU cores],
CPUCount.[Count logical CPUs]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Left Join tblSqlServers On tblSqlServers.AssetID = tblAssets.AssetID
Inner Join (Select Top 1000000 tSQLSoftware.AssetID,
tSQLSoftware.softwareName,
tSQLSoftware.softwareVersion,
tSQLSoftware.Lastchanged,
tSQLSoftware.Installdate
From (Select Max(Case
When CharIndex('(', tblSoftwareUni.softwareName) >
0 Then SubString(tblSoftwareUni.softwareName, 1, CharIndex('(',
tblSoftwareUni.softwareName) - 1) Else tblSoftwareUni.softwareName
End) As softwarename,
tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName In ('Microsoft SQL Server 2000',
'Microsoft SQL Server 2005', 'Microsoft SQL Server 2008',
'Microsoft SQL Server 2008 R2', 'Microsoft SQL Server 2008 (64-bit)',
'Microsoft SQL Server 2008 R2 (64-bit)', 'Microsoft SQL Server 2012',
'Microsoft SQL Server 2014', 'Microsoft SQL Server 2012 (64-bit)',
'Microsoft SQL Server 2014 (64-bit)')
Group By tblSoftware.AssetID) tSQLHighest
Inner Join (Select tblSoftware.Lastchanged,
tblSoftware.Installdate,
tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID)
tSQLSoftware On tSQLHighest.AssetID = tSQLSoftware.AssetID And
Replace(tSQLHighest.softwarename, ' ', '*') = Replace(Case
When CharIndex('(', tSQLSoftware.softwareName) >
0 Then SubString(tSQLSoftware.softwareName, 1, CharIndex('(',
tSQLSoftware.softwareName) - 1) Else tSQLSoftware.softwareName
End, ' ', '*')) tSQLSoftware On tSQLSoftware.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.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) CPUCount On tblAssets.AssetID =
CPUCount.AssetID
Where tblAssets.Assettype = -1
Order By [Lansweeper Asset State],
tblAssets.AssetName,
[SQL edition]
olipop
Engaged Sweeper II
Thank You for the reply. Unfortunatlly, the installer hasn't written to the registry since I did not get any input in the report for Install date.

I think I know how to find this info, but I don't know how to put it in the code for the report.
If there is a way to add OS Install date to this report(when the server OS was installed) then I have the answer. We always install SQL server, once the server OS is newly installed. This would mean that the SQL server was installed the same date as the Windows server, and it is always the same server.

Would this be possible? You replies are greatly appreciated.

Brgds,
Oliver P.
Daniel_B
Lansweeper Alumni
tblSoftware.Installdate contains the installation date if the installer of the software has written this information into the registry. Unfortunately this is not always the case. If this information is not available, you could only use tblSoftware.lastchanged, which contains the date when the last change on the software installation was registered by Lansweeper (this will work only back to the date when you first installed Lansweeper and successfully scanned your hosts). Please find a report below which lists relevant data for your SQL servers.

Select Top 1000000 tblState.Statename As [Lansweeper Asset State],
tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.Domain,
tblAssets.FQDN As [Machine Fully Qualified Name],
tblAssets.Lastseen As [Inventory Date],
tblOperatingsystem.Caption As OS,
tblSqlServers.serviceName As [SQL service name],
tSQLSoftware.softwareName As [SQL Version],
tblSqlServers.skuName As [SQL edition],
tSQLSoftware.Installdate As [Install date],
tSQLSoftware.Lastchanged As [Last software change date],
CPUCount.[Count physical CPUs],
CPUCount.[Count CPU cores],
CPUCount.[Count logical CPUs]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Left Join tblSqlServers On tblSqlServers.AssetID = tblAssets.AssetID
Inner Join (Select Top 1000000 tSQLSoftware.AssetID,
tSQLSoftware.softwareName,
tSQLSoftware.softwareVersion,
tSQLSoftware.Lastchanged,
tSQLSoftware.Installdate
From (Select Max(Case
When CharIndex('(', tblSoftwareUni.softwareName) >
0 Then SubString(tblSoftwareUni.softwareName, 1, CharIndex('(',
tblSoftwareUni.softwareName) - 1) Else tblSoftwareUni.softwareName
End) As softwarename,
tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName In ('Microsoft SQL Server 2000',
'Microsoft SQL Server 2005', 'Microsoft SQL Server 2008',
'Microsoft SQL Server 2008 R2', 'Microsoft SQL Server 2008 (64-bit)',
'Microsoft SQL Server 2008 R2 (64-bit)', 'Microsoft SQL Server 2012',
'Microsoft SQL Server 2014', 'Microsoft SQL Server 2012 (64-bit)',
'Microsoft SQL Server 2014 (64-bit)')
Group By tblSoftware.AssetID) tSQLHighest
Inner Join (Select tblSoftware.Lastchanged,
tblSoftware.Installdate,
tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID)
tSQLSoftware On tSQLHighest.AssetID = tSQLSoftware.AssetID And
Replace(tSQLHighest.softwarename, ' ', '*') = Replace(Case
When CharIndex('(', tSQLSoftware.softwareName) >
0 Then SubString(tSQLSoftware.softwareName, 1, CharIndex('(',
tSQLSoftware.softwareName) - 1) Else tSQLSoftware.softwareName
End, ' ', '*')) tSQLSoftware On tSQLSoftware.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.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) CPUCount On tblAssets.AssetID =
CPUCount.AssetID
Where tblAssets.Assettype = -1
Order By [Lansweeper Asset State],
tblAssets.AssetName,
[SQL edition]

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now