windows event log sql fails for "WHERE Logfile <> 'xxxxx'

I found some code that reads the system event log into an excel spreadsheet, OR the Application event log.

I want to get the union of ALL event logs.  For some reason, the simple SQL changes to accomplish that do not work the way I expect.

How can I select ALL events from the Windows logs?


I have had various attempts fail.  For instance, the following code only gets the Application events.
   …
  strServer = "."  ' This computer
   Set svcServices = GetObject("winmgmts:\\" & strServer & "\root\cimv2")
   strWQL = "SELECT * " & _
         "FROM Win32_NTLogEvent " & "WHERE Logfile <> ""xxxxx"""
   Set setObjectSet = svcServices.ExecQuery(strWQL)
    …

If I change the SQL predicate to << logfile = ""System"" >>  It just get the System log.

And I can't seem to get the Security log no matter what I try.  

I found one link that indicated the security log required a slightly different form of the Getobject but I could not get that to work either.  Here is that code in its entirety.  I does not give any error messages, but it does not list any Security events.

Any help would be appreciated.

Sub t1138()
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate,(Security)}!\\" & _
        strComputer & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery _
    ("Select * FROM Win32_NTLogEvent WHERE Logfile = 'Security' " & _
        "AND EventType = 5")
For Each objEvent In colLoggedEvents
    WScript.Echo "==================================================="
    WScript.Echo "Category: " & objEvent.Category
    WScript.Echo "Computer Name: " & objEvent.ComputerName
    WScript.Echo "Event Code: " & objEvent.EventCode
    WScript.Echo "Message: " & objEvent.Message
    WScript.Echo "Record Number: " & objEvent.RecordNumber
    WScript.Echo "Source Name: " & objEvent.SourceName
    WScript.Echo "Time Written: " & objEvent.TimeWritten
    WScript.Echo "Event Type: " & objEvent.Type
    WScript.Echo "User: " & objEvent.User
    WScript.Echo
Next

End Sub


LVL 5
rberkeConsultantAsked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
Also, when running the code on Windows 7, run a command prompt "As Administrator", then run
cscript GetEvents.vbs

Regards,

Rob.
0
 
prashanthdConnect With a Mentor Commented:
The following link has some samples

http://www.activexperts.com/activmonitor/windowsmanagement/scripts/logs/event/#LSELP.htm

I am not sure why double double quotes are being used ""xxxxx"""

0
 
rberkeConsultantAuthor Commented:
No luck with those examples.  For instance. the following code on my windows 7 pro machine comes up with "no luck"

Also, some people use a ""   instead of a '.  I am not really sure why, but that is the code that I copied and I didn't bother to change it.   I have also seen a chr(34) used instead of a '.   I think that older version of sql required a real double quote and would not accept a single quote/apostraphe.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate,(Security)}!\\" & _
        strComputer & "\root\cimv2")

Set colLogFiles = objWMIService.ExecQuery _
    ("Select * from Win32_NTEventLogFile where LogFileName='Security'")
If colLogFiles.count = 0 Then MsgBox "no luck"
For Each objLogfile In colLogFiles
    MsgBox objLogfile.NumberOfRecords
    MsgBox "Maximum Size: " & objLogfile.MaxFileSize
Next
   
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
RobSampsonCommented:
Hi, the query uses Windows Query Language (WQL), and not specifically SQL, so the syntax is a bit different.

You can't use LogFile <> 'Security', you need to use OR:

Set svcServices = GetObject("winmgmts:{impersonationLevel=impersonate, (Backup, Security)}!\\" & strServer & "\root\cimv2")
strWQL = "SELECT * FROM Win32_NTLogEvent WHERE LogFile='Application' OR LogFile='Security'"

Open in new window


Regards,

Rob.
0
 
rberkeConsultantAuthor Commented:
Progress is coming very slowly.  

You are correct, running as administrator allows me to get at the security log when I use this line of code.

   strWQL = "SELECT * FROM Win32_NTLogEvent WHERE LogFile='Security'"

But when I use your line of code, I only get the Application log, even when I run as administrator

    strWQL = "SELECT * FROM Win32_NTLogEvent WHERE LogFile='Application' OR LogFile='Security'"

I am actually using VBA from Excel, not vbscript, but I don't think that should make any difference

0
 
RobSampsonConnect With a Mentor Commented:
Hi, I've run this code on Windows XP, as an Administrator, in a Microsoft Excel module, and it has pulled all of my Application and Security logs.

Regards,

Rob.
Sub GetEvents()
    strComputer = "."
    Set svcServices = GetObject("winmgmts:{impersonationLevel=impersonate, (Backup, Security)}!\\" & strComputer & "\root\cimv2")
    strWQL = "SELECT * FROM Win32_NTLogEvent WHERE LogFile='Application' OR LogFile='Security'"
    Set colLoggedEvents = svcServices.ExecQuery(strWQL)
    intRow = 2
    Cells(1, "A").Value = "Log File"
    Cells(1, "B").Value = "Category"
    Cells(1, "C").Value = "Computer Name"
    Cells(1, "D").Value = "Event Code"
    Cells(1, "E").Value = "Message"
    Cells(1, "F").Value = "Record Number"
    Cells(1, "G").Value = "Source Name"
    Cells(1, "H").Value = "Time Written"
    Cells(1, "I").Value = "Event Type"
    Cells(1, "J").Value = "User"
    For Each objEvent In colLoggedEvents
        Cells(intRow, "A").Value = objEvent.LogFile
        Cells(intRow, "B").Value = objEvent.Category
        Cells(intRow, "C").Value = objEvent.ComputerName
        Cells(intRow, "D").Value = objEvent.EventCode
        Cells(intRow, "E").Value = objEvent.Message
        Cells(intRow, "F").Value = objEvent.RecordNumber
        Cells(intRow, "G").Value = objEvent.SourceName
        Cells(intRow, "H").Value = objEvent.TimeWritten
        Cells(intRow, "I").Value = objEvent.Type
        Cells(intRow, "J").Value = objEvent.User
        intRow = intRow + 1
    Next
    MsgBox "Done"
End Sub

Open in new window

0
 
rberkeConsultantAuthor Commented:
Aww crud !!!  

the select statement was giving me the ENTIRE application log THEN the
Security Log.  

Unfortunately, my program had logic saying "stop after log gets 5 days old", so the program stopped before it got to the Security Log.


If I had posted my whole program you guys would have found the problem immediately.

Best answer goes to the mention of running as administrator.  
0
 
RobSampsonCommented:
Instead of using logic for stopping at 5 days old, just add a filter to the query like so:

'Adjust your time bias to suit
strBias = "+660"
strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile='Application' OR LogFile='Security') AND TimeWritten>'20110407000000" & strBias & "'"

Regards,

Rob.
0
 
rberkeConsultantAuthor Commented:
Good idea, but my frustration level just got exceeded.  

First, your code gives me an automation error.  It appears the syntax needs to be changed as follows

        strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'System' OR LogFile='Application') AND TimeWritten>'20110411 18:00:00'"
   

Second,  if I pick a time that is in the future,  it hangs.    

Third, it only gives me the Application entries.  But if I drop the << OR LogFile='Application' >> predicate, it gives me just the System.

Fourth ...I am giving up.  I wrote a workaround that works just fine.  I added and outerloop
     <<  For each logtype in array("Application", "System", "Security")  >>

Fifth, I already closed this question and awarded points so I am not not going to put much more effort into this.  But, if anybody can tell me why the attached code only gets the Application log, instead of System PLUS Application, I would love to know. Maybe parenthesis don't work right?




Sub t1138()
' event viewer input
' add reference to microsoft wmi scripting
Dim objObject As WbemScripting.SWbemObject
Dim setObjectSet As WbemScripting.SWbemObjectSet
Dim strServer As String
Dim strWQL As String
Dim svcServices As WbemScripting.SWbemServices
Dim varInsertionString As Variant
Dim LogName As Variant
Application.ScreenUpdating = False

Sheets("Sheet1").Activate
ActiveSheet.Cells.Clear
Dim ur
  Set ur = ActiveSheet.UsedRange
   
strServer = "."  ' This computer
' Set svcServices = GetObject("winmgmts:\\" & strServer & "\root\cimv2")

Set svcServices = GetObject("winmgmts:{impersonationLevel=impersonate, (Backup, Security)}!\\" & strServer & "\root\cimv2")


    strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'System' OR LogFile='Application') AND TimeWritten>'20110411 18:00:00'"
    
    Set setObjectSet = svcServices.ExecQuery(strWQL)
    Dim cntI As Long
    cntI = 0
    
    Dim LastXLSTimeGenerated As Date, thisTimeGenerated As Date
    
    LastXLSTimeGenerated = 0: thisTimeGenerated = 0
    For Each objObject In setObjectSet
        cntI = cntI + 1
        
                Dim x
                x = objObject.TimeGenerated
                Dim lastTimeGenerated As Date
                lastTimeGenerated = thisTimeGenerated
                thisTimeGenerated = WMIDateStringToDate(x)
                
               If Int(LastXLSTimeGenerated) = 0 Or LastXLSTimeGenerated - thisTimeGenerated > TimeSerial(0, 3, 0) Then
                    LastXLSTimeGenerated = thisTimeGenerated
                    [2:2].Insert
                          [l2] = DateSerial(Mid(x, 1, 4), Mid(x, 5, 2), Mid(x, 7, 2)) _
                        + TimeSerial(Mid(x, 9, 2), Mid(x, 11, 2), Mid(x, 13, 2)) + TimeSerial(-4, 0, 0)
                          '            [a2] = objObject.Logfile
                    [a2] = objObject.Logfile
                     [b2] = objObject.Message
                     [c2] = objObject.RecordNumber
                     [d2] = objObject.SourceName
                     [e2] = x
                     [f2] = objObject.TimeWritten
                End If
        Set objObject = Nothing
        If cntI > 1000 Then Stop
        If now() - thisTimeGenerated > 1 Then Exit For
    
    Next

Set setObjectSet = Nothing

Set svcServices = Nothing

End Sub

Open in new window

0
 
RobSampsonCommented:
As for the TimeWritten value, you can use the SetVarDate method as shown here:
http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/logs/eventlogs/#CopyPrevDaysEventLog.htm

to help convert dates to the Event Log format.

I'm not sure why the retrieving of System and Application didn't work for you, as the code I showed did work for me, listing all events from both logs.  As I said, that was on Windows XP with Excel 2007.  If you're running Vista, or Windows 7, try running Excel itself "As Administrator" (because of UAC) and see what happens.

Otherwise, just use the Array loop as you have mentioned.

Regards,

Rob.
0
 
rberkeConsultantAuthor Commented:
Very odd.

I ran my code on a windows xp pro machine with Excel 2003 and got the same problem. Test1 fails.

Test1GetsApplication: strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'System' OR LogFile='Application') AND TimeWritten>'20110411 18:00:00'"


Test2GetsSystem:strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'System') AND TimeWritten>'20110411 18:00:00'"

Also, ran the same code under administrator or window 7, same result.

Also, if you'll do me one last favor before we give up.  You say you ran YOUR code and it worked.  But, did you try running MY code code?  I have a hunch that sub t1138 will not work on your machine either.  

Thanks

rberke
0
 
RobSampsonCommented:
Hi, I did as you asked and ran your code.  You have a problem with these lines:
        If Int(LastXLSTimeGenerated) = 0 Or LastXLSTimeGenerated - thisTimeGenerated > TimeSerial(0, 3, 0) Then
and
        If cntI > 1000 Then Stop
and
        If Now() - thisTimeGenerated > 1 Then Exit For

If I comment out each of those (plus the corresponding End If for the first statement), the code returns all events from the System and Application logs, so there is a condition that is being met that is exiting the loop before the events are output.

Regards,

Rob.
0
 
rberkeConsultantAuthor Commented:
Uggggh, I thought I had deleted that code. My humble apologies for my error.

Thanks for pointing out my error, and now the WQL works correctly, but it turns out it runs very slowly.

It takes nearly 20 seconds to run this query and append it to Excel:

qyr1: strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'System' OR LogFile='Application') AND TimeWritten>'20110411 18:00:00'"  


   
It only takes 2 seconds to run two separate query's and append each to Excel

qyr2a: strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'System' ) AND TimeWritten>'20110411 18:00:00'"  

followed by

qry2b: strWQL = "SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'Application') AND TimeWritten>'20110411 18:00:00'"  

I assume the qry1 needs to ready the entire Application log before it gets to the System log, whereas the separate queries do not.  (I have never bothered to limit my event log, so it has over a year of entries.)

Thanks for your patience with today's lousy coding -- it was not my best effort.

Bob

0
 
RobSampsonCommented:
Hmmm, I can't explain that....I tried both scenarios, and it only took 3 seconds for either query.  I also used the date conversion method I pointed to above, and used this for testing:

Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
DateToCheck = CDate("11-Apr-2011 18:00:00")
dtmStartDate.SetVarDate DateToCheck, True

arrQueries = Array("SELECT * FROM Win32_NTLogEvent WHERE (LogFile = 'System' OR LogFile='Application') AND TimeWritten>'" & DateToCheck & "'")

arrQueries = Array("SELECT * FROM Win32_NTLogEvent WHERE LogFile='Application' AND TimeWritten>'" & DateToCheck & "'", _
                "SELECT * FROM Win32_NTLogEvent WHERE LogFile = 'System' AND TimeWritten>'" & DateToCheck & "'")

For Each strQuery In arrQueries
    strWQL = strQuery
    '  ........  rest of the code
Next

Open in new window


I commented out each array in turn to test them.  Anyway, you've probably arrived at your final solution now, so I guess it's no big deal.

Regards,

Rob.
0
 
rberkeConsultantAuthor Commented:
Yes, I have arrived at the final solution.  

I don't want to waste any more of your time, so there is no need for you to respond to this final post from me.  

But I can't resist a final comment.

I believe we have also proved that using     (logfile = "System" OR logfile = "Application")  and TimeWritten > 4/11/2011 makes things go slower.

I have the following theory.  I think WQL is smart enough to know that  the records in the log are sorted by  Logfile (e.g. Application records first, then System records), but it is NOT smart enough to know the second level sort is timewritten.  So, when it gets the last application record after 4/11/2011, WQL is to stupid to stop reading Application records and skip to the System records.  Instead it keeps reading and discarding thousand of Application records.

Of course, if your log file is small, you will never notice it, but my log has 200,000 records so it becomes most obvious.

Here is my final test that demonstrates the point.  It produces the following debug.print list.  If you analyze the delays, you will see that they are consistent with my theory.

4/19/2011 12:02:32 PM Try item 1 in array  ========================
100 4/19/2011 12:02:33 PM Application 46203 The Software Protection service has
200 4/19/2011 12:02:33 PM Application 46103 Service Broker manager has started.
300 4/19/2011 12:02:33 PM Application 46003 Server local connection provider is

==========NOTICE THERE IS NO  DELAY.   THE VBA PROGRAM STOPS ASKING FOR RECORDS BEFORE WQL IS DONE DELIVERING THEM.  SO WQL DOES NOT NEED TO  SKIP THE APPLICATION RECORDS THAT DON’T MEET THE SELECTION CRITERIA ===============


4/19/2011 12:02:33 PM Try item 2 in array ==========================
100 4/19/2011 12:02:33 PM System 219951 The Encrypting File System (EFS) service
200 4/19/2011 12:02:34 PM System 219851 DHCPv4 client service is stopped. ShutDo
300 4/19/2011 12:02:34 PM System 219751 The TCP/IP NetBIOS Helper service entere
400 4/19/2011 12:02:34 PM System 219651 The WinHTTP Web Proxy Auto-Discovery Ser
500 4/19/2011 12:02:34 PM System 219551 The system time has changed to ?2011?-?0
600 4/19/2011 12:02:34 PM System 219451 The Microsoft Software Shadow Copy Provi
700 4/19/2011 12:02:35 PM System 219351 The Diagnostic System Host service enter
800 4/19/2011 12:02:35 PM System 219251 The Remote Desktop Services service ente
900 4/19/2011 12:02:35 PM System 219151 File System Filter 'TmPreFilter' (Versio
1000 4/19/2011 12:02:35 PM System 219051 The Multimedia Class Scheduler service
1100 4/19/2011 12:02:35 PM System 218951 The Microsoft Software Shadow Copy Prov
1200 4/19/2011 12:02:35 PM System 218851 The WinHTTP Web Proxy Auto-Discovery Se
1300 4/19/2011 12:02:36 PM System 218751 The Application Experience service ente
1400 4/19/2011 12:02:36 PM System 218651 The WinHTTP Web Proxy Auto-Discovery Se
1500 4/19/2011 12:02:36 PM System 218551 The Diagnostic System Host service ente
1600 4/19/2011 12:02:36 PM System 218451 WLAN Extensibility Module has stopped.
1700 4/19/2011 12:02:36 PM System 218351 The Multimedia Class Scheduler service
==========NOTICE LONG DELAY WHILE WQL SKIPS ALL THE APPLICATION RECORDS THAT DON’T MEET THE SELECTION CRITERIA ===============


4/19/2011 12:02:36 PM Try item 3 in array ===========================

100 4/19/2011 12:02:37 PM Application 46203 The Software Protection service has
200 4/19/2011 12:02:37 PM Application 46103 Service Broker manager has started.
300 4/19/2011 12:02:37 PM Application 46003 Server local connection provider is
400 4/19/2011 12:02:37 PM Application 45903
500 4/19/2011 12:02:37 PM Application 45803 Activation context generation failed

==========NOTICE LONG DELAY WHILE WQL SKIPS ALL THE APPLICATION RECORDS THAT DON’T MEET THE SELECTION CRITERIA ===============

600 4/19/2011 >>>12:03:28 PM <<<  System 220033 The Telephony service entered the runnin
700 4/19/2011 12:03:29 PM System 219933 DHCPv4 client service is started
800 4/19/2011 12:03:29 PM System 219833 The Multimedia Class Scheduler service e
900 4/19/2011 12:03:29 PM System 219733 Name resolution for the name server02.ed
1000 4/19/2011 12:03:29 PM System 219633 The SQL Server Browser service entered
1100 4/19/2011 12:03:29 PM System 219533 The IKE and AuthIP IPsec Keying Modules
1200 4/19/2011 12:03:29 PM System 219433 The Application Experience service ente
1300 4/19/2011 12:03:30 PM System 219333 The Program Compatibility Assistant Ser
1400 4/19/2011 12:03:30 PM System 219233 The Intel(R) Management and Security Ap
1500 4/19/2011 12:03:30 PM System 219133 The Multimedia Class Scheduler service
1600 4/19/2011 12:03:30 PM System 219033 A driver package which uses user-mode d
1700 4/19/2011 12:03:30 PM System 218933 The Multimedia Class Scheduler service
1800 4/19/2011 12:03:31 PM System 218833 The Volume Shadow Copy service entered
1900 4/19/2011 12:03:31 PM System 218733 The start type of the Windows Modules I
2000 4/19/2011 12:03:31 PM System 218633 The WinHTTP Web Proxy Auto-Discovery Se
2100 4/19/2011 12:03:31 PM System 218533 The Windows Live ID Sign-in Assistant s
2200 4/19/2011 12:03:31 PM System 218433 The IPsec Policy Agent service entered
2300 4/19/2011 12:03:32 PM System 218333 The Multimedia Class Scheduler service
2400 4/19/2011 12:03:32 PM System 218233 The Windows Modules Installer service e
2500 4/19/2011 12:03:32 PM System 218133 The IKE and AuthIP IPsec Keying Modules
2600 4/19/2011 12:03:32 PM System 218033 The Diagnostic Policy Service service e
2700 4/19/2011 12:03:32 PM System 217933 Installation Ready: The following updat
2800 4/19/2011 12:03:32 PM System 217833 NtpClient was unable to set a domain pe
2900 4/19/2011 12:03:33 PM System 217733 Microsoft (R) Windows (R) 6.01. 7601 Se
3000 4/19/2011 12:03:33 PM System 217633 The Application Experience service ente
========== ALSO, THERE IS A LONG DELAY EVEN AFTER WQL HAS DELIVERED THE LAST RECORD  ===============

4/19/2011 12:04:43 PM done with array



Sub t1119()
Dim svcservices As WbemScripting.SWbemServices
Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
DateToCheck = CDate("13-Apr-2011 18:00:00")
DayAfterDateToCheck = DateToCheck + 1
dtmStartDate.SetVarDate DateToCheck, True


Set svcservices = GetObject("winmgmts:{impersonationLevel=impersonate, (Backup, Security)}!\\.\root\cimv2")
arrQueries = Array( _
                    "SELECT * FROM Win32_NTLogEvent WHERE  LogFile='Application' AND TimeWritten>'" & DateToCheck & "'", _
                    "SELECT * FROM Win32_NTLogEvent WHERE  LogFile='System' AND TimeWritten>'" & DateToCheck & "'", _
                    "SELECT * FROM Win32_NTLogEvent WHERE (LogFile='System' OR LogFile='Application') AND TimeWritten>'" & DateToCheck & "'" _
                )

For Each strQuery In arrQueries
    cntI = cntI + 1
Debug.Print Now() & " Try item " & cntI & " in array"
    strWQL = strQuery
    Set setObjectSet = svcservices.ExecQuery(strWQL)
    cntj = 0
    For Each objObject In setObjectSet
        cntj = cntj + 1
       If cntj Mod 100 = 0 Then Debug.Print cntj & " " & Now() & " " & objObject.logfile & " " & objObject.recordnumber & " " & objObject.Message
       If cntI < 3 And WMIDateStringToDate(objObject.TimeGenerated) < DayAfterDateToCheck Then
            Exit For
        End If
    Next
Next
Debug.Print Now() & " done with array"
End Sub
Function WMIDateStringToDate(dtmDate)

WMIDateStringToDate = CDate(Mid(dtmDate, 5, 2) & "/" & _
Mid(dtmDate, 7, 2) & "/" & Left(dtmDate, 4) _
& " " & Mid(dtmDate, 9, 2) & ":" & Mid(dtmDate, 11, 2) & ":" & _
Mid(dtmDate, 13, 2))

End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.