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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...