Splunk Search

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

pmcfadden91
Path Finder

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:
alt text

 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.

0 Karma

lguinn2
Legend

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)

0 Karma

pmcfadden91
Path Finder

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

0 Karma

lguinn2
Legend

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
0 Karma

pmcfadden91
Path Finder

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?

0 Karma

ltrand
Contributor

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?

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...