topic Re: json to table in Getting Data In

json to table

arun_kant_sharm — Wed, 16 Oct 2019 04:56:54 GMT

Hi Experts,

I want to convert Json format into table.
My data have below field
[ [-]
{ [-]
day: Tue
dayOfMonth: 15
duration: (00:00)
month: Oct
program: ssh:notty
sourceHost: ljp1gwd01.axa-di
time: 15:09 - 15:09
user: shaikh
}
{ [-]
day: Thu
dayOfMonth: 3
duration: (00:00)
month: Oct
program: ssh:notty
sourceHost: ljp1gwd01.axa-di
time: 16:33 - 16:33
user: shaikh
}
]

Re: json to table

kamlesh_vaghela — Wed, 30 Sep 2020 02:33:21 GMT

@arun_kant_sharma
Can you please share _raw from your event?

Re: json to table

arun_kant_sharm — Wed, 16 Oct 2019 05:14:58 GMT

[{"month": "Oct", "program": "ssh:notty", "user": "shaikh", "time": "15:09 - 15:09", "duration": "(00:00)", "sourceHost": "ljp1gwd01.axa-di", "day": "Tue", "dayOfMonth": "15"}, {"month": "Oct", "program": "ssh:notty", "user": "shaikh", "time": "16:33 - 16:33", "duration": "(00:00)", "sourceHost": "ljp1gwd01.axa-di", "day": "Thu", "dayOfMonth": "3"}]

Re: json to table

kamlesh_vaghela — Wed, 30 Sep 2020 02:33:25 GMT

@arun_kant_sharma

Can you please try this?

YOUR_SEARCH  | spath path="{}." output=data | mvexpand data | eval _raw=data | spath | table day dayOfMonth month duration program sourceHost time user

Example:

| makeresults | eval _raw="[{\"day\": \"Tue\",\"dayOfMonth\": \"15\",\"duration\": \"(00:00)\",\"month\": \"Oct\",\"program\": \"ssh:notty\",\"sourceHost\": \"ljp1gwd01.axa-di\",\"time\": \"15:09 - 15:09\",\"user\": \"shaikh\" },{\"day\": \"Thu\",\"dayOfMonth\": \"3\",\"duration\": \"(00:00)\",\"month\": \"Oct\",\"program\": \"ssh:notty\",\"sourceHost\": \"ljp1gwd01.axa-di\",\"time\": \"16:33 - 16:33\",\"user\": \"shaikh\" } ]" | spath path="{}." output=data | mvexpand data | eval _raw=data | spath | table day dayOfMonth month duration program sourceHost time user

Re: json to table

poete — Wed, 30 Sep 2020 02:38:04 GMT

Hello @arun_kant_sharma,

here it is:

| makeresults 
| eval _raw="[ { \"day\": \"Tue\",\"dayOfMonth\": 15,\"duration\": \"(00:00)\",\"month\": \"Oct\",\"program\": \"ssh:notty\",\"sourceHost\": \"ljp1gwd01.axa-di\",\"time\": \"15:09 - 15:09\",\"user\": \"shaikh\"},{ \"day\": \"Thu\",\"dayOfMonth: 3,\"duration\": \"(00:00)\",\"month\": \"Oct\",
\"program\": \"ssh:notty\",\"sourceHost\": \"ljp1gwd01.axa-di\",\"time: \"16:33 - 16:33\",\"user\": \"shaikh\",}]"
| spath 
| rename {}.* as *
| fields - _raw
| table *