Getting Data In

Parsing fields from json logs

splunk_svc
Path Finder

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.

Tags (2)
0 Karma

JDukeSplunk
Builder

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

0 Karma

Masa
Splunk Employee
Splunk Employee

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

splunk_svc
Path Finder

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.

0 Karma

gokadroid
Motivator

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"
0 Karma

splunk_svc
Path Finder

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.

0 Karma

gokadroid
Motivator

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"
0 Karma

splunk_svc
Path Finder

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.

0 Karma

splunk_svc
Path Finder

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. "[^\]"

0 Karma

splunk_svc
Path Finder

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

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