topic Re: Combine Json elements in array in Splunk Search

How to combine JSON elements in array?

jbourne89 — Thu, 14 Apr 2022 16:20:53 GMT

I have created a query similar to the below

host=nftHost index=paymeNow
source="\\\\epamjhost\Logs\*"

| rex "(Message content+\s+:+\s+|\[Handling message+\s+:+\s+|\[Handling command of type CheckCommand:+\s+)(?<json>\{.*)"

| spath input=json
| table _time, MessageTypeDesc, CurrentState, CaseId, TaskType, Attributes{}.AttributeName, Attributes{}.JsonValue, _raw

The below json is obtained from the rex expression and spath is used to parse it.

{
"TaskId" : "1",
"CurrentState" : "COMPLETED",
"RequestedAction" : null,
"User" : "NFTPAYME",
"Attributes" : [{
"AttributeName" : "transactionId",
"AttributeType" : "int",
"JsonValue" : "4"
}, {
"AttributeName" : "Enabled",
"AttributeType" : "boolean",
"JsonValue" : "false"
}, {
"AttributeName" : "holdType",
"AttributeType" : "string",
"JsonValue" : ""
}, {
"AttributeName" : "isSettlement",
"AttributeType" : "boolean",
"JsonValue" : "false"
}, {
"AttributeName" : "isIntraday",
"AttributeType" : "boolean",
"JsonValue" : "false"
}, {
"AttributeName" : "isReleaseReady",
"AttributeType" : "boolean",
"JsonValue" : "false"
}, {
"AttributeName" : "isStat",
"AttributeType" : "boolean",
"JsonValue" : "false"
}, {
"AttributeName" : "StatusList",
"AttributeType" : "string",
"JsonValue" : ""
},
],
"TaskType" : "Settle",
"CaseId" : "1",
}

Attributes contains an array of objects so my question is how to take the attributes and create a single string from the whole array?

_time

MessageTypeDesc

CurrentState

CaseId

TaskType

Attributes

_raw

transactionId:4
Enabled:true

holdType:

isSettlement:false

Re: Combine Json elements in array

ITWhisperer — Thu, 14 Apr 2022 11:19:21 GMT

| streamstats count as row | spath input=json Attributes{} output=Attributes | mvexpand Attributes | spath input=Attributes | eval Attribute=AttributeName.": ".JsonValue | stats values(*) as * by row

Re: Combine Json elements in array

venky1544 — Thu, 14 Apr 2022 11:35:30 GMT

Hi @jbourne89

not sure if i got the understanding correct are you looking to combine all the values in the attributes field to create a single string try the below search you can join them using , | or space based on your requirement in between quotes " " in mvjoin function

|rename Attributes{}.AttributeName AS attributes| eval Exception=mvjoin(attributes," ") |table attributes Exception

Note :- if this help karma points are appreciated /and please accept the solution if it worked for you it might help others as well

Re: Combine Json elements in array

jbourne89 — Thu, 14 Apr 2022 14:44:28 GMT

@venky1544 Thank you for replying. This is very close to what I want combine the values in the AttributeName field with the value in the JsonValue field. Currently the JsonValue items are appended at the bottom of the AttributeName list.

What I would like is a single string containing AttributeName:JsonValue

Re: Combine Json elements in array

jbourne89 — Thu, 14 Apr 2022 14:49:46 GMT

This output is correct but I have a few questions. What is the purpose of streamstats count as row and stats values(*) by row?

Re: Combine Json elements in array

ITWhisperer — Thu, 14 Apr 2022 15:28:51 GMT

The streamstats tags the events with a unique number because the mvexpand creates multiple events for each attribute, and the stats brings them back together using the unique number to ensure they are collected together correctly.

Re: Combine Json elements in array

jbourne89 — Thu, 14 Apr 2022 16:16:39 GMT

That explains why the number of events jumped up. Is there anyway to display the stats as unique events?

Re: Combine Json elements in array

ITWhisperer — Thu, 14 Apr 2022 16:50:09 GMT

Do you mean something like this?

| streamstats count as row | spath input=json Attributes{} output=Attributes | mvexpand Attributes | spath input=Attributes | eval {AttributeName}=JsonValue | stats values(*) as * by row

Re: How to combine JSON elements in array?

jbourne89 — Thu, 14 Apr 2022 17:34:31 GMT

Thanks @ITWhisperer and @venky1544