Dashboards & Visualizations

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

super_edition
New Member

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.

Labels (1)
0 Karma

gcusello
Esteemed Legend

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

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Hi @super_edition,

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

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...