Finding the difference between to separate fields?

[dreschke]

Good morning everyone,

I am trying to find the difference between to fields. I have tried the eval command to subtract between the 2 fields and I have also tried to use the delta command to find the difference. Is there something that i am missing to accomplish this?

Here is the search that i am using below:

index=abc sourcetype=xxx field=xyz
|stats count by Code | stats sum(count) as total2
| append [search index=abc sourcetype=xxx field=xyz
| stats count as total ] | eval total3=(total - total2) | table total, total2, total3

Thank you all for your help with. It seems so simple to me but i just can't get it to work or I am missing something. Thanks again everyone.

[somesoni2]

It seems like you're getting a count of events where you've a matching LoginID from USER.csv, then getting total events, regardless whether they are in lookup or not, and then finding difference. Assuming base search for both is same, try somethign liek this

index=test sourcetype=xxx url="www.test123.com/login"
|lookup USER.csv Username AS Username, OUTPUTNEW LoginID as LoginID, Code AS Code
| stats count(LoginID) as total2 count as total
| eval abc=total-total2 | table total total2 abc

[dreschke]

Hi somesoni2,

This is still giving a result of 0. I am still not seeing the results from my second search:
index=test sourcetype=xxx url="www.test123.com/login"
| stats count as total

The first search is what is narrowing down the results and giving me the over all total. Thanks you for help though you search worked just the results are coming out as zero.

[somesoni2]

Is there any difference is the both searches (apart from stats calculation)? The query I wrote above will give difference of events between total events and events where LoginID is available (base search is same). Try this little inefficient version as well

index=abc sourcetype=xxx field=xyz
|stats count by Code | stats sum(count) as total2 
| appendcols [search index=abc sourcetype=xxx field=xyz
| stats count as total ] | eval total3=(total - total2) | table total, total2, total3

[renjith_nair]

Does this work for you ?

index=abc sourcetype=xxx field=xyz|eventstats count as total|stats count as code_count,first(total) as total by code|stats sum(code_count) as total2,first(total) as total|eval total3=total-total2

[dreschke]

Hello, renjith.nair thanks for your help, your search help me get a step closer to what i am looking for. The over all goal is to find the difference between an overall total and a specific total in order to produce a N/A field.

I tired to expand on what you gave me but I am not able to get the overall total. that what the appended search in my original search was trying to bring in. I removed the field=xyz from the append search since that might be confusing some people. Thanks for your help.

index=abc sourcetype=xxx field=xyz
|stats count by Code | stats sum(count) as total2
| append [search index=abc sourcetype=xxx
| stats count as total ] | eval total3=(total - total2) | table total, total2, total3

[renjith_nair]

The overall count of events could be brought in by using eventstats.
Are you getting the total event after the searchindex=abc sourcetype=xxx field=xyz|eventstats count as total|table total ?

[dreschke]

For my search that I am using the eventstats and the stats count by code are bringing the same name number, so the eval will always result in a 0. One search be bringing in an overall total amount and the other search should bringing in a number that is always less the over all total. I updated the search I originally provided due to so new items that will hopefully show what i am trying to achieve.

Search 1: Gives a count by Code and then an overall total (adding all the Code totals together)
index=test sourcetype=xxx url="www.test123.com/login"
|lookup USER.csv Username AS Username, OUTPUTNEW LoginID as LoginID, Code AS Code
| search LoginID=*
| table Username LoginID Code
|stats count by Code | stats sum(count) as total2

Search2: Gives over all total
| append [search index=test sourcetype=xxx url="www.test123.com/login"
| stats count as total | eval abc=(total-total2) | table total total2 abc ]

Hopefully this help. The logic behind your search works i just to figure how to get the overall total and the Code total together to find the difference.