topic Re: How do I edit my search to find the difference between two fields? in Splunk Search

How do I edit my search to find the difference between two fields?

sunnyparmar — Tue, 02 Feb 2016 12:54:24 GMT

Hi,

I have a search given below. All is working fine, but in last I want to sort out difference between total-acknowledged which I am not getting, so please suggest here.

(host="e2e-onp-front" response="Message acknowledged successfully*")    OR      (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=*|rex "Successfully (?<response>created) notification"|eval notifications=if(match(response,"created"),"      Total", "Acknowledged")| stats  count by notifications | eval Diff = Total - Acknowledged

Thanks in advance

Re: How do I edit my search to find the difference between two fields?

richgalloway — Tue, 02 Feb 2016 13:03:40 GMT

Are you sure your base query is producing events with Total and Acknowledged fields? Without the fields, a difference cannot be computed.

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Tue, 02 Feb 2016 13:05:42 GMT

thanks for answering.. yes my base query is production events with total=38 and acknowledged=2 so i want the rest of the difference 36 to be shown in dashboard with these two values.

Re: How do I edit my search to find the difference between two fields?

richgalloway — Tue, 02 Feb 2016 13:12:07 GMT

Case is significant with field names. If the names are "total" and "acknowledged" then your query must be ... | eval Diff = total - acknowledged.

Re: How do I edit my search to find the difference between two fields?

renjith_nair — Tue, 02 Feb 2016 13:14:36 GMT

Try a bit different approach. Join the eval in the stats, something like below. You might need to adjust for your final requirements

 (host="e2e-onp-front" response="Message acknowledged successfully*")      OR        (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=*|rex "Successfully (?<response>created) notification"|stats count(eval(like(response,"%created%"))) AS Total, count(eval(NOT like(response,"%created%"))) as Acknowledge| eval Diff = Total - Acknowledged

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Tue, 02 Feb 2016 13:15:53 GMT

in both words first alphabet are capital like i have pasted in my own query.

| eval Diff = Total - Acknowledged

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Tue, 02 Feb 2016 13:22:59 GMT

thanks for replying but still getting no difference with the query. Even by executing your query i have lost my notification column under which "Total" and "Acknowledged" parameters showing previously. Now it is showing only Total=38 and Acknowledged=2 columns. Notification column is vanished.

Re: How do I edit my search to find the difference between two fields?

renjith_nair — Tue, 02 Feb 2016 13:46:21 GMT

Diff is not showing since there is a typo ie : | eval Diff = Total - Acknowledge

In your first search , the Total and Acknowledged are on diff rows (ie: stats count by notification). So where do you want to dislpay the difference?

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Tue, 02 Feb 2016 13:57:36 GMT

i want to display it in the dashboard result.. Currently i am getting two columns. first is notifications under which i am getting "Total" and "Acknowledged" values and other one is count column under which i am getting "38" and "2" values respectively so now i want third column in which it will show the difference of Total-Acknowledged.

Thanks

Re: How do I edit my search to find the difference between two fields?

renjith_nair — Tue, 02 Feb 2016 14:10:41 GMT

Yes, got it, Your result is

notification                  count
Acknowledged            38
Total                             2

So in third column, which row you want to display the diff ?

Easiest method is using delta ie : | stats count by notifications | delta count as diff p=1
If you want to transpose the complete table, then use

your search| stats count by notification|transpose 2|rename "row 1" as Acknowledged,"row 2" as Total|eval Diff=Total-Acknowledged|search column=count
|fields - column
If you want to print diff in all columns

your search |stats count by notification|streamstats last(count) as newcount current=f|eval Diff=newcount-count|eventstats last(Diff) as Diff|fields - newcount

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Tue, 02 Feb 2016 14:29:24 GMT

Thanks.. You are close enough but displaying result is not exact.. Could you please rearrange these?

| stats count by notifications | delta count as diff p=1

notification count diff
Acknowledged 38
Total 2 36

your search| stats count by notification|transpose 2|rename "row 1" as Acknowledged,"row 2" as Total|eval Diff=Total-Acknowledged|search column=count|fields - column

Acknowledged Total Diff
2 38 36

your search |stats count by notification|streamstats last(count) as newcount current=f|eval Diff=newcount-count|eventstats last(Diff) as Diff|fields - newcount

notifications count Diff
Acknowledged 2 -36
Total 38 -36

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Tue, 02 Feb 2016 14:30:45 GMT

second one is more appropriate but the issue is notification column is vanished from it.

Re: How do I edit my search to find the difference between two fields?

renjith_nair — Tue, 02 Feb 2016 15:01:56 GMT

In the second one, the notification is transposed as Acknowledged and Total and the correspoding counts are shown under each column. Where do you wantto display notification now? How should be your final result looks like?

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Wed, 03 Feb 2016 05:34:40 GMT

final result will be look like -

notification Counts
Total 38
Acknowledged 2
Difference 36

Thanks

Re: How do I edit my search to find the difference between two fields?

renjith_nair — Wed, 03 Feb 2016 06:07:46 GMT

Alright. Try this

     (host="e2e-onp-front" response="Message acknowledged successfully*") OR (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=*
     |rex "Successfully (?<response>created) notification"|eval notifications=if(match(response,"created"),"Total", "Acknowledged")
     |stats  count by notifications
     |delta count as Difference p=1| appendpipe [|stats values(Difference) as Difference|eval notifications="Difference"|eval count=Difference]|fields - Difference

Re: How do I edit my search to find the difference between two fields?

sunnyparmar — Wed, 03 Feb 2016 06:12:53 GMT

Great buddy.. thanks a ton...

Re: How do I edit my search to find the difference between two fields?

rakeshh123 — Tue, 29 Sep 2020 08:39:16 GMT

Hello sunnyparmer,
i just changed order of the queries and changed stats to eventstats in the query ........I actually worked on my data with a similar query it is working fine..this is just due to the fact stats cannot pass data to another stats command in chain you have to use eventstats for that....i am sending screenshot of my query and data....

(host="e2e-onp-front" response="Message acknowledged successfully*") OR (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=|rex "Successfully (?created) notification"|eventstats count by notifications*|stats count(eval(like(response,"%created%"))) AS Total, count(eval(NOT like(response,"%created%"))) as Acknowledge| eval Diff = Total - Acknowledged...
Let me know if it works