topic Re: Subtract total values of 2 fields in Splunk Search

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

vishalduttauk — Wed, 27 Sep 2023 19:27:21 GMT

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

Re: Subtract total values of 2 fields

ITWhisperer — Wed, 27 Sep 2023 09:50:26 GMT

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

Re: Subtract total values of 2 fields

vishalduttauk — Wed, 27 Sep 2023 15:38:01 GMT

Thanks @ITWhisperer

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

Re: Subtract total values of 2 fields

ITWhisperer — Wed, 27 Sep 2023 16:52:08 GMT

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

Re: Subtract total values of 2 fields

vishalduttauk — Thu, 28 Sep 2023 11:53:37 GMT

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?

Re: Subtract total values of 2 fields

ITWhisperer — Thu, 28 Sep 2023 14:03:24 GMT

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

Re: Subtract total values of 2 fields

vishalduttauk — Thu, 28 Sep 2023 15:06:20 GMT

Thank you @ITWhisperer !!

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