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!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...