json_extract returns zero results - why?

[codekiln]

I have a JSON-based log file for which every line is a valid JSON document. When searching it like this:

source="/path/to/json/logfile" message.path="/ws/ws_metrics/page_hidden/" | table message.params.page_hide_metrics

I get entries with the JSON I expect, like this: 

{"connections":[{"connection_num":1,"initialized":"2021-10-25T20:46:45.318Z","ready_state":1,"connected_duration_seconds":32.296,"ready_state_times":[null,0.512,null,null]}],"tab_session_id":"604931x|concept|1635194804","first_connection_index":0,"percent_uptime":0.9843940502316508,"duration_seconds":32.296,"page_duration_seconds":32.808}

However, when I try to use an example like example #1 given for json_extract in the splunk docs, 

source="/path/to/json/logfile" message.path="/ws/ws_metrics/page_hidden/" | eval ph_metrics = json_extract(message.params.page_hide_metrics) | table ph_metrics

I don't get any results. Why?

[codekiln]

The reason why is that field specifiers seemingly require single quotes. I found an SPL2 table of quotation marks here. Apparently, single quotes are meant for field names, and double quotes are meant for string parameters to functions.  My hypothesis is that SPL lets one use fields with dotted delimiters in certain parts of queries such as at the beginning, but not when passing a reference to fields in a function.

To solve, use single quotes around the field specifier. So this will return the entire JSON object:

source="/path/to/json/logfile" message.path="/ws/ws_metrics/page_hidden/" | eval ph_metrics = json_extract('message.params.page_hide_metrics') | table ph_metrics

And this will return just the connections attribute within the JSON: 

source="/path/to/json/logfile" message.path="/ws/ws_metrics/page_hidden/" | eval ph_metrics=json_extract('message.params.page_hide_metrics', "connections{}") | table ph_metrics

[venkatasri]

Hi @codekiln 

page_hide_metrics json element doesn't exist in payload, if you want to extract out of the payload try something like this to findout what fields being extracted.

If Auto KV json mode enabled you would see them extracted by default, otherwise spath helps to findout. then you can find which field to be extracted and figure out which function (json_extract) /command (spath) to use. 

| makeresults 
| eval _raw="{\"connections\":[{\"connection_num\":1,\"initialized\":\"2021-10-25T20:46:45.318Z\",\"ready_state\":1,\"connected_duration_seconds\":32.296,\"ready_state_times\":[null,0.512,null,null]}],\"tab_session_id\":\"604931x|concept|1635194804\",\"first_connection_index\":0,\"percent_uptime\":0.9843940502316508,\"duration_seconds\":32.296,\"page_duration_seconds\":32.808}
" 
| spath 
| fields *