I have following splunk query
(index=index_1 OR index=index_2) sourcetype=openshift_logs openshift_namespace="my_ns" openshift_cluster="*" | spath "message.url" | search "message.url"="/dummy/url/v1*" | search "message.tracers.ke-channel{}"="*" |search "message.jsonObject.payments{}.products{}.type"=GROCERY | dedup message.tracers.ke-correlation-id{} | search "message.statusCode"<400 |rename "message.jsonObject.payments{}.orderStatus.status" AS "ORDER_STATUS"| top limit=50 "ORDER_STATUS"
which gives the below output
ORDER_STATUS count percent
-----------------------------------
PAYMENT_ACCEPTED 500 70
PAYMENT_PENDING 100 20
PAYMENT_UNDER_REVIEW 90 2
PAYMENT_REDIRECTION 40 1.32
PAYMENT_NOT_ATTEMPTED10 3.11
I want to display another item in the dashbaord which should be the sum of the count of following order status: PAYMENT_ACCEPTED+PAYMENT_PENDING+PAYMENT_UNDER_REVIEW+PAYMENT_REDIRECTION i.e
500 + 100+90+40=730
Below is my query:
(index=index_1 OR index=federated:index_2) sourcetype=openshift_logs openshift_namespace="my_ns" openshift_cluster="*" | spath "message.url" | search "message.url"="/dummy/url/v1*" | search "message.tracers.ke-channel{}"="*" |search "message.jsonObject.payments{}.products{}.type"=GROCERY | search "message.statusCode"<400 | dedup message.jsonObject.id |search ("message.jsonObject.payments{}.orderStatus.status"="PAYMENT_ACCEPTED" OR "message.jsonObject.payments{}.orderStatus.status"="PAYMENT_PENDING" OR "message.jsonObject.payments{}.orderStatus.status"="PAYMENT_UNDER_REVIEW" OR "message.jsonObject.payments{}.orderStatus.status"="PAYMENT_REDIRECTION") | stats count(message.jsonObject.id)
But the sum of the count using the above query is always more than the actual total count.
Appreciate if someone can let me know where am i going wrong.
Hi @super_edition,
in the first search, you have to add the command addcoltotals (https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Addcoltotals) at the end of the search to sum al the values:
(index=index_1 OR index=index_2) sourcetype=openshift_logs openshift_namespace="my_ns" openshift_cluster="*"
| spath "message.url"
| search
"message.url"="/dummy/url/v1*"
"message.tracers.ke-channel{}"="*"
"message.jsonObject.payments{}.products{}.type"=GROCERY
"message.statusCode"<400
| rename "message.jsonObject.payments{}.orderStatus.status" AS "ORDER_STATUS"
| top limit=50 "ORDER_STATUS"
| addcoltotals count labelfield="ORDER_STATUS" label="Total Count"
then don't use dedup when you have to make a sum of values because using dedup (as @manjunathmeti said) you lose some values.
Then, don't put search terms in different search commands because in this way your search is slower: put all search terms in the same search command or (if possible) in the main search.
Ciao.
Giuseppe
Hi @super_edition,
Check dedup fields in both queries, both are different: message.tracers.ke-correlation-id{} and message.jsonObject.id.