SQL Server – Install Date – Application & Last Service Pack

Background

It is that time of year again for our Microsoft “True Up” and we need to come clean with new Installs.

 

What is “True Up”?

Link

 

Install & Last SP Date

Let us ask each database its install date and while at it get the date the last SP was applied.

Outline

  1. Install Date
    • Install
      • When SQL Service is installed some OS Accounts are automatically provisioned, as well
        • List of OS Accounts:
          • NT SERVICE\ReportServer
          • NT SERVICE\SQLSERVERAGENT
          • NT AUTHORITY\SYSTEM
          • NT Service\MSSQLSERVER
          • NT SERVICE\Winmgmt
          • NT SERVICE\SQLWriter
  2. Last Service Pack Apply Date
    • Service Pack Applied
      • When a Service Pack is applied, new certificates are applied, as well
        • ##MS_AgentSigningCertificate##
        • ##MS_PolicySigningCertificate##
        • ##MS_SQLAuthenticatorCertificate##
        • ##MS_SQLReplicationSigningCertificate##
        • ##MS_SQLResourceSigningCertificate##
        • ##MS_SmoExtendedSigningCertificate##

 

Code


set nocount on;
set transaction isolation level read uncommitted;
go
  
; with cteServerPrincipalNT
(
      [name]
    , [create_date]
)
as
(
  
    select
  
              tblSSP.[name]
            , [create_date]
				= convert
					(
						  datetime
						, tblSSP.[create_date]
					)
  
    from   sys.server_principals tblSSP
  
    where   tblSSP.[name] in
                (
                      'NT SERVICE\ReportServer'
                    , 'NT SERVICE\SQLSERVERAGENT'
                    , 'NT AUTHORITY\SYSTEM'
                    , 'NT Service\MSSQLSERVER'
                    , 'NT SERVICE\Winmgmt'
                    , 'NT SERVICE\SQLWriter'
                )
  
)

, cteServerPrincipalCertificate
(
      [name]
    , [create_date]
)
as
(
  
    select
  
              tblSSP.[name]
            , tblSSP.[create_date]
  
    from   sys.server_principals tblSSP
  
    inner join master.sys.certificates tblSC
   
        on tblSSP.[name] = tblSC.[name]
   
)

, cteProductVersion
(
	  [productLevel]
	, [version]
)
as
(
  
    select
		  [productLevel]
				= convert
			      (
					  varchar(60)
					, serverProperty('ProductLevel')
			     )

		, [@@VERSION]
			 = @@version

)
  
select top 1
  
          [sqlInstance]
            = cast(serverproperty('servername') as sysname)

		, [productLevel]
			= ctePV.productLevel
  

		, [@@version]
			= ctePV.[version]

        , [installDate] 
            = convert(varchar(30), cteSP.[create_date], 100)
  
        , [servicePackDate] 
            = case
 
                when (

                           ( cteSPC.[create_date] > cteSP.[create_date] ) 

                        or (
								    ( ctePV.[version] not like '%RTM%') 
								and ( ctePV.productLevel != 'RTM' )
						   )
                    ) then
                        convert(varchar(30), cteSPC.[create_date], 100)
                         
                else null
 
              end

 
from   cteServerPrincipalNT cteSP
  
outer apply cteServerPrincipalCertificate cteSPC

cross apply cteProductVersion ctePV
  
order by
          cteSP.[create_date] asc

Output