Export results to a file gives Invalid Json file

[MattZerfas]

When I export my results to a JSON file and then try to validate it with jsonlint.com I get an EOF error.

Is there an option I can add in my search or conf file I can change so when it exports the file in JSON format it saves the file as 1 big JSON file instead of treating each line as being valid JSON?

Here is the error I get when trying to validate it.

Parse error on line 7:
...ceId)": "87"    }}{    "preview": fal
---------------------^
Expecting 'EOF', '}', ',', ']'

Here is my a sample of my data.

{"preview":false,"result":{"_time":"2014-04-01T20:00:00.000-0500","dc(mobileDeviceId)":"87"}}
{"preview":false,"result":{"_time":"2014-04-01T21:00:00.000-0500","dc(mobileDeviceId)":"78"}}
{"preview":false,"result":{"_time":"2014-04-01T22:00:00.000-0500","dc(mobileDeviceId)":"69"}}
{"preview":false,"lastrow":true,"result":{"_time":"2014-04-01T23:00:00.000-0500","dc(mobileDeviceId)":"44"}}

Here is what the exported file should look like in order for it to be valid JSON.( [] around the whole thing and commas at the end of every line)

[{"preview":false,"result":{"_time":"2014-04-01T20:00:00.000-0500","dc(mobileDeviceId)":"87"}},
{"preview":false,"result":{"_time":"2014-04-01T21:00:00.000-0500","dc(mobileDeviceId)":"78"}},
{"preview":false,"result":{"_time":"2014-04-01T22:00:00.000-0500","dc(mobileDeviceId)":"69"}},
{"preview":false,"lastrow":true,"result":{"_time":"2014-04-01T23:00:00.000-0500","dc(mobileDeviceId)":"44"}}]

[scrown]

Why the decision to use an endpoint that streams data, resulting in a broken document, for an endpoint/behaviour where streaming (the rationale for the broken behviour) is specifically not required/used?

[ckibler12]

This is the key point. The "by design" answer is centered on the behavior of the REST API's streaming nature. But that's entirely separate from the behavior one would expect when exporting a completed query from the Splunk UI for further analysis.

The cited reason for this behavior actually contradicts the behavior in the Splunk UI:

"the response as a whole is not a valid JSON document is correct. However, this is by design,
and equivalent to what we do with XML output"
-- from https://answers.splunk.com/answers/65932/rest-api-json-invalid-format.html.

In fact, if i download query results from the UI as XML, it returns a fully-formed XML document, not a list of individual lines of XML. The UI is not treating this as a streaming response in the case of XML, but it is for JSON, which is inconsistent.

This is where the behavior of the REST API should be separated from the behavior of the UI: they have different intended purposes. If user is downloading from UI they want a valid document of either XML or JSON. If they are using the API, they should get the raw form of line-oriented records. Anyone using the API has tools to rewrite it however they want, but a user downloading from web does not.

If the Splunk team is set on delivering query results as if they are incomplete streams, could they at least bend a little and offer an option? Offer "Streaming JSON" and "JSON" both so users don't have to cobble together code snippets to get a valid file at the end.

[piotrgalas]

Still no improvement here. So sad.

[martin_mueller · Accepted solution]

You could run this to convert it after the fact:

sed -e '1s/^/[/; s/}}$/}},/g; $s/,$/]/' input > output

Some background on why you're getting what you're getting: http://answers.splunk.com/answers/65932/rest-api-json-invalid-format

[martin_mueller]

Forgot to list the ODBC URL: http://apps.splunk.com/app/1606/

[MattZerfas]

Awesome I appreciate the help very much! I will look into those 2 methods. Thank you for you time, again much appreciated!

[martin_mueller]

Okay... it appears to me as if the quickest way to get you guys started with manual exports is indeed running one sed command on the export, that doesn't really make the manual process that much more tedious.

After that getting-started phase you could either switch to D3 charting within Splunk, or an automated export between the two systems - for example by calling this REST endpoint: http://docs.splunk.com/Documentation/Splunk/latest/RESTAPI/RESTsearch#search.2Fjobs.2F.7Bsearch_id.7...
Another alternative would be to use Splunk as an ODBC source for the other system if it can use it.

[MattZerfas]

-Char limit in previous comment-

I know Splunk can do D3 charting as well but I haven't had the time to experiment with it at all yet or learn to use it and the developer I am working with hasn't touched Splunk before so he didn't have the time to get up to speed with it at this time but knew how to load info from a JSON file and display it through D3 on a webpage. So not knowing that Splunk exports JSON files the way it does we thought this way would be the easiest/quickest way to experiment with some data in D3.

[MattZerfas]

I will be manually moving it for the time being while we are experimenting with different D3 charts. Eventually in the long run we would like to have the other system just use the Splunk API to request the data but we didn't want to go through the hassle of setting that up yet. We thought we would just export the data and have the other system look at the JSON file and load it in.

[martin_mueller]

Okay - is that different system going to pull data automatically or will you be moving data manually only?

Side note - you can do D3-based charting in Splunk as well.

[MattZerfas]

I am then using that exported data to populate data points on a different system that is using some D3 charting.

[martin_mueller]

There's always a way. How to get there depends on where you want to get to.

You're exporting JSON data, and would like to have each event included in a large array instead of as its own JSON document. What's the bigger picture?

[MattZerfas]

So basically what you are saying is there isn't a way to have it do what I want? 😞

[martin_mueller]

That link is relevant to your question because the export button does call the export REST endpoint.

[MattZerfas]

Yeah I understand I can just format the data after I have saved the file but that isn't the point. I shouldn't have to. Thanks for the link. That is one I had already ran across while trying to find a solution to my problem. That's fine and makes sense why each line they send is valid JSON if I was using the API to get results but since I am exporting my results from the GUI I am always going to get all my results at once because you can't export until the search has finished.