topic Re: SUM of count in Dashboards & Visualizations

How to display another item in the dashbaord which should be the sum of the count?

super_edition — Wed, 25 Jan 2023 16:22:28 GMT

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.

Re: SUM of count

manjunathmeti — Wed, 25 Jan 2023 06:34:01 GMT

Hi @super_edition,

Check dedup fields in both queries, both are different: message.tracers.ke-correlation-id{} and message.jsonObject.id.

Re: SUM of count

gcusello — Wed, 25 Jan 2023 07:27:22 GMT

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