topic Re: JSON Transformation in Getting Data In

JSON Transformation

willryals — Mon, 11 Jan 2021 18:59:09 GMT

Hi guys,

So I have a multi array json input. What I am looking to do is have it split the initial raw data into seperate JSON events.

EX:

{
"response": {
"method": "switchvox.callQueues.getCurrentStatus",
"result": {
"call_queue": {
"extension": "2070",
"strategy": "ring_all",
"queue_members": {
"queue_member": [
{
"paused_time": "1626",
"completed_calls": "1",
"paused_since": "2021-01-08 08:59:28",
"talking_to_name": "",
"login_type": "login",
"order": "1",
"login_time": "7265",
"extension": "4826",
"max_talk_time": "835",
"time_of_last_call": "2021-01-08 08:26:32",
"paused": "1",
"account_id": "1503",
"missed_calls": "0",
"logged_in_status": "logged_in",
"fullname": "",
"talking_to_number": "",
"avg_talk_time": "835"
},
{
"paused_time": "773",
"completed_calls": "1",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "2",
"login_time": "3713",
"extension": "4824",
"max_talk_time": "183",
"time_of_last_call": "2021-01-08 08:13:34",
"paused": "0",
"account_id": "1587",
"missed_calls": "1",
"logged_in_status": "logged_in",
"fullname": "",
"talking_to_number": "",
"avg_talk_time": "183"
},

{
"paused_time": "1626",
"completed_calls": "1",
"paused_since": "2021-01-08 08:59:28",
"talking_to_name": "",
"login_type": "login",
"order": "1",
"login_time": "7265",
"extension": "4826",
"max_talk_time": "835",
"time_of_last_call": "2021-01-08 08:26:32",
"paused": "1",
"account_id": "1503",
"missed_calls": "0",
"logged_in_status": "logged_in",
"fullname": "",
"talking_to_number": "",
"avg_talk_time": "835"
}

and

{
"paused_time": "773",
"completed_calls": "1",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "2",
"login_time": "3713",
"extension": "4824",
"max_talk_time": "183",
"time_of_last_call": "2021-01-08 08:13:34",
"paused": "0",
"account_id": "1587",
"missed_calls": "1",
"logged_in_status": "logged_in",
"fullname": "",
"talking_to_number": "",
"avg_talk_time": "183"
},

I think I need to use a transformation so this happens at indexing, but I am not sure how to do it while making sure Splunk still processes the resultant data and JSON.

Re: JSON Transformation

to4kawa — Sat, 16 Jan 2021 02:01:52 GMT

index=_internal | head 1 | fields _raw | eval _raw="{\"response\":{\"method\":\"switchvox.callQueues.getCurrentStatus\",\"result\":{\"call_queue\":{\"extension\":\"2070\",\"strategy\":\"ring_all\",\"queue_members\":{\"queue_member\":[{\"paused_time\":\"1626\",\"completed_calls\":\"1\",\"paused_since\":\"2021-01-08 08:59:28\",\"talking_to_name\":\"\",\"login_type\":\"login\",\"order\":\"1\",\"login_time\":\"7265\",\"extension\":\"4826\",\"max_talk_time\":\"835\",\"time_of_last_call\":\"2021-01-08 08:26:32\",\"paused\":\"1\",\"account_id\":\"1503\",\"missed_calls\":\"0\",\"logged_in_status\":\"logged_in\",\"fullname\":\"\",\"talking_to_number\":\"\",\"avg_talk_time\":\"835\"},{\"paused_time\":\"773\",\"completed_calls\":\"1\",\"paused_since\":\"\",\"talking_to_name\":\"\",\"login_type\":\"login\",\"order\":\"2\",\"login_time\":\"3713\",\"extension\":\"4824\",\"max_talk_time\":\"183\",\"time_of_last_call\":\"2021-01-08 08:13:34\",\"paused\":\"0\",\"account_id\":\"1587\",\"missed_calls\":\"1\",\"logged_in_status\":\"logged_in\",\"fullname\":\"\",\"talking_to_number\":\"\",\"avg_talk_time\":\"183\"}]}}}}}" | rex mode=sed "s/.*\[// s/\].*// s/(,)(?=\{\"paused_time)/#/g" | eval _raw=split(_raw,"#") | stats count by _raw | spath

props.conf:

SEDCMD-trim = s/.*\[// s/\].*//
LINE_BREAKER = (,)\{\"paused_time

Re: JSON Transformation

willryals — Mon, 18 Jan 2021 19:28:01 GMT

to4kawa,

Thank you for your help! Just to make sure I am understanding this correctly, the code block should go into the props.conf as well as the second bit. Is that right, or am I misinterpreting your statement? I just haven't previously seen eval as an option that is ran at index and I figured it would be best to double-check before proceeding blindly on my end.

Re: JSON Transformation

to4kawa — Mon, 18 Jan 2021 22:51:52 GMT

I'm writing the example because the search results are easy to understand as a test of props.conf.

| rex mode=sed "s/.*\[// s/\].*// s/(,)(?=\{\"paused_time)/#/g"
→　SEDCMD-trim = s/.*\[// s/\].*// and LINE_BREAKER = (,)\{\"paused_time

It's hard to understand LINE_BREAKER, but you can specify the part you want to split with a regular expression. Here's the # and (,).

The behavior is a little different between rex and LINE_BREAKER, so the way I write it is also different.

e.g. [my blog _ english translated](https://qiita.com/toshikawa/items/062000fba552258d5289#propsconf%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8Bline_breaker%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6)