-1

I read similar question but not found an answer. I have this data in input:

{  "connectionHistory": [
{
  "endTime": 1585571806,
  "bytesSent": 31588,
  "startTime": 1585571453,
  "duration": 353,
  "bytesReceived": 68711,
  "virtualIpAddress": "10.20.1.102",
  "remoteIpAddress": "172.16.15.183"
},
{
  "endTime": 1585591333,
  "bytesSent": 21927,
  "startTime": 1585591095,
  "duration": 238,
  "bytesReceived": 51041,
  "virtualIpAddress": "10.20.1.102",
  "remoteIpAddress": "172.16.13.75"
},
{
  "endTime": 1585592547,
  "bytesSent": 4630423,
  "startTime": 1585591333,
  "duration": 1214,
  "bytesReceived": 678052,
  "virtualIpAddress": "10.20.1.102",
  "remoteIpAddress": "172.16.13.75"
},
{
  "endTime": 1585743727,
  "bytesSent": 2153310,
  "startTime": 1585743512,
  "duration": 215,
  "bytesReceived": 499382,
  "virtualIpAddress": "10.20.1.102",
  "remoteIpAddress": "172.16.12.209"
}

]}

And like to have in output something like:

Start               End                 Duration    IP client       IP remote      Received       Sent
01 Apr 2020, 16:13  01 Apr 2020, 16:15  02m 11s     10.20.1.102     5.170.193.103   475.15 K    2.01 M
01 Apr 2020, 14:18  01 Apr 2020, 14:22  03m 35s     10.20.1.102     5.170.192.209   487.68 K    2.05 M
30 Mar 2020, 20:02  30 Mar 2020, 20:22  20m 14s     10.20.1.102     5.170.193.75    662.16 K    4.42 M
30 Mar 2020, 19:58  30 Mar 2020, 20:02  03m 58s     10.20.1.102     5.170.193.75    49.84 K     21.41 K
30 Mar 2020, 14:30  30 Mar 2020, 14:36  05m 53s     10.20.1.102     5.170.195.183   67.1 K      30.85 K

Tried to play with jq, but with no good results ...

Hints appreciated ;-)

Thanks, P.

PaulVM
  • 1
  • What have you tried exactly? Could you at least get the desired output without headers with `@tsv` filter? – oguz ismail Apr 01 '20 at 15:39
  • I am a jq absolute beginner (discovered it 2 days ago), so my attempts and results are very few interesting. – PaulVM Apr 01 '20 at 20:38
  • I Tried trivial extraction of values using basic syntax like : jq -r '.connectionHistory | map(.startTime), map(.endTime), map(.duration) ' that give something like: [ 1585571453, 1585591095, 1585591333, 1585743512, 1585750427 ] [ 1585571806, 1585591333, 1585592547, 1585743727, 1585750558 ] [ 353, 238, 1214, 215, 131 ] That need be transposed (not able to do it right), and next I want to do the maths to the time numbers ... – PaulVM Apr 01 '20 at 20:47

1 Answers1

0

I did some attempts and search on the net. Found code that help (jq: Object cannot be csv-formatted, only array).

Created file json2csv.jq containing:

  def json2headers:
  def isscalar: type | . != "array" and . != "object";
  def isflat: all(.[]; isscalar);
  paths as $p
  | getpath($p)
  | if type == "array" and isflat then $p
     elif isscalar and (($p[-1]|type) == "string") then $p
     else empty end ;

def json2array($header):
  def value($p):
    try getpath($p) catch null
    | if type == "object" then null else . end;
  [$header[] as $p | value($p)];

def json2csv:
  ( [.[] | json2headers] | unique) as $h
  | ([$h[]|join("_") ],
     (.[]
      | json2array($h)
      | map( if type == "array" then map(tostring)|join("|") else tostring end)))
  | @csv ;

Call it using: 

 jq -r -L. 'include "json2csv"; json2csv' connAAA.json

I got:

"bytesReceived","bytesSent","duration","endTime","remoteIpAddress","startTime","virtualIpAddress"
"9510","4657","81","1585511362","192.168.101.91","1585511281","10.20.1.6"
"48586","52696","1956","1585514599","192.168.101.91","1585512643","10.20.1.6"
"11829","7399","153","1585514835","192.168.101.91","1585514682","10.20.1.6"
"13871","10318","330","1585518156","192.168.101.91","1585517826","10.20.1.6"

If I use @tsv instead of @csv I got:

bytesReceived   bytesSent       duration        endTime remoteIpAddress startTime       virtualIpAddress
9510    4657    81      1585511362      192.168.101.91  1585511281      10.20.1.6
48586   52696   1956    1585514599      192.168.101.91  1585512643      10.20.1.6
11829   7399    153     1585514835      192.168.101.91  1585514682      10.20.1.6
13871   10318   330     1585518156      192.168.101.91  1585517826      10.20.1.6

That is near my desidered result. Now (before probably), I need to convert Unix Timestamp to DateTime.

I suppose using todateiso8601 function, but can't insert it correctly.

Suppose this isn't difficult for a jq skilled guy ;-)

Thanks, P.

PaulVM
  • 1