Certificates, get expiration date, and import into SQL database.


Some weeks ago, a responsible for my team ask me to lead a project to collect some specific information from all windows servers in the company. That specific information is to collect in windows servers would have certificates and when they will expire. He need a process, to collect and import that information into a SQL Database, and create a process to warning every week, which certificates expire in 180 days or less.

The process I’ll describe, is the script to collect the information of the certificate expiration.

The following script, is the most important step of this process. The script is a Batch file. Why? because the multiple versions of operating systems involved.

This process will check if server have IIS role. If dont have IIS, it will create a file with the hostname.txt with 0 kb.

If have IIS role, it will be check the certificates present in that server, and create a file whit the hostname.txt with the necessary information.

######################### Begin Script #########################

echo off

cd\

cd %systemdrive%

IF exist temp/nul ( echo temp exists ) ELSE ( mkdir TEMP && echo temp created)

cd temp

IF exist CERTF/nul ( echo CERTF exists ) ELSE ( mkdir CERTF && echo CERTF created)

cd CERTF

del *.txt

FOR /F “tokens=1,2,3,4” %%A IN (‘SC.EXE QUERY W3SVC ^| FIND /I “STATE”‘) DO SET STATE=%%D

IF /I “%STATE%”==”RUNNING” (

GOTO CERTLIST

) ELSE GOTO SEMIIS

:CERTLIST

certutil -store my | findstr /i ” Subject: ” >> issuer.txt

certutil -store my | findstr /i ” NotAfter: ” >> notafter.txt

setlocal EnableDelayedExpansion

< issuer.txt (

for /F “delims=” %%a in (notafter.txt) do (

set /P line1=

echo !line1!;%%a

)

) > %computername%.txt

ren %computername%.txt %computername%-old.txt

set h=%computername%;

for /f “tokens=* delims= ” %%a in (%computername%-old.txt) do (echo %h%%%a% >> temp.txt)

del %computername%-old.txt

ren temp.txt %computername%.txt

forfiles /M Issuer.txt /C “cmd /c If @fsize==0 copy /y NUL %computername%.txt >NUL

GOTO FTP

:SEMIIS

copy /y NUL %computername%.txt >NUL

Rem Until now, the process is done. From here is to send to a central location. In my case i’m sending to a FTP server

GOTO FTP

:FTP

del issuer.txt

del notafter.txt

@ftp -i -s:”%~f0″

open SERVER_IP

USERNAME

PASSWORD

cd REMOTE_FOLDER

mput *.txt

quit

goto END

:END

######################### End Script #########################

If you have some tools, which you can manage all your servers, like PSEXEC, you can create a schedule task, to run in all your servers.

After this, in the SQL server you want to import that information, create a task to download from the FTP Server, and compile all files into one. Do not forget to remove some garbage, like certificates Web Management Service, or others that you don’t needed. After you download your files, to your SQL server, you can compile into one file all your information;

Open a Command line into your folder which have all files, and wrote;

type * >> c:\FOLDER-PATH\temp.txt

After that run the following PowerShell script;

######################### Begin Script #########################

(Get-Content “c:\ FOLDER-PATH \temp\temp.txt”) -notmatch “WMSvc” -notmatch “localhost” | out-file c:\ FOLDER-PATH \import.txt

(gc c:\FOLDER-PATH \import.txt) -replace ‘;Subject: CN=’,’;’ -replace ‘; Subject: CN=’,’;’ -replace ‘NotAfter: ‘,’ ‘ -replace ‘; ‘,’;’ -replace ‘; ‘,’;’ | out-file c:\ FOLDER-PATH \import.txt

Get-ChildItem c:\ FOLDER-PATH \servers\ -Recurse | Select-Object -Property Name | out-file c:\ FOLDER-PATH \HOSTNAMES.txt

(gc c:\ FOLDER-PATH\HOSTNAMES.txt) -replace ‘—- ‘,” -replace ‘Name’,” -replace ‘.txt’,” | out-file c:\ FOLDER-PATH \HOSTNAMES.txt

######################### End Script #########################

You need to create two folders, TEMP and Servers into your Folder path.

After this, you have your file ready to be import into your SQL server, and have this appearance.

sql

 

 

 

 

At this moment, you only need to create process to be alerted in SQL server by creating a routine/SQL Script to alert weekly, 180 days from now you will have some certificates expired.

This process was created, because the client have more than 3000 windows servers :), and that is a lot of servers to check, and remember.

That’s it, enjoy it.

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