topic Re: Splunk Search Where Key is dynamic in Splunk Search

Splunk Search-Where is key dynamic?

Sanjana — Mon, 29 Aug 2022 17:53:05 GMT

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

Re: Splunk Search Where Key is dynamic

ITWhisperer — Mon, 29 Aug 2022 08:21:34 GMT

| 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

Re: Splunk Search Where Key is dynamic

Sanjana — Mon, 29 Aug 2022 11:15:21 GMT

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

Re: Splunk Search Where Key is dynamic

Sanjana — Mon, 29 Aug 2022 12:59:09 GMT

Hey Again,

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

Re: Splunk Search Where Key is dynamic

ITWhisperer — Mon, 29 Aug 2022 16:47:56 GMT

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

Re: Splunk Search Where Key is dynamic

Sanjana — Thu, 08 Sep 2022 09:11:58 GMT

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 } } }

how should I handle it?

Re: Splunk Search Where Key is dynamic

ITWhisperer — Thu, 08 Sep 2022 09:46:32 GMT

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 ...

Re: Splunk Search-Where is key dynamic?

Sanjana — Thu, 15 Sep 2022 06:25:03 GMT

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!!