Skip to main content

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]

Comments

  1. Very good concept, but these scripts that you gave does not work?

    Regards

    ReplyDelete
  2. hmmm. what'd you find wrong with it? it seems to work fine for me.

    ReplyDelete

Post a Comment

Popular posts from this blog

using preloadpkgonsite.exe to stage compressed copies to child site distribution points

UPDATE: john marcum sent me a kind email to let me know about a problem he ran into with preloadpkgonsite.exe in the new SCCM Toolkit V2 where under certain conditions, packages will not uncompress.  if you are using the v2 toolkit, PLEASE read this blog post before proceeding.   here’s a scenario that came up on the mssms@lists.myitforum.com mailing list. when confronted with a situation of large packages and wan links, it’s generally best to get the data to the other location without going over the wire. in this case, 75gb. :/ the “how” you get the files there is really not the most important thing to worry about. once they’re there and moved to the appropriate location, preloadpkgonsite.exe is required to install the compressed source files. once done, a status message goes back to the parent server which should stop the upstream server from copying the package source files over the wan to the child site. anyway, if it’s a relatively small amount of packages, you can

How to Identify Applications Using Your Domain Controller

Problem Everyone has been through it. We've all had to retire or replace a domain controller at some point in our checkered collective experiences. While AD provides very intelligent high availability, some applications are just plain dumb. They do not observe site awareness or participate in locating a domain controller. All they want is the name or IP of one domain controller which gets hardcoded in a configuration file somewhere, deeply embedded in some file folder or setting that you are never going to find. How do you look at a DC and decide which applications might be doing it? Packet trace? Logs? Shut it down and wait for screaming? It seems very tedious and nearly impossible. Potential Solution Obviously I wouldn't even bother posting this if I hadn't run across something interesting. :) I ran across something in draftcalled Domain Controller Isolation. Since it's in draft, I don't know that it's published yet. HOWEVER, the concept is based off

sccm: content hash fails to match

back in 2008, I wrote up a little thing about how distribution manager fails to send a package to a distribution point . even though a lot of what I wrote that for was the failure of packages to get delivered to child sites, the result was pretty much the same. when the client tries to run the advertisement with an old package, the result was a failure because of content mismatch. I went through an ordeal recently capturing these exact kinds of failures and corrected quite a number of problems with these packages. the resulting blog post is my effort to capture how these problems were resolved. if nothing else, it's a basic checklist of things you can use.   DETECTION status messages take a look at your status messages. this has to be the easiest way to determine where these problems exist. unfortunately, it requires that a client is already experiencing problems. there are client logs you can examine as well such as cas, but I wasn't even sure I was going to have enough m