This is the event :
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": 1603009, "slaCategory": -1, "summary": "MM/DD n539 hardware failure IBM PMR: 24465.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": 10095155, "logText": [{"text": "Note added from offline, remote machine 208\n", "textType": "DEEMPHASIZED"}, {"text": "{FIFW PRQS 160269881(FIELD6)} submitted to take N539(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"}}
From the above event I want to create a statistics table with Field1-Fileds 6
I have highlighted the needed fields as bold . I get some of them but not all 6 fields
Please check this rex query... (if the event got different words, then rex needs to be adjusted)
Updated - the base query
contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" AND (PRQS AND submitted)
| rex field=_raw ".*\"drqsNumber\"\:\s(?<Field5>\d+)\(FIELD5\).*\"routeToGroup\"\:\s(?<Field4>\d+)\(FIELD4\).*PRQS\s(?<Field6>\d+)\(FIELD6\).*take\s(?<Field1>\S+)\(FIELD1\).*offline\son\s(?<Field2>.*)\(FIELD2\)\sfor\s(?<Field3>.*)\(FIELD3\)"
| table Field5 Field4 Field3 Field6 Field1 Field2 Field3 _raw
Please check this rex query... (if the event got different words, then rex needs to be adjusted)
Updated - the base query
contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" AND (PRQS AND submitted)
| rex field=_raw ".*\"drqsNumber\"\:\s(?<Field5>\d+)\(FIELD5\).*\"routeToGroup\"\:\s(?<Field4>\d+)\(FIELD4\).*PRQS\s(?<Field6>\d+)\(FIELD6\).*take\s(?<Field1>\S+)\(FIELD1\).*offline\son\s(?<Field2>.*)\(FIELD2\)\sfor\s(?<Field3>.*)\(FIELD3\)"
| table Field5 Field4 Field3 Field6 Field1 Field2 Field3 _raw
keep getting an error :
Unknown search command 'sourcetype'.
i uploaded the sample event on my splunk with the sourcetype as rexField.
for your environment, you have to write the base splunk query which will get the right events.
similar to this -
index=main source=fieldlogs sourcetype=logs host=hostname | remaining rex query
yep. I have my regular query before the above rex you provided , but still gives me the unknown source type error
may i know your splunk search query please, when you run it, do you get the events similar to the one updated on the question
this is my query
contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" AND (PRQS AND submitted)
and yes the question above is the result of the above query
Please try this query and update me what happens -
contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" AND (PRQS AND submitted) | rex field=_raw ".*\"drqsNumber\"\:\s(?\d+)\(FIELD5\).*\"routeToGroup\"\:\s(?\d+)\(FIELD4\).*PRQS\s(?\d+)\(FIELD6\).*take\s(?\S+)\(FIELD1\).*offline\son\s(?.*)\(FIELD2\)\sfor\s(?.*)\(FIELD3\)"
| table Field5 Field4 Field3 Field6 Field1 Field2 Field3 _raw
got this error now :
Error in 'rex' command: Encountered the following error while compiling the regex '."drqsNumber":\s(?\d+)(FIELD5)."routeToGroup":\s(?\d+)(FIELD4).PRQS\s(?\d+)(FIELD6).*take\s(?\S+)(FIELD1).*offline\son\s(?.)(FIELD2)\sfor\s(?.*)(FIELD3)': Regex: unrecognized character after (? or (?-
i have updated the Splunk Query on this answer post.. can you please try it and update me -
same error :
Error in 'rex' command: Encountered the following error while compiling the regex '."drqsNumber":\s(?\d+)(FIELD5)."routeToGroup":\s(?\d+)(FIELD4).PRQS\s(?\d+)(FIELD6).*take\s(?\S+)(FIELD1).*offline\son\s(?.)(FIELD2)\sfor\s(?.*)(FIELD3)': Regex: unrecognized character after (? or (?-
ok, lets do step by step.. when you run this, do you get the Field5 results?
contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" AND (PRQS AND submitted)
| rex field=_raw ".*\"drqsNumber\"\:\s(?<Field5>\d+) | table _raw Field5
the above query is going on for the last 20 mins and its still going and producing no results
when you run this, how many events you get -
contextName=olliebot service_name=olliebot source="/bb/logs/vcon/olliebot.log.2017*" AND (PRQS AND submitted)
ok the query finished and did yield field 5 correctly ( there are empty rows in between the results in the statistics table) but it did yield field 5
ok great.. maybe now you can add one more field on the rex or add the full rex query and search.
if search takes long time, do you search with "All time"?
if search runs for long time, then please adjust the time selection(maybe, one day / few hours, etc..)
ok cool.
how do I get rid of the empty rows in between ?
what query are you running when you get the empty rows?
maybe attach a screenshot photo(you can not attach a photo here in a comment. you have to write a new answer and attach the photo)
ok I was able to get rid of the blank rows and the entire query works now and I get the needed results
Just one last thing :
I want to build the table based on the field 2 (which is the date field in my original question that shows goes from current backwards . How can I do that ?
the 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
Basically the current date shows on top and the goes backwards
you can add the "Field2" to the table command (and if needed add a sort Field2) -
| table HOSTNAME , DRQS , PRQS , WINDOW , MAINTENANCE Field2 | fillnull value="NULL" | search HOSTNAME!="NULL" AND DRQS!="NULL" AND PRQS!="NULL" AND WINDOW!="NULL" | dedup HOSTNAME | sort field2