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!

Updated Team Landing Page in Splunk Observability

We’re making some changes to the team landing page in Splunk Observability, based on your feedback. The ...

New! Splunk Observability Search Enhancements for Splunk APM Services/Traces and ...

Regardless of where you are in Splunk Observability, you can search for relevant APM targets including service ...

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 ...