Getting Data In

JSON Transformation

willryals
Engager

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"
}, 

 

to

{
"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. 

 

Labels (2)
0 Karma

to4kawa
Ultra Champion
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

willryals
Engager

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.

 

0 Karma

to4kawa
Ultra Champion

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%8B...)

0 Karma
Get Updates on the Splunk Community!

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...

What’s New in Splunk Observability – September 2025

What's NewWe are excited to announce the latest enhancements to Splunk Observability, designed to help ITOps ...

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...