3

I am using an API to get data from NetFlow Analyzer. I get a JSON file formatted like this;

{"startTime":"2017-12-29 11:58","resultVector":[{"port":"*","app":"Unknown_App","dscpCode":"0","traffic":"4.77 MB","dscp":"Default","src":"20.xx.xx.2","dst":"10.xx.xx.1","dstport":"*","prot":"Unknown"}],"Type":"DestinationIN","devDetails":{"deviceID":"5000006","Total":"4.77 MB"},"TimeZone":"America/Chicago","endTime":"2018-01-05 11:58"}

I have been trying to use json2csv (https://github.com/jehiah/json2csv) found at github, and did have success using for a different API & JSON output format. When I run

json2csv -k port,app,dscpCode,traffic,dscp,src,dst,dstport,prot -i filein.json -o fileout2.csv

I get a csv file with nothing but ",,,,,". What I am trying to get are the traffic, source IP, and destination IP.

Running

json2csv -k startTime,resultVector -i filein.json -o fileout2.csv

gives me this output which while close, it not csv really

2017-12-29 11:58,[map[dscpCode:0 src:20.xx.xx.2 dst:10.xx.xx.1 prot:Unknown port:* app:Unknown_App dstport:* traffic:4.77 MB dscp:Default]]

Checked few online sites that report this is a valid RFC 4627 JSON. Anyone else familiar with json2csv, or if nothing else another cli tool for Linux that I can use in a script to convert?

jsotola
  • 2,238
  • 1
  • 10
  • 22
Sean W
  • 57
  • 1
  • 1
  • 5

3 Answers3

12

This is a good job for jq processor:

jq -r '.resultVector[] | [.traffic, .src, .dst] | @csv' filein.json > fileout2.csv

The final fileout2.csv contents:

"4.77 MB","20.xx.xx.2","10.xx.xx.1"
RomanPerekhrest
  • 88,541
  • 4
  • 65
  • 105
  • Thank you so much, that worked perfectly. I had tried jq but did not have the output format ".resultVector[] | [.traffic, .src, .dst]" correct, so I gave up and was trying with json2csv since I had previously luck using that. I appreciate this – Sean W Jan 05 '18 at 21:00
  • @RomanPerekhrest This does not produce headers. Can/Will you offer an elegant solution that does? – jasonleonhard Apr 23 '19 at 22:53
  • @jasonleonhard, Did the OP requested preserving any headers? Had the original question any mentions about "headers"? – RomanPerekhrest Apr 24 '19 at 09:45
  • @RomanPerekhrest fair point, however it is very common and needed in many instances. – jasonleonhard Apr 24 '19 at 18:10
0

This is another CLI tool for conversion from JSON to CSV:

https://github.com/luca-vercelli/json2csv

It is able to parse nested JSON's.

This is the output with OP data:

startTime,Type,devDetails-deviceID,devDetails-Total,TimeZone,endTime,resultVector-port,resultVector-app,resultVector-dscpCode,resultVector-traffic,resultVector-dscp,resultVector-src,resultVector-dst,resultVector-dstport,resultVector-prot
2017-12-29 11:58,DestinationIN,5000006,4.77 MB,America/Chicago,2018-01-05 11:58,*,Unknown_App,0,4.77 MB,Default,20.xx.xx.2,10.xx.xx.1,*,Unknown
luca.vercelli
  • 898
  • 7
  • 24
-2

Typically I prefer cli tools too

If you want to quickly format some json into csv you might also checkout this:

https://json-csv.com/

Provides file upload as well as copy pasting for quick results.

jasonleonhard
  • 12,047
  • 89
  • 66