Splunk Search

JSON Field Extraction names with curly brackets

Explorer

Hello
I'm currently searching over a collection of events that contains some JSON structure, when applying SPATH over the field contaning the JSON, the resulting fields for a specific node of the JSON structure may vary according to the arrays on the message. I need to do some aritmetic operations over this particular node in order to sum all the values from all the events. These are the resulting fields when using SPATH:
agreementsGroup.agreements.agreementParticipants.proportionalClaimAmount
agreementsGroup.agreements.agreementParticipants{}.proportionalClaimAmount
agreementsGroup.agreements{}.agreementParticipants{}.proportionalClaimAmount
agreementsGroup{}.agreements.agreementParticipants{}.proportionalClaimAmount

As you can see they all have different names but refer to the same data I need to sum. Here is an example of my search and the respective result:

index="idx_cuadre_core_gw" sourcetype="rbt_cuadre_gw_src_type" | spath input=msg_body | stats sum("agreementsGroup.agreements.agreementParticipants{}.proportionalClaimAmount") by "referenceIdSAP" "policy.currencyCode" | rename "referenceIdSAP" as ID_SAP "policy.currencyCode" as MONEDA sum("agreementsGroup.agreements.agreementParticipants{}.proportionalClaimAmount") as CLAIM_AMOUNT

alt text

How can I treat these "different" named fields as one in order to sum and display the table without missing any data?

0 Karma

SplunkTrust
SplunkTrust

Do every event, after spath, contain all 4 field or just one of them? If it's later, @Daljeanis's answer can create a common field name from all those different named field which you can use in your stats.

0 Karma

Explorer

After some tests I finally, use this kind of workaround which allows me to extract the values of all the nodes based on a regular expression. Not the best solution anyway but at least I was able to generate my report. If anybody comes up with a better solution I'll be glad to hear about it.

index="idx_cuadre_core_gw" sourcetype="rbt_cuadre_gw_src_type" | search "cop" "proportionalClaimAmount" | rex field=_raw max_match=0 "\"proportionalClaimAmount\":\"(?<proportionalClaimAmount>.*?)\"" | stats sum(proportionalClaimAmount)
0 Karma

SplunkTrust
SplunkTrust

Have you tried...

| eval proportionalClaimAmount =coalesce(agreementsGroup.agreements.agreementParticipants.proportionalClaimAmount,
    agreementsGroup.agreements.agreementParticipants{}.proportionalClaimAmount,
    agreementsGroup.agreements{}.agreementParticipants{}.proportionalClaimAmount,
    agreementsGroup{}.agreements.agreementParticipants{}.proportionalClaimAmount)
0 Karma

Explorer

Thanks @DalJeanis.

I tried, but it doesn't give any results, and even if it worked, given the case of a more complex JSON structure the amount of different names for the same node will be more than 4, whats makes this approach not appropiate for this situation.

0 Karma