Get Server Lifecycle Info from MS Support into a table

As part of database life cycle management process organizations ensure database is patched and kept up to date to ensure database system is secured from any vulnerabilities and to stay in support life cycle.

Microsoft Support Life cycle provides a consistent and predictable guidelines for product support availability when a product releases and throughout that product’s life. By understanding the product support available, customers are will be able to maximize the management of their IT investments and strategically plan for a successful IT future.

To know the current status of Microsft life cycle is not easy, MS provides an Excel file that contains all the build versions together with their current support life cycle stage for 2005 through the current version is available.(please note as we speak MS ended support for 2005)

So put to together a powershell script that will load the excel file into SQL Server table.

  1. First step download the excel file.
  2. Second create a table and procedure in any Database to hold the SQL Lifecycle data
  3. load the Excel file into the Table.

Link is available on this page.(https://support.microsoft.com/en-us/kb/321185) (http://download.microsoft.com/download/7/C/1/7C1BA242-14B5-48AE-9A99-7CE4FE9DAAF9/SQL%20Server%20Builds%20V3.xlsx)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SQLServerBuilds](
	[Version] [varchar](100) NOT NULL,
	[BuildNumber] [varchar](100) NOT NULL,
	[KBNumber] [varchar](100) NOT NULL,
	[ServicePackRTM] [varchar](100) NULL,
	[ReleaseDate] [date] NULL,
	[UpdateNumber] [varchar](100) NULL,
	[IncrementalServicing] [varchar](100) NULL,
	[IncludeSecurity] [varchar](1000) NULL,
	[LifeCycleStatus] [varchar](100) NULL,
	[Notes] [varchar](max) NULL,
	[EOS] [date] NULL,
	[inserted_on] [datetime] NOT NULL,
	[inserted_by] [varchar](100) NOT NULL,
 CONSTRAINT [PK_SQLServerBuilds] PRIMARY KEY CLUSTERED 
(
	[Version] ASC,
	[BuildNumber] ASC,
	[KBNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SQLServerBuilds] ADD  CONSTRAINT [DF_SQLServerBuilds_inserted_on]  DEFAULT (getdate()) FOR [inserted_on]
GO

ALTER TABLE [dbo].[SQLServerBuilds] ADD  CONSTRAINT [DF_SQLServerBuilds_inserted_by]  DEFAULT (suser_sname()) FOR [inserted_by]
GO

  Create  procedure [dbo].[wmsp_CUSQLServerBuilds] (
  @version varchar(100)
  ,@buildNumber varchar(100)
  ,@Kbnumber varchar(100)
  ,@ServicePackRTM varchar(100)
  ,@ReleaseDate varchar(100)
  ,@UpdateNumber varchar(100)
  ,@IncrementalServicing varchar(100)
  ,@IncludeSecurity varchar(1000)
  ,@LifeCycleStatus Varchar(100)
  ,@Notes varchar(100)
  )
  as
  begin 
  declare @eos date;
  set @ReleaseDate = case when  PATINDEX('%[0-9]%',@ReleaseDate)  = 1 and PATINDEX('%[A-Z]%',@ReleaseDate) = 0 then dateadd(dd,convert(int,@ReleaseDate),'12/31/1899 00:00:00') else @ReleaseDate end
  set @eos = case when  PATINDEX('%[0-9]%',@Notes)  = 1 and PATINDEX('%[A-Z]%',@Notes) = 0 then dateadd(dd,convert(int,@Notes),'12/31/1899 00:00:00') else null end
  if not  exists (select 1 from SQLServerBuilds where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber)
  Begin 
  Insert into SQLServerBuilds(
       [version]
       ,[BuildNumber]
      ,[KBNumber]
      ,[ServicePackRTM]
      ,[ReleaseDate]
      ,[UpdateNumber]
      ,[IncrementalServicing]
      ,[IncludeSecurity]
      ,[LifeCycleStatus]
      ,[Notes]
      ,EOS)
   Values (
   @version
   ,@buildNumber 
  ,@Kbnumber 
  ,@ServicePackRTM 
  ,@ReleaseDate 
  ,@UpdateNumber 
  ,@IncrementalServicing 
  ,@IncludeSecurity 
  ,@LifeCycleStatus 
  ,@Notes
  ,@eos
  )
  end 
  else 
  begin 
  
  update SQLServerBuilds
  set LifeCycleStatus= @LifeCycleStatus
  ,UpdateNumber = @UpdateNumber
  ,IncrementalServicing = @IncrementalServicing
  ,IncludeSecurity = @IncludeSecurity
  ,Notes =  @Notes
  ,ReleaseDate = @ReleaseDate
  ,ServicePackRTM = @ServicePackRTM
  ,eos = @eos
 where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber 
  end 
  
  end    

 

Powershell Script to load the excel

 $srv = "Server Name"
$Dbname = "Database Name"
$filelocation = "Excel File Location"

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$cscn = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srv 


$files = Get-ChildItem $filelocation *.xlsx

foreach ($file in $files) { 
 Get-ExcelSheetInfo $file.FullName  | ForEach-Object {
           $Sheet =$_.Name; 
                  $SQLserver = Import-Excel $file.FullName -Sheet $Sheet; ForEach ($SQL in $SQLserver) {
                       $Buldnumber = $SQL.'Build number'; 
                        $kbnumber = $SQL.'KB number';  
                        $lifecycle = if($SQL.'Lifecycle stage' -EQ $null){$SQL.'Lifecycle status (Service Pack Support End Date) '}else{$SQL.'Lifecycle stage'}	
                        $Updatenumber = $SQL.'Cumulative Update number/Security ID'; 
                        $IncrementalServicing = $SQL.'Incremental Servicing Model';
                        $inlcudeSecurity = $SQL.'Includes latest security fixes?';
	                    [String]$notes =  $SQL.'Notes regarding Lifecycle stage'+$SQL.'Notes regarding Lifecycle stage/ Service Pack support end date'+$SQL.'Lifecycle status (Service Pack Support End Date) ';
	                    if($notes -ne $null){ $notes=$notes.replace("'","''")}
                        $releasedate =  $SQL.'Release Date';
	                    $servicepack = $SQL.'Service Pack/RTM';
                     if($Buldnumber -NE $null){ 
                            $cscn.Databases[$Dbname].ExecuteNonQuery("exec wmsp_cuSQLServerBuilds '$Sheet','$Buldnumber','$kbnumber','$servicepack','$releasedate','$Updatenumber','$IncrementalServicing','$inlcudeSecurity','$lifecycle','$notes'")}
                     }
        }

 

Final Result

sqlserverbuilds

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s