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
Legend

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!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...