How can I sort by date? Example time format is: Sat Oct 07 2017 07:30:00 GMT-0400 (EDT)

[bharpur183]

I have a search from which I get the below result one of the columns in the statistics table :

Sat Oct 07 2017 07:30:00 GMT-0400 (EDT)

Sat Oct 07 2017 12:00:00 GMT-0400 (EDT)
Thu Oct 05 2017 08:00:00 GMT-0400 (EDT)
Tue Oct 03 2017 10:00:00 GMT-0400 (EDT)
Tue Oct 03 2017 18:00:00 GMT-0400 (EDT)
Wed Oct 04 2017 13:00:00 GMT-0400 (EDT)
Wed Oct 04 2017 22:30:00 GMT-0400 (EDT)
Wed Oct 04 2017 17:30:00 GMT-0400 (EDT)
Wed Oct 04 2017 16:15:00 GMT-0400 (EDT)
Wed Oct 04 2017 08:00:00 GMT-0400 (EDT)

I am trying to sort the complete table based on the above field which is the date field, but the sort for the above comes up in alphabetically order of the days and not the dates in the above result.

SO I want to sort them based on the dates in the above table and show only the future dates from whatever time onward and not the past results.

[inventsekar]

 contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" AND (PRQS AND submitted)
     | rex field=_raw ".*\"drqsNumber\"\:\s(?<DRQS>\d+)\(FIELD5\).*\"routeToGroup\"\:\s(?<Field4>\d+)\(FIELD4\).*PRQS\s(?<PRQS>\d+)\(FIELD6\).*take\s(?<Field1>\S+)\(FIELD1\).*offline\son\s(?<Field2>.*)\(FIELD2\)\sfor\s(?<Field3>.*)\(FIELD3\)"
| eval epochDate=strptime(FIELD2, "%a %b %d %Y %H:%M:%S GMT%z (%Z)")|sort 0 epochDate
| table HOSTNAME , DRQS , PRQS , WINDOW , MAINTENANCE | fillnull value="NULL" | search HOSTNAME!="NULL" AND DRQS!="NULL" AND PRQS!="NULL" AND WINDOW!="NULL" | dedup HOSTNAME

the WINDOW , MAINTENANCE are not rex'ed here.. i think they are already extracted, otherwise, you will need to update the rex query to include them.
converted the date-field FIELD2 to epoch and sorted. hope this is good.. please check and suggest. if values not extracted properly, remove all rex'es and add them one by one, while adding verify the syntax. hope all works fine.

[cmerriman]

Try something like this:

| eval epochDate=strptime(date, "%a %b %d %Y %H:%M:%S GMT%z (%Z)")|sort 0 epochDate

To remove the epoch field after sorting, just use |fields - epochDate

[bharpur183]

Didn't help either. No change in the results.

Does the string you provided need to be at a specific place in the entire query ?
right now my entire query is :

contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" | search (PRQS AND submitted) | rex field=_raw "(?\S+) submitted to take (?\S+) .* offline on (?.*) for (?[^\"]+)\"" | rex field=_raw "\"drqsNumber\": (?\S+)," | rex field=_raw "\"routeToGroup\": (?\S+)," | table HOSTNAME , DRQS , PRQS , WINDOW , MAINTENANCE | fillnull value="NULL" | search HOSTNAME!="NULL" AND DRQS!="NULL" AND PRQS!="NULL" AND WINDOW!="NULL" | dedup HOSTNAME

The above table I posted in the question is the result of regex :

rex field=_raw "(?\S+) submitted to take (?\S+) .* offline on (?.*) for (?[^\"]+)\""

[cmerriman]

can you provide some of the raw data, with any sensitive data masked? Is your regex giving your date a field name? It doesn’t look like it to me when I’m just looking over it. You need the regex to extract the date into its own field, and use that field in the strptime eval provided earlier. That eval can just be tacked onto the end of the search.

[bharpur183]

yes the date field from the regex is extracted by :

rex field=_raw "(?\S+) submitted to take (?\S+) . offline on (?.) for (?[^\"]+)\""

Below is the raw data :

02OCT2017_16:46:47.212 130880:140149567481600 INFO event.py:177 root event = {"hopTrace": {"hops": [{"machine": {"nodeId": 569}, "application": {"processId": 19295, "processName": "udrqssvc.tsk", "appName": "DRQS"}, "authenticatedUser": {"uuid": 10095155}}]}, "event": {"eventType": "DRQS UPDATED", "drqsNumber": 107516809(FIELD5), "newHeader": {"status": "Q", "function": "N539", "billToId": 5028, "yellowKey": "", "billToType": "HIER", "lastUpdateTime": "2017-10-02T20:46:47.000+00:00", "type": "IW", "creatorUuid": xxxxxxx, "slaCategory": -1, "summary": "MM/DD nxxx hardware failure xxx xxx: xxxxx.L6Q.000", "queue": "", "timeClosed": "1899-12-31T05:00:00.000+00:00", "ouTypeCode": 0, "routeToGroup": 270(FIELD4), "ouTypeDescription": "", "tsCustomerNumber": 0, "closedUuid": 0, "lastUpdateUuid": 10095155, "createTime": "2017-09-29T12:00:48.000+00:00", "ownerUuid": 2984495}, "logNotes": [{"logNoteId": "1049598095", "timestamp": "2017-10-02T20:46:47.141+00:00", "authorUuid": xxxxxxxx, "logText": [{"text": "Note added from offline, remote machine 208\n", "textType": "DEEMPHASIZED"}, {"text": "{FIFW PRQS 160269881(FIELD6)} submitted to take Nxxx(FIELD1) (N539) offline on Tue Oct 03 2017 19:00:00 GMT-0400 (EDT)(FIELD2) for HARDWARE REPAIRS(FIELD3)\n", "textType": "NORMAL"}], "isAutomated": true}]}, "metadata": {"publishId": "121785005", "publishTime": "2017-10-02T16:46:47.189-04:00"}}

[s2_splunk]

Convert your timestamps to epoch using | eval sortableDate=strptime(d, "%a %b %d %Y %H:%M:%S GMT%z"), then sort by sortableDate. Add | where sortableDate > now() to only show events with a timestamp > current timestamp.
Remove the field from your results afterwards using | fields -sortableDate if you don't want it to show up.

[bharpur183]

So I tried the above and no luck. Its not sorting anything.

Also the above results I put are extracted as a regex and not as a regular filed. ( not sure if that matters)

[masonmorales]

Try using strptime to convert it to epoch, then sort, then convert back to your desired time format using strftime.