Splunk Search-Where is key dynamic?

[Sanjana]

Hello ,

I have data like below. I need to frame a query such that I can calculate number of desync for each rate-parity-group.

For example:

"rate-parity-group":{"CN":{"avail":11,"price":11}}}

rate-parity-group":{"CK":{"avail":18,"price":0},"CL":{"avail":36,"price":0},"CM":{"avail":18,"price":0}}},

"rate-parity-group":{"CL":{"avail":18,"price":0},"CM":{"avail":36,"price":0}}}

Expected outcome 

rate-parity-group  total-desync

CL                                        54(36+18)

CM                                      54

CK                                       18

Since CK,CM,CL all these rate-parity-group is dynamic so I m facing problem. 

Could someone help me to get the desync count at rate-parity-group.

Sample data attached in screenshot.

Thanks in Advance

[Sanjana]

Hello @ITWhisperer 

I am working on another splunk query , looking some input from your side.

 have Logger lines as below:

job MONITOR-DESYNC-3-20I-ERNC: { "chain":"PR1", "nbProperties":1345, "propertyStartCount":1, "nbPropertyPerExecution":5, "propertyEndCount":6, "nbPropertyForCurrentExecution":5 }

job MONITOR-DESYNC-3-20I-ERNC: { "chain":"PR2", "nbProperties":1345, "propertyStartCount":6, "nbPropertyPerExecution":5, "propertyEndCount":11, "nbPropertyForCurrentExecution":5 }

------These lines continue till propertyEndCount = nbProperties but sometimes it does not get equal  and stops.

This job stopped at "propertyEndCount":1076 only

job MONITOR-DESYNC-3-6AQ-Q7Z: { "chain":"PR1", "nbProperties":1345, "propertyStartCount":1071, "nbPropertyPerExecution":5, "propertyEndCount":1076, "nbPropertyForCurrentExecution":5 }

SPlunk query to find if all hotels got covered  for each chain . In this case
Output Expected is:

only if propertyEndCount < nbProperties then I need to get output as below

chain total-property  end-property
     PR1         1345                      1076

     PR2          1345                   1000

I have tried like below :

index="index1" "propertyEndCount" "MONITOR-DESYNC-3*"
| rex field=_raw "(?<json>\{.*\})"
| spath input=json output=nb_property "nbProperties"
| spath input=json output=nb_endproperty "propertyEndCount"
| spath input=json output=chain "chain"
| bucket _time span=day
| eval nb_end = max(nb_endproperty)
| search nb_end < nb_property | reverse
| stats latest(nb_property) as property_scheduled latest(nb_endproperty) as property_covered by chain

but from this still I m getting record evenif propertyEndCount > nbProperties

could you please help me on this.

Thanks in advance!!

[ITWhisperer]

| makeresults
| eval _raw="{\"rate-parity-group\":{\"CN\":{\"avail\":11,\"price\":11}}}
{\"rate-parity-group\":{\"CK\":{\"avail\":18,\"price\":0},\"CL\":{\"avail\":36,\"price\":0},\"CM\":{\"avail\":18,\"price\":0}}}
{\"rate-parity-group\":{\"CL\":{\"avail\":18,\"price\":0},\"CM\":{\"avail\":36,\"price\":0}}}"
| multikv noheader=t
| fields _raw
| spath rate-parity-group
| spath input=rate-parity-group
| foreach *.avail
    [| eval group="<<MATCHSEG1>>"
    | eval {group}='<<FIELD>>']
| fields - *.avail *.price group rate-parity-group _time
| untable _raw group avail
| stats sum(avail) as avail by group

[Sanjana]

Hey @ITWhisperer ,

Thanks for the quick response.

I am not getting right output with this.

{\"rate-parity-group\":{\"CN\":{\"avail\":11,\"price\":11}}}

For this record my outcome should look like below

CN  22 . 

For each group I need to add both avail and price count.  To give you more insight .

This is hoe I have nested json fields. After Rate-parity-group level calculation, I am supposed to find percentage as well. To find percentage I have to use "total" object mentioned in scree.

Also I have a small doubt, you have used below as spath

| fields _raw
| spath rate-parity-group
| spath input=rate-parity-group

This is how I used but output is not as expected

index="index1" 
| multikv noheader=t
| fields _raw
| spath content.kIndexKey_EventMessage{1}.rate-parity-group
| spath input=content.kIndexKey_EventMessage{1}.rate-parity-group
| foreach *.avail
[| eval group="<<MATCHSEG1>>"
| eval {group}='<<FIELD>>']
| fields - *.avail *.price group rate-parity-group _time
| untable _raw group avail
| stats sum(avail) as avail by group

[ITWhisperer · Accepted solution]

index="index1" 
| fields _raw
| spath content.kIndexKey_EventMessage{1}.rate-parity-group
| spath input=content.kIndexKey_EventMessage{1}.rate-parity-group
| foreach *.avail
    [| eval "<<MATCHSEG1>>"='<<FIELD>>']
| foreach *.price
    [| eval "<<MATCHSEG1>>"='<<MATCHSEG1>>'+'<<FIELD>>']
| fields - *.avail *.price group rate-parity-group _time
| untable _raw group avail
| stats sum(avail) as avail by group

[Sanjana]

Hello @ITWhisperer 

Thanks for the solution. IT worked perfectly fine.

But If I am having duplicated logs ,in that case facing trouble.

Duplicate record should not get added.

2022/09/01 21:18:22.199000 [ABC:XXX-XXXX:DESYNC-20I-ST8F-I2] "rate-parity-group":{ "CJ":{ "avail":4, "price":0 }, "CK":{ "avail":8, "price":0 }, "CL":{ "avail":8, "price":0 }, "CM":{ "avail":12, "price":0 } } }

2022/09/01 21:18:22.199000 [ABC:XXX-XXXX:DESYNC-20I-ST8F-I2] "rate-parity-group":{ "CJ":{ "avail":4, "price":0 }, "CK":{ "avail":8, "price":0 }, "CL":{ "avail":8, "price":0 }, "CM":{ "avail":12, "price":0 } } }

how should I handle it?

[ITWhisperer]

You should look at why you are getting duplicated records - are they expected e.g. for redundancy purposes, or has something been misconfigured somehow?

If you can't stop the records being duplicated at source, or at least before they are indexed, you could use dedup, although this might prove to be time-consuming

| fields _raw
| dedup _raw
...

[Sanjana]

Hey Again,

Expected output is avail+price for each group .And then calculate percentage with total from data