2

Apparently WQL does not contain an ORDER BY clause. Is there a way to sort the result set based on one of the columns? For example:

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sSQL = "Select Time,Source,Event,CategoryNum,Category,TypeNum,Type,User,ComputerName,Insertion1,Insertion2,Data from Win32_NTLogEvent Where Logfile = 'System' and SourceName = 'Service Control Manager'"
Set resultSet = objWMIService.ExecQuery (sSQL)
For Each objEvent In resultSet
    ...
Next

Is there a way to sort resultSet by the Time column?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Neil Weicher
  • 2,370
  • 6
  • 34
  • 56

1 Answers1

3

WQL indeed doesn't have an ordering clause, so sorting directly in the query is not possible. What you can do is put the returned data in a disconnected recordset and then sort the recordset:

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "Time", 7
DataList.Fields.Append "Source", 200, 255
DataList.Fields.Append "Event", 3
...
DataList.Open

Set wmi = GetObject("winmgmts:{impersonationLevel=impersonate}!//./root/cimv2")
qry = "SELECT Time,Source,Event,CategoryNum,Category,TypeNum,Type,User,ComputerName,Insertion1,Insertion2,Data " & _
      "FROM Win32_NTLogEvent " & _ 
      "WHERE Logfile='System' AND SourceName='Service Control Manager'"

For Each evt In wmi.ExecQuery(qry)
    DataList.AddNew
    DataList("Time")   = evt.Time
    DataList("Source") = evt.Source
    DataList("Event")  = evt.Event
    ...
    DataList.Update
Next

DataList.Sort = "Time"
DataList.MoveFirst
Do Until DataList.EOF
    WScript.Echo DataList.Fields.Item("Time") & vbTab & _
        DataList.Fields.Item("Event")
    DataList.MoveNext
Loop

Adjust the data type of the fields as required.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • Thanks for the suggestion. When editing and running the sample I immediately get an error on line 2: _Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another_. Code: 800A0BB9 Source: ADODB.Fields – Neil Weicher Sep 12 '17 at 11:50
  • Can't reproduce. Please provide evidence. – Ansgar Wiechers Sep 12 '17 at 12:17
  • I reduced it to these two lines and put in test1.vbs: `Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "Time", adDate` Then ran test1.vbs from Command Line. Got the popup message I referenced. I am using Windows 7 Pro x64.     – Neil Weicher Sep 12 '17 at 12:26
  • Do you see an `adDate` in my answer anywhere? No? That might have something to do with me not putting it there in the first place. Please postpone getting creative until *after* you got the code to work. VBScript doesn't know these constants, so you must define them yourself if you want to use them. Otherwise you must use numeric values. – Ansgar Wiechers Sep 12 '17 at 13:35
  • Thanks. I thought those were defined constants. FYI, I found them here: https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum – Neil Weicher Sep 12 '17 at 19:34
  • They are in some languages, but not in plain VBScript. – Ansgar Wiechers Sep 12 '17 at 19:52