How to extract events relative to latest time using eval for stats?

[umaryasin]

Hi,

I am new to Splunk and have been working on a dashboard. Most of the knowledge I have picked up via documentations and reading other questions/answers.

We have specific system logs that we will be using in order to make counts and averages in order to forecast future trends. As this is a demo for the client, we have limited test data (complete for Feb 2017) which we have to use, as other data is not enough to get meaningful information from.

Currently I have set up the search as follows:

sourcetype="log_test" earliest=-1mon@mon latest=@mon
| rename requestno AS "Request Number"  
| join "Request Number" [inputlookup Request_Lookup.csv] 
| stats count by "Request Number" "Request Name" 
| eval lastweek=relative_time(latest, "-1w@w1") | eventstats avg(count) AS lastweekcount by "Request Number"

I was not to sure if I can use relative_time() in this context to extract data from the previous full week from Monday (UK), and was wondering if you have any pointers on how this would be possible. I understand there is not enough information but this is all I have at the moment as the future calculations are based on the average on the events from the previous week for the demo. Currently it returns the same values as the stats count above.

I have been looking at examples online and from other questions from other people but could not quite understand the dates that well. If you have any links to documentation I should be looking at, please link them.

If you have any pointers/help, please let me know.

Thanks.

[woodcock]

If you are trying to get week-over-week (or similar) comparisons, you need to investigate the timewrap command:

https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Timewrap

[cmerriman]

if you're trying to get week over week data to compare last week for the time frame you're looking at, try using a streamstats. something along the lines of:

sourcetype="log_test" earliest=-1mon@mon latest=@mon
 | rename requestno AS "Request Number"  
 | join "Request Number" [inputlookup Request_Lookup.csv] 
 | sort 0 "Request Number" "Request Name" + _time
 | eval week=relative_time(_time,"@w1")
 | streamstats window=1 current=f values(week) as previousWeek by "Request Number" "Request Name" 
 | eventstats max(week) as maxWeek
 | stats count values(previousWeek) as previousWeek values(maxWeek) as maxWeek by "Request Number" "Request Name" week
 | eval lastWeek=if(maxWeek-previousWeek=604800,1,0)
 | eventstats avg(count) AS lastweekcount by "Request Number" lastWeek

[rjthibod]

You cannot do what you are attempting to do because the values of earliest and latest do not exist in the search as you are trying to use them.

More importantly to get you to a proper answer, what exactly are you trying to do with the value of lastweek? Are you trying to compare the average of last week to the previous month? If so, there are multiple different ways of doing that, but I would need you to clarify.

[umaryasin]

Thank you for your prompt reply.

So the idea is that I get the average of the various requests from last week and then divide it by the total number of requests in order to create a new column called daily average. As it is just a mockup it isn't entirely realistic but it will be useful. I only put a small subset of the overall search query above. Below is the full query we have.

sourcetype="log_test" earliest=-1mon@mon latest=@mon
| rename requestno AS "Request Number"  
| join "Request Number" [inputlookup Request_Lookup.csv] 
| stats count by "Request Number" "Request Name" 
| eventstats sum(count) as total
| eval percent=round(count/total*100,2)
| eval "Daily Average"=round(count/3)
| join "Request Number" [inputlookup Request_ThresholdLookup.csv] 
| eval "Warning Threshold" = if('Warning Threshold'=0, round('Daily Average' * 1.10), 'Warning Threshold')
| eval "%Warning" = round(count/'Warning Threshold'*100)
| eval "Error Threshold" = round('Daily Average' * 1.5)
| eval "%Error" = round(count/'Error Threshold'*100)
| eval "RAG Status" = case ('%Warning'>=100 AND '%Error'>= 100, "Error", '%Warning'>=100, "Warning", '%Error' >=100, "Error", 1=1, "Normal")
| table  "Request Number" "Request Name", count, percent, "Daily Average", "Warning Threshold", "%Warning", "Error Threshold", "%Error", "RAG Status"
| sort by - count
| rename count AS "Number of Requests"

So as you can see currently Daily Average is count/3. This was just put there in order to ensure I can get something in the fields. I wanted to put the average of previous week into that which is why I had the
eval lastweek=relative_time(latest, "-1w@w1") query

Hopefully this will make more sense now.

Thanks.