Getting Data In

json to table

arun_kant_sharm
Path Finder

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
}
]

Tags (2)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

View solution in original post

0 Karma

poete
Builder

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 *
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@arun_kant_sharma
Can you please share _raw from your event?

0 Karma

arun_kant_sharm
Path Finder

[{"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"}]

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...