Archive

How to subtract from today and yesterdays date column which are dynamically generated?

I need to take the difference in results from today and yesterdays results. but no result is showing up, I tried converting it into an int, but that didn't help. I have added the query below.

| eval period=if(strftime(_time,"%d")==strftime(now(),"%d"),"today",strftime(_time,"%m-%d")) 
| chart count by EXCEPTION_TYPE,period 
| sort -today 
| eval yesterday = strftime(relative_time(now(), "-1d@d"),"%m-%d") 
| eval Total = today - 'yesterday'
0 Karma

Champion

Try this - <your query>|addtotals| eval x=Total-today| eval Total=today-x| fields - x

You can remove your last eval and also try adding from | addtotals onwards

0 Karma

Actually there are 7 date columns generated, but I need the difference of only today and yesterdays results, else if there were only 2 columns it would have worked
I need to calculate the exception % between today and yesterdays result and display it in a new column
if I manually enter the date values it is working('07-10') but not when I pass the value through the strftime function.

0 Karma

Champion

Hi,
Ok I did not realize you had other fields in addition to 07-10 as well.
Now, try this query as it is, since audit index ships by default it should work for you, choose last 7 days as time range to see how this query behaves with last 1 week data. If thus query works all you need to dois replace 'action' by your custom field 'EXCEPTION_TYPE'

index="_audit"  | eval period=if(strftime(_time,"%d")==strftime(now(),"%d"),"today",strftime(_time,"%m-%d")) | eval period1=case(period !="today",period)|  chart count by action,period 
| eval yesterday = strftime(relative_time(now(), "-1d@d"),"%m-%d")| appendcols [search index="_audit"  | eval period=if(strftime(_time,"%d")==strftime(now(),"%d"),"today",strftime(_time,"%m-%d")) |eval yesterday = strftime(relative_time(now(), "-1d@d"),"%m-%d")| eval period1=case(period !="today",period)|eventstats max(period1) as maxp by action |where maxp=period|chart count(action) as yest_count,values(maxp) as maxp by action]|eval yest_count=if(maxp=yesterday,yest_count,0)| eval total=today-yest_count | fields - maxp, - yest_count
0 Karma