Splunk Search

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

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

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

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

Path Finder

@vnravikumar Thanks..this worked for me.

0 Karma

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

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