topic Re: How to edit my eval statement to extract values for the current day to find the percentage difference between dates? in Splunk Search

How to edit my eval statement to extract values for the current day to find the percentage difference between dates?

pmcfadden91 — Wed, 28 Oct 2015 14:25:15 GMT

Hi,

I posted this question before, but was unable to attach the picture later in the thread. I am looking to add a column that gives me the % Difference. The formula is this: ((5_Day_Avg - currentDay) /5_Day_Avg) *100.

My search looks like this:

 index=csl_dblogs   | rename TRUNC_CREATION_TIME as Date2  | rename SOURCE_SYSTEM_NAME as Source_System | Rename COUNT as Count  | eval Date=strftime('Date', "%m-%d-%Y")  | chart  values(COUNT) over Source_System by Date  | addtotals  fieldname=Total  | eval   5_Day_Avg=round(Total/5) |  eval  currentDay=if(Date>now(), 0, Count)

Based on the picture, I would need just 1 column that represents the difference based on the formula provided. However, I am having a problem extracting the values of the current day's field. Right now, it shows 10/27/2015 as the current day, but the DB will index 10/28 files later today (which will be Current Day). I know I need an if statement, but i tried with the one above and it doesn't exactly work. Any help is greatly appreciated.

Re: How to edit my eval statement to extract values for the current day to find the percentage difference between dates?

pmcfadden91 — Thu, 29 Oct 2015 21:27:21 GMT

I reazlized the problems is that after running the Chart command, I couldnt see anymore fields so I worked backwards to extract eachfield I needed. The problem is I can't seem to use or add these fields.

index=csl_dblogs | rename TRUNC_CREATION_TIME as Date | rename SOURCE_SYSTEM_NAME as Source_System | eval Date=strftime('Date', "%m-%d-%Y") | eval t2=relative_time(now(), "-2d@d" ) | eval t2=strftime('t2', "%m-%d-%Y") | eval today_count=if(Date>=t2, COUNT, 0) | eventstats avg(COUNT) as Average sum(COUNT) as Sum by Source_System | eval ten_Day_Avg=round(Sum/10) | eval Diff=((ten_Day_Avg-today_count)/ten_Day_Avg)*100 | chart values(COUNT) as Vals over Source_System by Date

So how can I add additionals Column fields afer using a Chart or Stats command?

Re: How to edit my eval statement to extract values for the current day to find the percentage difference between dates?

ltrand — Tue, 29 Sep 2020 07:46:58 GMT

Adding fields after stats or charting can look like this:

base search | evals for new_field | chart chart values(COUNT) as Vals over Source_System by Date | table Date, Source_System, Values, new_field.

It takes the new field names from chart, and any other still valid field, and lays them out in a table, all pretty and such.

But why not include the field in the original chart step?

Re: How to edit my eval statement to extract values for the current day to find the percentage difference between dates?

lguinn2 — Tue, 29 Sep 2020 07:44:29 GMT

I think that using the chart command has made it difficult to do what you want. If you aren't required to show all the individual days of data, you can try this:

index=csl_dblogs
| rename TRUNC_CREATION_TIME as Date
| rename SOURCE_SYSTEM_NAME as Source_System
| eval Date=strftime(Date, "%m-%d-%Y")
| stats count as Count by Source_System Date
| eval TodayCount=if(Date=strftime(now(), "%m-%d-%Y"),Count,0)
| stats sum(TodayCount) as TodayCount avg(Count) as AvgCount sum(Count) as Total by Source_System
| eval Difference = round(((AvgCount - TodayCount) /AvgCount) *100,2)

Re: How to edit my eval statement to extract values for the current day to find the percentage difference between dates?

pmcfadden91 — Thu, 29 Oct 2015 22:47:22 GMT

I am required to show the days in that format unfortunately.

Re: How to edit my eval statement to extract values for the current day to find the percentage difference between dates?

lguinn2 — Fri, 30 Oct 2015 00:18:20 GMT

If you ARE required to show all the individual days of data, you can try this:

index=csl_dblogs
| rename TRUNC_CREATION_TIME as Date
| rename SOURCE_SYSTEM_NAME as Source_System
| eval Date=strftime(Date, "%m-%d-%Y") 
| stats count as Count by Source_System Date
| eval Today=strftime(now(), "%m-%d-%Y")
| eval TodayCount=if(Date=Today,Count,0)
| eventstats sum(TodayCount) as TodayCount avg(Count) as AvgCount sum(Count) as Total list(Date) as Dates list(Count) as "Daily Count" by Source_System
| eval AvgCount=round(AvgCount,2)
| eval Difference = round(((AvgCount - TodayCount) /AvgCount)*100,2)
| where Date=Today
| table Source_System Dates "Daily Count" Total AvgCount Today TodayCount Difference