'Save_Parsed_Print_Log_to_MDB.vbs
'Version 1.1
'This script will scan through a list of Print Servers and extract successful print job events to 
' an Access MDB. It saves the insertion strings into separate fields in the MDB. This enables you to 
' identify heavily used printers and the users printing to them.
'Author. David Byrne 12/03/03

'Email david.byrne@tafensw.edu.au or dbyrne2000@bigpond.com
'
'Requirements:
'1. MS Access 2000 Database called printlogs.mdb with the following structure:
'       Name		Type	Size
'	RecordNumber	Long 	Integer	4
'	Server		Text	20
'	evDateTime	Date/	Time	8
'	PageCount	Long 	Integer	4
'	docSize		Long 	Integer	4
'	portstr		Text	20
'	prtnamestr	Text	20
'	userstr		Text	20
'	eventno		Text	10
'	docstr		Text	200
' Also has an unique index of Server and Recordnumber.

'2. WMI/WBEM on the servers to be queried - if NT4 then WMI must be installed
'3. Admin rights on the servers.

Dim catNewDB, Database
on error resume next
dim eventstr(10)
'MDB path - can be modified
strDBPath="d:\printlogs.mdb"
'create ADO connection
Set catNewDB = CreateObject("ADOX.Catalog")
Set Database = CreateObject("ADODB.Connection")
'Text File for printservers - location and name can be modified
infile="d:\printservers.txt"
set fsomain=createobject("scripting.filesystemobject")
set machines=fsomain.opentextfile(infile)
Database.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath)
recx=0
Do until machines.AtEndOfLine
  strcomputer=machines.readline
  'This section finds the max recordnumber for the current server to limit the recordset to recordnumbers above it.
  sql = "SELECT PrintLogs.Server, Max(PrintLogs.RecordNumber) AS MaxOfRecordNumber FROM PrintLogs GROUP BY PrintLogs.Server HAVING (((PrintLogs.Server)='"&strcomputer&"'))"
  Set rs = Database.Execute(sql)
  maxrec=rs("MaxOfRecordNumber")
  Set wbemServices =nothing
  Set wbemObjectSet = nothing 
  Set wbemServices = GetObject("winmgmts:\\" &strComputer& "\root\cimv2")
  Set wbemObjectSet = wbemServices.ExecQuery("SELECT * FROM Win32_NTLogEvent where Logfile='System' and SourceName='Print' and RecordNumber>'"&maxrec&"' and EventCode='10'")
  For Each wbemObject In wbemObjectSet
    recx=recx+1
    x=0    
    'Loads InsertionStrings into array
    If Not IsNull(wbemObject.InsertionStrings) Then
      For Each istr in wbemObject.InsertionStrings
        eventstr(x)=istr
        x=x+1
      Next
    End If
    ' formats event date into Access format
    NewDate = FormatDMTFDate(wbemObject.TimeGenerated)
    'Writes new record
    sqlinsert="INSERT INTO PrintLogs ( RecordNumber, Server, evDateTime, PageCount, docSize, portstr, prtnamestr, userstr, eventno, docstr ) "& _
    "VALUES("&wbemObject.RecordNumber&",'"&strComputer&"',#"&NewDate&"#,"&eventstr(6)&","&eventstr(5)&",'"&eventstr(4)&"','"&eventstr(3)&"','"&eventstr(2)&"','"&eventstr(0)&"','"&eventstr(1)&"')"
    Set rs = Database.Execute(sqlinsert) 
  Next
loop
DataBase.Close
Set catNewDB = Nothing
Set Database = Nothing
set rs=nothing
Set wbemServices =nothing
Set wbemObjectSet = nothing
wscript.echo "Finished Print Log Scan"&vbcrlf&recx&" new records added to "&strDBPath&"."
wscript.quit

'=========================================================================
' Function: FormatDMTFDate(sDate)
' Author:   Charles-Henri HALLARD
' Date:     18/08/2000
' Format a DMTF date to human readable format

' DMTF Date in string like yyyymmddHHMMSS.mmmmmmsUUU
'
' yyyy Four-digit year (0000 through 9999)
' mm Two-digit month (01 through 12).
' dd Two-digit day of the month (01 through 31). This value must be appropriate for the month.
' HH Two-digit hour of the day using the 24-hour clock (00 through 23).
' MM Two-digit minute in the hour (00 through 59).
' SS Two-digit number of seconds in the minute (00 through 59).
' mmmmmm Six-digit number of microseconds in the second (000000 through 999999).
' s Plus sign (+) or minus sign (-) to indicate a positive or negative offset from Universal Time Coordinates (UTC).
' UUU Three-digit offset indicating the number of minutes that the originating time zone deviates from UTC
'=========================================================================
' Modified by David Byrne to return dd/mm/yyyy hh:mm:ss value only
Function FormatDMTFDate(sDate)
	Set regEx = New RegExp
	regEx.Global = True
	regEx.Pattern = "(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})\.\d{6}([\+|\-])(\d{3})"
	FormatDMTFDate = regEx.Replace(sDate,"$3/$2/$1 $4:$5:$6")
	Set RegEx = Nothing
End Function