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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...