sms: serial numbers with warranty expiration...

here's something fun to try over the holiday season. marry up serial numbers that you're already collecting from your dell systems and join them to expiration warranty data so that you can see when your systems will go out of warranty. anyway, i'm sure someone very versed in sql scripting can come up with something better than what i've illustrated. i'm using stock scripts from query analyzer. remember, this is completely unsupported by microsoft. :) there are a few things you're going to need to get this started:

1. a csv containing serial numbers and warranty
2. a new table to hold the information
3. a view for the new table
4. a method to get the information into the table
5. a report to look at all the new data

for the csv, you're going to need to get this data from dell (or from whatever manufacturer you use). basically, you'll want the format to look like this:

ABC1234,12/04/2009
ABC2234,12/04/2008
...

on your sms server, create a new table. if you're going to use my script to push the data in, then create one like this:

column name         data type
--------------      -------------
SerialNumber        nvarchar(50)
ExpirationDate      smalldatetime 

 

note, in the above, you do not want to allow nulls. you also want to set the primary key on SerialNumber since theoretically it should be unique. you could also use this little sql script to create it.

 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO

CREATE TABLE [dbo].[DellWarranty]([SerialNumber] [nvarchar](50) 
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
[ExpirationDate] [smalldatetime] NOT NULL, 
CONSTRAINT [PK_DellWarranty] 
PRIMARY KEY CLUSTERED ( [SerialNumber] ASC )WITH 
(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] 

 

now that we have a table to work with, we'll create a view so that things are like the way sms does it. here's a sql script to run that will create a view called v_R_DellWarranty:

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE VIEW [dbo].[v_R_DellWarranty] AS 
SELECT SerialNumber, ExpirationDate FROM dbo.DellWarranty

 

once your table and view is setup, apply the proper permission. i've outlined it in this blog post: sms: adjust permissions when using new tables... let's get the data into the table. once you have your .csv file ready, rename it to serials.csv and place it in c:\temp. why? because i'm lazy and haven't updated the script.

modify the following values so that you can connect to the sms server:

• "Server=SMSSERVER;" &_
• "Database=SMSDB;" &_
• Set oTextFile = oFSO.OpenTextFile("C:\temp\serials.csv", 1) - this is the file and location

here's the script you're going to need:

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTextFile = oFSO.OpenTextFile("C:\temp\serials.csv", 1)

Set oConnection = CreateObject("ADODB.Connection")
oConnection.ConnectionString = _
    "Driver={SQL Server};" &_
    "Server=<YOUR SMS SERVER>;" &_ 
    "Database=<YOUR SMS DATABASE>;" &_
    "Trusted_Connection=yes;"

oConnection.Open

Do While oTextFile.AtEndOfStream <> True
    sLine = oTextFile.ReadLine
    If inStr(sLine, ",") Then
        aSerialRecord = Split(sLine, ",")
        sQuery =    "INSERT INTO DellWarranty(SerialNumber,ExpirationDate) " &_
                    "VALUES('" & Trim(aSerialRecord(0)) & "','" & Trim(aSerialRecord(1)) & "')"
        oConnection.Execute sQuery
      End If
Loop

oConnection.Close

 

once you run that, you'll have values populated into your new table. here's a sql query that you can create a sms report with. it's just a small example of how to use this new data:

 

select SYS.Name0 AS [System], CS.Model0 AS [Model], SerialNumber0 AS [Serial],
       Convert(CHAR(11),ExpirationDate,111) AS [Expiration]
from   v_R_System SYS INNER JOIN
       v_GS_System_Enclosure SE ON SYS.ResourceID = SE.ResourceID INNER JOIN
       v_GS_Computer_System CS ON SYS.ResourceID = CS.ResourceID INNER JOIN
       v_R_DellWarranty DW ON SE.SerialNumber0 = DW.SerialNumber 
order by [System]