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!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...