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
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
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!!
| 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
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
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
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?
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
...
Hey Again,
Expected output is avail+price for each group .And then calculate percentage with total from data