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

[vishalduttauk]

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

[ITWhisperer]

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]

Thanks @ITWhisperer 

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

[ITWhisperer · Accepted solution]

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]

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?

[ITWhisperer]

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

[vishalduttauk]

Thank you @ITWhisperer !!

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