Reading from a text file to variables 1

[gmagerr]

I'm having trouble with this. I have a text file (generated with the certutil) I want to be able to go through the file and send users an email when their certificates are going to expire. Here's a sample of the file

 11/6/2008 12:00 AM
 10/7/2008 8:00 AM
Schema:
  Column Name                   Localized Name                Type    MaxLength
  ----------------------------  ----------------------------  ------  ---------
  RequestID                     Issued Request ID             Long    4 -- Indexed
  NotAfter                      Certificate Expiration Date   Date    8 -- Indexed
  UPN                           User Principal Name           String  2048 -- Indexed
  CertificateTemplate           Certificate Template          String  254 -- Indexed

Row 1:
  Issued Request ID: 0x21f (543)
  Certificate Expiration Date: 10/8/2008 11:03 AM
  User Principal Name: "_lossing@rand.org"
  Certificate Template: "1.3.6.1.4.1.311.21.8.1452867.10910400.12403072.16600237.1244368.171.16744963.12748033" company Smartcard Logon

Row 2:
  Issued Request ID: 0x332 (818)
  Certificate Expiration Date: 10/8/2008 4:20 PM
  User Principal Name: EMPTY
  Certificate Template: "CAExchange"

Row 3:
  Issued Request ID: 0x220 (544)
  Certificate Expiration Date: 10/9/2008 12:04 PM
  User Principal Name: "user2@company.com"
  Certificate Template: "User"

I can read the text file into an array ok, but how would I pull the results from the fields and just put the results in variables. For example Certificate Expiration Data: I just want to put the date into a variable, and do the same for all the others.

Issued Request ID: 0x220 (544)
Certificate Expiration Date: 10/9/2008 12:04 PM
User Principal Name: "user2@company.com"
Certificate Template: "User"

Here's what i have, it's not much

Dim objShell, objNetwork, objFSO

Set objShell = CreateObject("WScript.Shell")
Set objNetwork = WScript.CreateObject("WScript.Network")
Set objFSO = CreateObject("Scripting.FilesystemObject")

'==========================================================================
' STATIC VARIABLE ASSIGNMENTS
'==========================================================================
Const FOR_READING = 1, FOR_WRITING = 2, FOR_APPENDING = 8

'==========================================================================
' MAIN SCRIPT CODE
'==========================================================================
Set strCert = objFSO.OpenTextFile("C:\expiring_certificates.txt",1)
strArray = Split(strCert.ReadAll,vbCrLf)
For Each cert In strArray


WScript.Echo cert
Next

 

[PHV]

A starting point:

...
For Each cert In strArray
  If InStr(cert, "Issued Request ID:" Then
    reqID = Trim(Split(cert,":")(1))
  ElseIf InStr(cert, "Certificate Expiration Date:" Then
    expDate = Trim(Split(cert,":")(1))
  ElseIf InStr(cert, "User Principal Name:" Then
    userName = Trim(Split(cert,":")(1))
  ElseIf InStr(cert, "Certificate Template:" Then
    cerTemplate = Trim(Split(cert,":")(1))
  End If
Next
WScript.Echo _
 "Issued Request ID: " & reqID & vbCrLf & _
 "Certificate Expiration Date: " & expDate & vbCrLf & _
 "User Principal Name: " & userName & vbCrLf & _
 "Certificate Template: " & cerTemplate
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gmagerr]

Sweet, Thanks I'll give it a try

 

[gmagerr]

Well this is working out pretty good. I've filtered the variables and added them to a new text file, the file now looks like this.

Issued Request ID: 0x220 (544)
Certificate Expiration Date: 10/9/2008 12
User Principal Name: "user1@company.com"
Certificate Template: "User"

Issued Request ID: 0x222 (546)
Certificate Expiration Date: 10/9/2008 12
User Principal Name: "user1@company.com"
Certificate Template: "User"

Issued Request ID: 0x222 (546)
Certificate Expiration Date: 10/9/2008 10
User Principal Name: "user1@company.com"
Certificate Template: "User"

Issued Request ID: 0x222 (546)
Certificate Expiration Date: 10/9/2008 10
User Principal Name: "user2@company.com"
Certificate Template: "User"

Issued Request ID: 0x222 (546)
Certificate Expiration Date: 10/9/2008 10
User Principal Name: "user2@company.com"
Certificate Template: "EFS"

Issued Request ID: 0x222 (546)
Certificate Expiration Date: 10/9/2008 10
User Principal Name: "user2@company.com"
Certificate Template: "EFS"

Issued Request ID: 0x222 (546)
Certificate Expiration Date: 10/9/2008 10
User Principal Name: "user2@company.com"
Certificate Template: "EFS"

Now, how would I go through this text file and send one email to each user in the list with all of the expiration notices pertaining to them. Each user may have multiple extries in this text file. Here's what i have so far, but can't figure out how to accomplish this.

'==========================================================================
' 
' NAME: 
' 
' AUTHOR: Gene Magerr
' EMAIL: genemagerr@hotmail.com
'
' COMMENT: 
'
' You have a royalty-free right to use, modify, reproduce, and
' distribute this script file in any way you find useful, provided that
' you agree that the creator, owner above has no warranty, obligations,
' or liability for such use.
'
' VERSION HISTORY:
' 1.0   xx/xx/xxxx  Initial release
'
'==========================================================================
'Option Explicit

'==========================================================================
' VARIABLE DECLARATIONS
'==========================================================================
Dim objShell, objNetwork, objFSO

Set objShell = CreateObject("WScript.Shell")
Set objNetwork = WScript.CreateObject("WScript.Network")
Set objFSO = CreateObject("Scripting.FilesystemObject")

'==========================================================================
' STATIC VARIABLE ASSIGNMENTS
'==========================================================================
Const FOR_READING = 1, FOR_WRITING = 2, FOR_APPENDING = 8

'==========================================================================
' MAIN SCRIPT CODE
'==========================================================================
If Not objFSO.FileExists("C:\TempCert.txt") Then
objFSO.CreateTextFile("C:\TempCert.txt")
End If

Set objTempCert = objFSO.OpenTextFile("C:\TempCert.txt",2)

Set strCert = objFSO.OpenTextFile("C:\expiring_certificates.txt",1)
strArray = Split(strCert.ReadAll,vbCrLf)

For Each cert In strArray
If InStr(cert, "Issued Request ID:") Then
    reqID = Trim(Split(cert,":")(1))
  ElseIf InStr(cert, "Certificate Expiration Date:") Then
    expDate = Trim(Split(cert,":")(1))
  ElseIf InStr(cert, "User Principal Name:") Then
    userName = Trim(Split(cert,":")(1))
  ElseIf InStr(cert, "Certificate Template:") Then
    cerTemplate = Trim(Split(cert,":")(1))
  End If

If reqID <> "" and expDate <> "" and userName <> "" and cerTemplate <> "" Then
'WScript.Echo _
' "Issued Request ID: " & reqID & vbCrLf & _
' "Certificate Expiration Date: " & expDate & vbCrLf & _
' "User Principal Name: " & userName & vbCrLf & _
' "Certificate Template: " & cerTemplate
objTempCert.WriteLine "Issued Request ID: " & reqID
objTempCert.WriteLine "Certificate Expiration Date: " & expDate
objTempCert.WriteLine "User Principal Name: " & userName
objTempCert.WriteLine "Certificate Template: " & cerTemplate & vbCrLf 
End If

Next

objTempCert.Close

'Set strTempCert = objFSO.OpenTextFile("C:\TempCert.txt",1)
'strArray2 = Split(strTempCert.ReadAll,vbCrLf)

'For Each tempcert In strTempCert
'If InStr(tempcert, "User Principal Name:") Then 

'==========================================================================
' SUBS AND FUNCTIONS
'==========================================================================

 

[PHV]

Anyway:

If reqID <> "" And expDate <> "" And userName <> "" And cerTemplate <> "" Then
  objTempCert.WriteLine "Issued Request ID: " & reqID
  objTempCert.WriteLine "Certificate Expiration Date: " & expDate
  objTempCert.WriteLine "User Principal Name: " & userName
  objTempCert.WriteLine "Certificate Template: " & cerTemplate & vbCrLf
  [!]reqID = ""; expDate = ""; userName = ""; cerTemplate = ""[/!]
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gmagerr]

Getting an error "Expecting End Of Statement" reqID = ""; in between the "" and the ;

 

[PHV]

Sorry for the typos: replace the ; with :

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gmagerr]

Ok, I see what that did, it removed all of the duplicate entries. Sweet. There are users with valid multiple entries (Different Request ID for example) I want to take all of those entries in the text file for that user only, and send it to that user via email. This will be done for each user in the text file.

User Principal Name: "user1@company.com"
Issued Request ID: 0x22c (556)
Certificate Expiration Date: 10/22/2008 4
Certificate Template: "User"

User Principal Name: "user1@company.com"
Issued Request ID: 0x22d (557)
Certificate Expiration Date: 10/22/2008 4
Certificate Template: "User"

 

[gmagerr]

BTW Thanks I really appreciate the help.