Splunk Search

Issue with regex

bharpur183
Explorer

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

0 Karma
1 Solution

inventsekar
Ultra Champion

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

alt text

View solution in original post

0 Karma

inventsekar
Ultra Champion

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

alt text

0 Karma

bharpur183
Explorer

keep getting an error :

Unknown search command 'sourcetype'.

0 Karma

inventsekar
Ultra Champion

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

0 Karma

bharpur183
Explorer

yep. I have my regular query before the above rex you provided , but still gives me the unknown source type error

0 Karma

inventsekar
Ultra Champion

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

0 Karma

bharpur183
Explorer

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

0 Karma

inventsekar
Ultra Champion

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

0 Karma

bharpur183
Explorer

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 (?-

0 Karma

inventsekar
Ultra Champion

i have updated the Splunk Query on this answer post.. can you please try it and update me -

0 Karma

bharpur183
Explorer

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 (?-

0 Karma

inventsekar
Ultra Champion

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

0 Karma

bharpur183
Explorer

the above query is going on for the last 20 mins and its still going and producing no results

0 Karma

inventsekar
Ultra Champion

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)

0 Karma

bharpur183
Explorer

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

0 Karma

inventsekar
Ultra Champion

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..)

0 Karma

bharpur183
Explorer

ok cool.
how do I get rid of the empty rows in between ?

0 Karma

inventsekar
Ultra Champion

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)

0 Karma

bharpur183
Explorer

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

0 Karma

bharpur183
Explorer

Basically the current date shows on top and the goes backwards

0 Karma

inventsekar
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...