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!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...