VBScript: Alert system for certificates that will expire in 'n' days.

One day, an individual was called at... oh lets say 3 in the morning, because some certificates which were not set to auto renew and the outlook reminder was never set to tell him or her that the certificates were going to expire.


This could have been avoided by preparing a scheduled task to help alert the Computer System Technician of the upcoming demise and prompt them to take action 5, 10, 30, 60 days before the certs expired. 

I did a bit of research to help the person out... no it was not me called at 3am, thankfully! 

Breaking the VBScript into parts helps out a lot... portability is key when it comes to any scripting language.  I do not have any formal education when it comes to programming, however I do have a lot of confidence in two things... Trial and Error... and Google.

One part of this overall task is the alerting piece.... yes, standard email will do the trick.  I already had something I had used this for anyhow and its a pretty standard piece of code that sys admins should have readily available.  The below code is derived from http://www.paulsadowski.com/wsh/cdo.htm

Sub EMAIL(mailto,txtbody)
    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "Certificates are expiring soon..."
    objMessage.From = "CertificateAuthorityExpirations@work.com"
    objMessage.To = mailto
    objMessage.TextBody = txtbody

    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "exchange.server.dot.com"

    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    objMessage.Configuration.Fields.Update
    objMessage.Send
END SUB

The next part is derived from http://blogs.msdn.com/b/spatdsg/archive/2007/07/19/notify-users-of-cert-expiration.aspx  and modified to meet my needs...  so you can add any arguments you want like number of days to check for... also maybe who you want the email to go to.  Like I mentioned before portability is a sys admins best friend.  Along with documentation... but... like I said, I don't have any formal programming education, so that's my excuse for not commenting my code enough ;)

Const CV_OUT_BASE64 = &H1
Const DUMMY_CERT = "modifiable cert to exclude"  '<== I used this part to skip common certs that all desktops use, because they auto renew anyways and nothing will go down if they are expired

' Setup arguments if there are any....
SET Args = WScript.Arguments

If WScript.Arguments.Count = 1 Then    ' This part can be modified to something hard coded
    mailto = Args.Item(0)                         ' definitely up to you though.
    limit = 60
else
    limit = 60   ' <-- number of days to check for...
    mailto = "system_admin@work.com"
End if
'Log it...
Set objFSO = CreateObject("Scripting.FileSystemObject")
SET writeFile = objFSO.OpenTextFile("Certificates.log", 2, True)  '<-- just creates a new log every time... you can set it to append if you want.

'THIS IS THE <Machinename>\CAName
CAName = "domain_controller.work.int\WORK"     '=======>> CHANGE THIS TO THE CORRECT MACHINE\CA==

'create the CAView object
set oCAView = CreateObject("CertificateAuthority.View")


'open the connection to the Machine\CA
oCAView.OpenConnection (CAName)

'retrieve specific columns from DB  (This part took me awhile to figure out the field names...)
oCAView.SetResultColumnCount(4)
Index0 = oCAView.GetColumnIndex(False, "CommonName") 
Index1 = oCAView.GetColumnIndex(False, "NotAfter")
Index2 = oCAView.GetColumnIndex(False, "SerialNumber")
Index3 = oCAView.GetColumnIndex(False, "CertificateTemplate")
oCAView.SetResultColumn (Index0)
oCAView.SetResultColumn (Index1)
oCAView.SetResultColumn (Index2)
oCAView.SetResultColumn (Index3)
'open the view
Set RowObj= oCAView.OpenView

txtbody = ""

on error resume next
Do Until RowObj.Next = -1

   Set ColObj = RowObj.EnumCertViewColumn()
        x = ""
        Do Until ColObj.Next = -1
            x = x + Cstr(ColObj.GetValue(CV_OUT_BASE64))+","
        Loop
        if Left(x, 1) <> " " then
            items = split(x,",")
            Machine = items(0)
            Expires = items(1)
            Subject = items(2)
            Info = items(3)
            if Info <> DUMMY_CERT then
                edate = cdate(Expires)
                odate = Date
                blarg = DateDiff("y", edate, odate)
                if blarg < 0 then
                    if abs(blarg) < limit then
                        difference = Abs(DateDiff("y", edate, odate)) & " Days until it expires!"
                        writeFile.writeline(Machine & "," & difference & ",SerialNumber: " & Subject & ",CertificateTemplate: " & Info)
                        txtbody = txtbody + "Certificate for: "& Machine & "      Has Only: " & difference & vbcrlf & vbcrlf & _
                        "CA Details;" & vbcrlf & "SerialNumber: " & Subject & vbcrlf & "CertificateTemplate: " & Info & _
                        vbcrlf & "=======================" & vbcrlf & vbcrlf
                    end if
                end if
            end if
        end if
  Set ColObj = Nothing

Loop
call EMAIL(mailto,txtbody)   '<-- this is the SUB i mentioned earlier... cdo send method
writeFile.Close

There is a lot going on it there, but its not hard to pick out what needs to change.  The domain controller
I found from the command line using the certutil exe found in system32...  I don't recall the actual command, but I'm sure its not hard to find on the msdn site...

Hopefully this has been informative and takes you all less time than it took me ;)

Happy testing/fixing/administering