topic Extract multiple jsons from array in Getting Data In

Extract multiple jsons from array

amitdaniel — Wed, 30 Sep 2020 01:21:20 GMT

Hi all .

This is an example of a json i'm sending to my Splunk cloud.

{   "workers": [
    {
      "UserID": 100000906,
      "ActiveShiftName": "learning sports",
      "WorkerName": "tim",
      "CurrentRoleInShift": "Tagger",
      "IsLogged": true,
      "TimeFromLastEventSaved": "00:00:13.2323651",
      "TimeFromLastEventApproved": null,
      "TotalEventsSavedInPast30Min": 68,
      "TotalEventsApprovedInPast30Min": 0
    },
    {
      "UserID": 100002137,
      "ActiveShiftName": "learning sports",
      "WorkerName": "liverpool2",
      "CurrentRoleInShift": null,
      "IsLogged": false,
      "TimeFromLastEventSaved": null,
      "TimeFromLastEventApproved": null,
      "TotalEventsSavedInPast30Min": 0,
      "TotalEventsApprovedInPast30Min": 0
    }   ] }

I would like to build a table with this fields :
workers{}.active_shift_name
workers{}.worker_name
workers{}.worker_id CurrentRoleInShift
workers{}.TimeFromLastEventSaved
workers{}.TimeFromLastEventApproved
workers{}.IsLogged

I tried multiple commands with no success
For an example I tried this command :

index="Test1" | spath input=_raw path="workers{}.ActiveShiftName" output=active_shift_name | spath input=_raw path="workers{}.WorkerName" output=worker_name |spath input=_raw path="workers{}.UserID" output=worker_id |spath input=_raw path="workers{}.CurrentRoleInShift" output=CurrentRoleInShift |spath input=_raw path="workers{}.TimeFromLastEventSaved" output=TimeFromLastEventSaved |spath input=_raw path="workers{}.TimeFromLastEventApproved" output=TimeFromLastEventApproved |spath input=_raw path="workers{}.TotalEventsSavedInPast30Min" output=TotalEventsSavedInPast30Min |spath input=_raw path="workers{}.TotalEventsApprovedInPast30Min" output=TotalEventsApprovedInPast30Min |spath input=_raw path="workers{}.IsLogged" output=IsLogged  | table active_shift_name worker_name worker_id CurrentRoleInShift TimeFromLastEventSaved TimeFromLastEventApproved IsLogged

Can you please help ?

Thank you!
Amit

Re: Extract multiple jsons from array

vnravikumar — Mon, 22 Jul 2019 07:24:51 GMT

Try this and rename the columns as required

| makeresults 
| eval test=" {   \"workers\": [
     {
       \"UserID\": 100000906,
       \"ActiveShiftName\": \"learning sports\",
       \"WorkerName\": \"tim\",
       \"CurrentRoleInShift\": \"Tagger\",
       \"IsLogged\": true,
       \"TimeFromLastEventSaved\": \"00:00:13.2323651\",
       \"TimeFromLastEventApproved\": null,
       \"TotalEventsSavedInPast30Min\": 68,
       \"TotalEventsApprovedInPast30Min\": 0
     },
     {
       \"UserID\": 100002137,
       \"ActiveShiftName\": \"learning sports\",
       \"WorkerName\": \"liverpool2\",
       \"CurrentRoleInShift\": null,
       \"IsLogged\": false,
       \"TimeFromLastEventSaved\": null,
       \"TimeFromLastEventApproved\": null,
       \"TotalEventsSavedInPast30Min\": 0,
       \"TotalEventsApprovedInPast30Min\": 0
     }   ] }" 
| spath input=test 
| table workers{}.ActiveShiftName, workers{}.WorkerName,workers{}.CurrentRoleInShift,workers{}.TimeFromLastEventSaved,workers{}.TimeFromLastEventApproved,workers{}.IsLogged

Re: Extract multiple jsons from array

chinmoya — Mon, 22 Jul 2019 07:48:54 GMT

path="workers{}.ActiveShiftName"

Use need to enclose each field in quotes. See attachment.

Re: Extract multiple jsons from array

amitdaniel — Mon, 22 Jul 2019 08:43:32 GMT

Thank you @chinmoya !!!