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

[sunnyparmar]

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

[rakeshh123]

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

[renjith_nair · Accepted solution]

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

[sunnyparmar]

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

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

notification count diff
Acknowledged 38
Total 2 36

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

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

[sunnyparmar]

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

[renjith_nair]

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?

[sunnyparmar]

final result will be look like -

notification Counts
Total 38
Acknowledged 2
Difference 36

Thanks

[renjith_nair]

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

[sunnyparmar]

Great buddy.. thanks a ton...

[sunnyparmar]

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.

[renjith_nair]

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?

[sunnyparmar]

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

[renjith_nair]

Yes, got it, Your result is

notification                  count
Acknowledged            38
Total                             2

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

1. Easiest method is using delta ie : | stats count by notifications | delta count as diff p=1

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

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

[richgalloway]

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

[sunnyparmar]

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.

[richgalloway]

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

[sunnyparmar]

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

| eval Diff = Total - Acknowledged