Splunk Search

Transforming results from json api to a table

willryals
Engager

Hi there!

I am kinda new to Splunk so I apologize if my wording is off, but I am trying to collect metrics from a weirder phone system, and the way it presents data is like this:

{
"response": {
"method": "switchvox.callQueues.getCurrentStatus",
"result": {
"call_queue": {
"extension": "***",
"strategy": "ring_all",
"queue_members": {
"queue_member": [
{
"paused_time": "15911",
"completed_calls": "8",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "1",
"login_time": "32963",
"extension": "***",
"max_talk_time": "661",
"time_of_last_call": "2021-01-06 13:52:31",
"paused": "0",
"account_id": "***",
"missed_calls": "14",
"logged_in_status": "logged_off",
"fullname": "***",
"talking_to_number": "",
"avg_talk_time": "443"
},

Where it restates the variable name with each record. So far I have the rest api module pulling the data, and doing some light translation on it (it still looks like an array but it is at least identifying each extension). Is there a way to get Splunk to use the first portion of each response as a field name while tying it to the same record?

 

EX:

{
"paused_time": "15911",
"completed_calls": "8",
"paused_since": "",
"talking_to_name": "",
"login_type": "login",
"order": "1",
"login_time": "32963",
"extension": "***",
"max_talk_time": "661",
"time_of_last_call": "2021-01-06 13:52:31",
"paused": "0",
"account_id": "**1",
"missed_calls": "14",
"logged_in_status": "logged_off",
"fullname": "***",
"talking_to_number": "",
"avg_talk_time": "443"
}, 

getting converted to 

 

Account_IDlogin_timemissed_callspaused_sinceavg_talk_timemax_talk_time

 

 

**13296314 443661**2329450 250450

 

Labels (1)
0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi @willryals ,

Yes, on some json events especially arrays cannot be parsed default successfully. That is why you should use this kind of search commands. 

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

willryals
Engager

So I went ahead and created new sources and have them writing to a new index, but it still appears to be disregarding the SPL. 2021-01-07 18_19_30-Search _ Splunk 8.1.1.pngimage.png

 

So far the only thing I have is the response type set to JSON on the source... 

 

Are there any other ideas you can think of?

 

0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi @willryals,

You can try below;

| eval _raw=replace(replace(_raw,"\[",""),"\]","") 
| spath path=response.result.call_queue.queue_members output=queue_members
| spath input=queue_members
| rename queue_member.* AS *
| table account_id login_time	missed_calls	paused_since	avg_talk_time	max_talk_time

 

If this reply helps you an upvote is appreciated.

If this reply helps you an upvote and "Accept as Solution" is appreciated.

willryals
Engager

@scelikok Thank you for the fast reply! While Splunk takes those arguments, it doesn't appear to actually be parsing the data. I would add these arguments to the search right?

 

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...