Splunk Search

How do you convert numeric JSON arrays to comma separated strings?

karthi25
Path Finder

I have a JSON with the following format:

{
  "TestSplunkLog" : {
    "TestFailureLog" : {
      "appName" : "***",
      "eventType" : "****",
      "payload" : {
        "level" : "ERROR",
        "startTime" : "2019-02-06 06:20:13",
        "failureCount" : 1000,
        "errorCode" : 0,
        "errorDescription" : "ERROR: column \"***\" is of type numeric but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 860",
        "failureIdList" : [ 2185745757, 2185745763, 2185745765, 2185960646, 2185960648, 2185960650, 2187284798, 2187345395, 2187345398, 2187353443 ],
        "endTime" : "2019-02-06 06:20:24"
      }
    }
  }
}

Now, I need to get all the failureidlist as a single comma separated string.

Can anyone please help me with some sample queries?

0 Karma
1 Solution

vnravikumar
Champion

Hi @karthi25

Try this and let me know

source="test1.json" host="dpds" sourcetype="_json" 
| rename TestSplunkLog.TestFailureLog.payload.failureIdList{} as failureList 
| eval new_failureList =mvjoin(failureList,",") 
| table new_failureList

View solution in original post

0 Karma

vnravikumar
Champion

Hi @karthi25

Try this and let me know

source="test1.json" host="dpds" sourcetype="_json" 
| rename TestSplunkLog.TestFailureLog.payload.failureIdList{} as failureList 
| eval new_failureList =mvjoin(failureList,",") 
| table new_failureList
0 Karma

karthi25
Path Finder

@vnravikumar Thanks..this worked for me.

0 Karma

MoniM
Communicator

Hi @karthi25 ,
What exactly you need in your output ? As the data is in jason format so the output will be in field-value i.e failureIdList" = 2185745757, 2185745763, 2185745765, 2185960646, 2185960648, 2185960650, 2187284798, 2187345395, 2187345398, 2187353443

Thanks

0 Karma

karthi25
Path Finder

@MoniM I have tried this query

basesearch |spath path=TestSplunkLog.TestFailureLog.payload.failureIdList output=FAILURELIST| eval SNO=1 | accum SNO | table SNO,APPNAME,FAILURECOUNT,FAILURELIST,ERRORDESCRIPTION,STARTTIME,ENDTIME

All the records are populated correctly, but failurelist is not getting populated. In a table, at a single row I want like

1 | appname1 | 10 |2185745757, 2185745763, 2185745765, 2185960646, 2185960648, 2185960650, 2187284798, 2187345395, 2187345398, 2187353443 |  INVALID RECORD | NULL | NULL
0 Karma
Get Updates on the Splunk Community!

Earn a $35 Gift Card for Answering our Splunk Admins & App Developer Survey

Survey for Splunk Admins and App Developers is open now! | Earn a $35 gift card!      Hello there,  Splunk ...

Continuing Innovation & New Integrations Unlock Full Stack Observability For Your ...

You’ve probably heard the latest about AppDynamics joining the Splunk Observability portfolio, deepening our ...

Monitoring Amazon Elastic Kubernetes Service (EKS)

As we’ve seen, integrating Kubernetes environments with Splunk Observability Cloud is a quick and easy way to ...