Splunk Search

Why does REST API return nested json as string not json?

bdunstan
Path Finder

Hi,

I am using the REST API to pull data from splunk, using the output_mode=json.

The data that is returned is a mix of strings and JSON (objects) and I am trying to work out a way for the API to return the entire data set as JSON.

For Example:
Curl Command:
curl -k -u 'user1'' https://splunk-server:8089/servicesNS/admin/search/search/jobs/export -d 'preview=false' -d 'output_mode=json' -d 'search=|savedsearch syslog_stats latest="-2d@d" earliest="-3d@d" span=1' | jq .

Results: Note how the result is in JSON, but devices is an array of strings not json.
{
  "preview": false,
  "offset": 0,
  "lastrow": true,
  "result": {
    "MsgType": "LINK-3-UPDOWN",
    "devices": [
      "{\"device\":\"1.1.1.1\",\"events\":12,\"deviceId\":null}",
      "{\"device\":\"2.2.2.2\",\"events\":128,\"deviceId\":1}",
      "{\"device\":\"3.3.3.3\",\"events\":217,\"deviceId\":2}"
    ],
    "total": "357",
  }
}

Query:
| tstats count as events where index=X-syslog Severity<=4 earliest=-3d@d latest=-2d@d by _time, Severity, MsgType Device span=1d
| search MsgType="LINK-3-UPDOWN"
| eval devices=json_object("device", Device, "events", events, "deviceId", deviceId )
| fields - Device events _time Filter UUID Regex deviceId addressDeviceId
| table MsgType devices

Query Result in UI:

MsgType devices total

LINK-3-UPDOWN
{"device":"1.1.1.1","events":12,"deviceId":null}
{"device":"2.2.2.2","events":128,"deviceId":null}
{"device":"3.3.3.3","events":217,"deviceId":null}
357

 

As can be seen from the UI the device is in JSON format (using json_object), but from the curl result it is a string in json format - is there a way for the query to return the whole result as a json object, not a mix of json and strings ?

I have also tried tojson in a number of differnt ways, but no success.

Desired Result: where devices is a json object and not treated a string as above.
{
  "preview": false,
  "offset": 0,
  "lastrow": true,
  "result": {
    "MsgType": "LINK-3-UPDOWN",
    "devices": [
      {"device":"1.1.1.1","events":12,"deviceId":null}",
      {"device":"2.2.2.2","events":128,"deviceId":1}",
      {"device":"3.3.3.3","events":217,"deviceId":2}"
    ],
    "total": "357",
  }
}

I can post process the strings into JSON, but I would rather get JSON from SPlunk directly.

Thanks !

 

 

 

 

Labels (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

That is because the original data field "devices" contain strings and not JSON objects. (Even though the strings are themselves escaped JSON objects.)  For the output to be like your desired results, search UI will give you this instead:

lastrowoffsetpreviewresult.MsgType
result.devices{}.device
result.devices{}.deviceId
result.devices{}.events
results.total
true0falseLINK-3-UPDOWN
1.1.1.1
2.2.2.2
3.3.3.3
null
1
2
12
128
217
357

Escaped JSON string is often employed by considerate developers to avoid unnecessary complexity.

0 Karma

bdunstan
Path Finder

Thanks. 

I make the assumtion (obviously wrong) that the json_object() command created a JSON object and not a string that looks like JSON.

| eval devices=json_object("device", Device, "events", events, "deviceId", deviceId )

0 Karma

yuanliu
SplunkTrust
SplunkTrust

You are not wrong.  json_object() creates an escaped JSON string in order to protect the object in ordinarily text contexts.  Many log files use this technique, especially when they are already a JSON object but need to embed a text message that includes JSON objects.  You just need to remember to unescape it when you need to access JSON.

SPL functions in a text context.  Therefore it automatically un-excapes strings created by this function. spath readily recognizes such un-escaped string.  You can examine this example,

 

| makeresults
| fields - _time
| eval my_message = json_object("key","value")
| spath input=my_message

 

The output will be

keymy_message
value{"key":"value"}
0 Karma
Get Updates on the Splunk Community!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...