Splunk Search

JSON Field Extraction names with curly brackets

dmonsag
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

somesoni2
Revered Legend

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

dmonsag
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

DalJeanis
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

dmonsag
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
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...