topic Re: Parsing fields from json logs in Getting Data In

Parsing fields from json logs

splunk_svc — Fri, 21 Oct 2016 03:42:42 GMT

Hi Splunkers.
I'm attempting to search based on fields in a JSON log file
For example I am trying to search based on the "action" field from the following (sample) JSON event:

{"message":"{\"action\":\"USER_PROFILEACTION\"}","requestfrom":"source","responsestatus":"403","username":"user@name.com","station":"/level1/profile","resource":"/level1/profile","responsetime":275,"starttime":1476061950172,"finishtime":1476061950447}

I've attempted to use spath and also a rex pipe but have had no luck.
(i.e. here : https://answers.splunk.com/answers/418995/how-to-extract-fields-from-json-which-is-stored-)in.html

In this example it contains "USER_PROFILEACTION".
Also note that the string in the action field also contains a trailing backslash at the end of the string.
Preferably I'd like to strip this in the process.

Any attempts I've made end up converting the field that the raw JSON log is stored in into a multivalue field, with a a second copy of the JSON log.

Thanks in advance.

Re: Parsing fields from json logs

gokadroid — Fri, 21 Oct 2016 05:02:23 GMT

Try this if there is only one \ in the string and that's at the end of action string. If there are other slashes then please paste some samples of action field so more apt regex can be built.

yourBaseSearch
| rex field=_raw ".*action\\\":\\\"(?<action>[^\\]+)"
|complete your search

UPDATED SEARCH
If you want to extract all the key value pairs from the search and not just the action field then try this below search:

yourBaseSearch
| rex max_match=0 field=_raw "\"(?<key>[^\"]+)\":(\"|\"{\\\")*(?<value>[^,]+)"
 | table key, value
 |  eval z=mvzip(key, value, "~") 
 | mvexpand z
 | table z
 |rex field=z "(?<key>[^~]+)~(?<value>.*)"
 | rex mode=sed field=value "s/{//g
 s/}//g
 s/\\\//g"
 |table key value 
 | rex max_match=0 field=value "\"(?<key>[^\"]+)\":\"(?<value>[^,]+)" 
 | table key, value
 |  eval z=mvzip(key, value, "~") 
 | mvexpand z
 | table z
 | rex field=z "(?<key>[^~]+)~(?<value>.*)"
 | table key, value
 | rex mode=sed field=value "s/\"//g"

Re: Parsing fields from json logs

splunk_svc — Fri, 21 Oct 2016 06:01:15 GMT

Just tried that.
I seem to get the following error:

Error in 'rex' command: Encountered the following error while compiling the regex '.*action\":\"(?<action>[^\]+)': Regex: missing terminating ] for character class

Will post some samples of action field. (Need to to remove identifying data first) them first.

Re: Parsing fields from json logs

gokadroid — Fri, 21 Oct 2016 06:17:28 GMT

Issue is you didn't copy the box properly as there is one \ missing inside the [^\] box.

put two slashes in the box [^\\]+)

The data if is exactly like you have put in question should work with the above regex, unless there are no slashes in your json data. However try this below query that should extract all the fields for you. Updating this in answer above as well:

yourBaseSearch    
| rex max_match=0 field=_raw "\"(?<key>[^\"]+)\":(\"|\"{\\\")*(?<value>[^,]+)"
    | table key, value
    |  eval z=mvzip(key, value, "~") 
    | mvexpand z
    | table z
    |rex field=z "(?<key>[^~]+)~(?<value>.*)"
    | rex mode=sed field=value "s/{//g
    s/}//g
    s/\\\//g"
    |table key value 
    | rex max_match=0 field=value "\"(?<key>[^\"]+)\":\"(?<value>[^,]+)" 
    | table key, value
    |  eval z=mvzip(key, value, "~") 
    | mvexpand z
    | table z
    | rex field=z "(?<key>[^~]+)~(?<value>.*)"
    | table key, value
    | rex mode=sed field=value "s/\"//g"

Re: Parsing fields from json logs

Masa — Wed, 26 Oct 2016 21:16:16 GMT

Back slash is confusing.
Splunk is supposed to detect json format.

So, in your case, message field should be populated as follows;

message = {"action":"USER_PROFILEACTION"}

Note: backslash in _raw exists while json field extraction removes it as it is escaping double-quote(").

In that case, the following rex should populate action=USER_PROFILEACTION

| rex field=message ":\"(?<action>.*)\""

Ideally, your message value makes use of json format like this

"message": {"action": "USER_PROFILEACTION" },

then, Splunk automatically extracts message.action=USER_PROFILEACTION

Re: Parsing fields from json logs

splunk_svc — Thu, 27 Oct 2016 03:09:42 GMT

Thanks gokadroid.
I'll put this down to a copy and paste snafu.
The forum is removing the second backslash and needs an additional one used to escape the first one.

Will have a bit of a play with your revised search string.

Re: Parsing fields from json logs

splunk_svc — Thu, 27 Oct 2016 03:10:47 GMT

Unfortunately Masa, that is the way the data is being presented.
We don't have the opportunity to modify the source to exclude the extra characters.

Re: Parsing fields from json logs

JDukeSplunk — Thu, 27 Oct 2016 03:36:34 GMT

For what it's worth, I had success using XMLKV on one of my JSON feeds.

https://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Xmlkv

Re: Parsing fields from json logs

splunk_svc — Thu, 27 Oct 2016 04:10:56 GMT

Have been comparing results of original and full search that gokadroid supplied.

Still get the following error when using the smaller of the two searches. i.e. to get just the message field.

Error in 'rex' command: Encountered the following error while compiling the regex '.*action\":\"(?<action>[^\]+)': Regex: missing terminating ] for character class.

The actual query itself does contain [^\] in it.
It's only the error message that Splunk returns that has the single backslash in it.
i.e. "[^\]"

Re: Parsing fields from json logs

splunk_svc — Thu, 27 Oct 2016 04:50:53 GMT

Sorry about the confusion.

The trailing backslash is NOT coming in with the data.
This was only displayed when I viewed the output in table mode.
The table mode was escaping the quotes with a backslash.
Viewing it in raw mode shows the field(s) to be quite delimited (only).