Splunk Search

How to subtract total values of 2 fields in a dashboard?

vishalduttauk
Path Finder

Hi there,

I have a dashboard and I want to subtract the total number of events of 2 queries but not sure how to do it, can you help?

Query 1:

 

index=mssql sourcetype=SQL_Query source=Sales_Contracts_Activations* OR source=Sales_Contracts_Activations_BOM

 

 

Query 2:

 

index=mssql sourcetype=SQL_Query source=Esigns CALLBACK_STATUS="SUCCESS" STATUS=Complete

 

Labels (2)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Sorry, typo in field names.

index=mssql sourcetype=SQL_Query source=Sales_Contracts_Activations* OR source=Sales_Contracts_Activations_BOM OR (source=Esigns CALLBACK_STATUS="SUCCESS" STATUS=Complete)
| eval query_source=if(source="Esigns", "query2", "query1")
| stats count(eval(query_source="query1")) as count1 count(eval(query_source="query2")) as count2
| eval diff=count1-count2

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
index=mssql sourcetype=SQL_Query source=Sales_Contracts_Activations* OR source=Sales_Contracts_Activations_BOM OR (source=Esigns CALLBACK_STATUS="SUCCESS" STATUS=Complete)
| eval query_source=if(source="Esigns", "query2", "query1")
| stats count(eval(source_query="query1")) as count1 count(eval(source_query="query2")) as count2
| eval diff=count1-count2

vishalduttauk
Path Finder

Thanks @ITWhisperer 

I can see the values in the query1 and query2 but count1 count2 diff are all showing as 0

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sorry, typo in field names.

index=mssql sourcetype=SQL_Query source=Sales_Contracts_Activations* OR source=Sales_Contracts_Activations_BOM OR (source=Esigns CALLBACK_STATUS="SUCCESS" STATUS=Complete)
| eval query_source=if(source="Esigns", "query2", "query1")
| stats count(eval(query_source="query1")) as count1 count(eval(query_source="query2")) as count2
| eval diff=count1-count2

vishalduttauk
Path Finder

Thank you and that has worked 👍

I've noticed we have got duplicates with our data. How can I dedup on PROPOSALNUMBER and PROPOSAL_NUMBER?



0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Assuming some of your events have PROPOSALNUMBER and some events have PROPOSAL_NUMBER, you could try this

| eval PROPOSAL_NUMBER=coalesce(PROPOSAL_NUMBER, PROPOSALNUMBER)
| dedup PROPOSAL_NUMBER
0 Karma

vishalduttauk
Path Finder

Thank you @ITWhisperer !!

 

I've checked and its worked a treat as there are no duplicate propsals

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...