SQL Server: Install Date

[olipop]

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!

[olipop]

Thank You very much for solving this, it works great! Greatly appreciated!
This community rocks!

Brgds,
Oliver P.

[Daniel_B · Accepted solution]

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]

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]

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]