use of & and quotes

[vbcdor]

Hi,
I'm new to vbscript and I am having alot of problems with the use of &, ' and ". I keep getting the error "unterminated string constant" on this select statement. dtmStartDate and dtmEndDate are variables defined earlier in the script. This code scans (or is attempting to scan) the Windows Event log for a specific code, within a specific date range. The OS is Windows 2000.

Set colLoggedEvents = objWMIService.ExecQuery _
("Select * from Win32_NTLogEvent Where Logfile = 'Application' and EventCode = '1704' and _
TimeWritten >= "' & dtmStartDate & '" and TimeWritten <= "' & dtmEndDate & '"")
For each objEvent in colLoggedEvents
e1.WriteLine "*********"
e1.WriteLine "Oracle DB shutdowns: " & colLoggedEvents.Count
e1.WriteLine "Message: " & objEvent.Message
e1.WriteLine "Time Written: " & objEvent.TimeWritten
e1.WriteLine "Event Code: " & objEvent.EventCode
e1.WriteLine "*********"
e1.WriteBlankLines(1)
Next

I've been searching for a good explanation of this in the Users guide and Reference, but I can't find anything that talks about concatenation and the use of quotes. If anyone has any ideas or can point me to an article that talks about this type of thing, I would really appreciate it.


Thanks,
vbcdor

 

[mrmovie]

msgbox "Select * from Win32_NTLogEvent Where Logfile = 'Application' and EventCode = '1704' and TimeWritten >= " & dtmStartDate & " and TimeWritten <= " & dtmEndDate

does that display what you want??

i use Chr(34) for qoutes.
i think you have a problem with your "and _" for one thing, ie the line continuation

 

[PHV]

Set colLoggedEvents = objWMIService.ExecQuery _
("Select * from Win32_NTLogEvent Where Logfile = 'Application' and EventCode = '1704' and [red]" &[/red]_
[red]"[/red]TimeWritten >= [red]'[/red]" & dtmStartDate & "[red]'[/red] and TimeWritten <= [red]'[/red]" & dtmEndDate & "[red]'[/red]")
The double quote (") are for VB string literal, the single one (') is for SQL string literal.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vbcdor]

PHV,

I changed the syntax around as you suggested and it works, kind of. I got around the syntax error, but now I have some sort of logic error because this code is suppose to capture eventlog errors for today and yesterday, but it is actually capturing errors from yesterday and the day before yesterday. Any ideas why this might be happening?

Thanks,
vbcdor

 

[PHV]

How are dtmStartDate and dtmEndDate filled ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vbcdor]

It looks like this:

dtmEndDate = CDate(Date)
dtmStartDate = dtmEndDate - 1



' Converting to WMI "date"
dtmEndDate = Year(dtmEndDate) _
& Right( "00" & Month(dtmEndDate), 2) _
& Right( "00" & Day(dtmEndDate), 2)

dtmStartDate = Year(dtmStartDate) _
& Right( "00" & Month(dtmStartDate), 2) _
& Right( "00" & Day(dtmStartDate), 2)

vbcdor

 

[vbcdor]

it's almost like it's writing the events after the one it is selecting. Another words, the 1st 3 events in the eventlog are for today, yesterday and the day before (and all the correct eventcode). It should write events 1 and 2, but writes events 2 and 3.

vbcdor

 

[PHV]

TimeWritten <=
Seems you have to add some time info in your parameters.
Something like 00:00:00 for start date and 23:59:59 for end date.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vbcdor]

Thanks PHV,

Can I do that with something like:

dtmEndDate = Year(dtmEndDate) _
& Right( "00" & Month(dtmEndDate), 2) _
& Right( "00" & Day(dtmEndDate), 2)
newEndDate = DateAdd("hns", "23:59:59", dtmEndDate)


dtmStartDate = Year(dtmStartDate) _
& Right( "00" & Month(dtmStartDate), 2) _
& Right( "00" & Day(dtmStartDate), 2)
newStartDate = DateAdd("hns", "00:00:00", dtmStartDate)

Thanks.

 

[vbcdor]

or
newEndDate = DateAdd("hns", "235959", dtmEndDate
newStartDate = DateAdd("hns", "0", dtmStartDate)

 

[vbcdor]

ok, I tried concatenating like so:

newStartDate = dtmStartDate & "000000"
newEndDate = dtmEndate & "235959"

The dates look ok when I echo them, but when I run the script I get an error 0x80041017, code 80041017 on this line
("Select * from Win32_NTLogEvent Where Logfile = 'Application' and EventCode = '1704' and "& "TimeWritten >= '" & dtmStartDate & "' and TimeWritten <= '" & dtmEndDate & "'")

 

[mrmovie]

i might be way off base here but could it be WMI's funny date formats? yyyymmddHHMMSS.mmmmmmsUUU

 

[mrmovie]

i only say that as i know if you iterate though a collection of eventlogs returned by a WMI query if you want to get a VB date and time format you have to convert it because TimeWritten is returned in the format i have posted.

saying that it might not require this format in the WQL statement.

only experience i have is in returning all the event entries and then iterating through them and checking the TimeWritten,,whcih isnt as efficient i guess

 

[vbcdor]

mrmovie,
Is there a sub string function? I can't seem to find one in the reference guide. Can I some way set a variable to a the first 14 positions of the WMI date?

vbcdor

 

[vbcdor]

I tried using left(TimeWritten,14) to do the comparison and I received an error.

 

[mrmovie]

to go from WMI date to vb date

Function GetVBDate(wd)
GetVBDate = DateSerial(left(wd,4),mid(wd,5,2),mid(wd,7,2)) + TimeSerial(mid(wd,9,2),mid(wd,11,2),mid(wd,13,2))
End Function

 

[vbcdor]

PHV and mrmovie,

Thanks for your help. I couldn't get the script to work with the vb date function, but I used this idea to stick an if statement after the "For each objEvent" statement. The code now looks like:

Set colLoggedEvents = objWMIService.ExecQuery _
("Select * from Win32_NTLogEvent Where Logfile = 'Application' and EventCode = '1704'")
For each objEvent in colLoggedEvents
wd = objEvent.TimeWritten
newdate = (left(wd,14))
If newdate >= dtmStartDate and newdate <= dtmEndDate Then
WScript.Echo "newdate = " & newdate
e1.WriteLine "*********"
e1.WriteLine "Oracle DB shutdowns: " & colLoggedEvents.Count
e1.WriteLine "Message: " & objEvent.Message
e1.WriteLine "Time Written: " & objEvent.TimeWritten
e1.WriteLine "Event Code: " & objEvent.EventCode
e1.WriteLine "*********"
e1.WriteBlankLines(1)
End if
Next

It's probably not the most efficient code, but it works. Thanks for your help.

vbcdor