Why can't I generate data table of statistics?

[POR160893]

Hi,

My overall goal is to create a resulting data table with headings including HourOfDay, BucketMinuteOfHour, DayOfWeek, and source, as well as creating an upperBound and lowerBound.

My current query is as follows:

index="akamai" sourcetype=akamaisiem
| eval time = _time
| eval time=strptime(time, "%Y-%m-%dT%H:%M:%S")
| bin time span=15m
| eval HourOfDay=strftime(time, "%H")
| eval BucketMinuteOfHour=strftime(time, "%M")
| eval DayOfWeek=strftime(time, "%A")
| stats avg(count) as avg stdev(count) as stdev by HourOfDay,BucketMinuteOfHour,DayOfWeek,source
| eval lowerBound=(avg-stdev*exact(2)), upperBound=(avg+stdev*exact(2))
| fields lowerBound,upperBound,HourOfDay,BucketMinuteOfHour,DayOfWeek,source
| outputlookup state.csv

However, it produces zero results. Can you please help?
I am using the following article as a guide as this is for an anomaly detection project: https://www.splunk.com/en_us/blog/platform/cyclical-statistical-forecasts-and-anomalies-part-1.html


I appreciate any help.

tHANKS!

[gcusello]

Hi @POR160893,

review the time definitions:

index="akamai" sourcetype=akamaisiem
| bin _time span=15m
| eval HourOfDay=strftime(_time, "%H")
| eval BucketMinuteOfHour=strftime(_time, "%M")
| eval DayOfWeek=strftime(_time, "%A")
| stats avg(count) as avg stdev(count) as stdev by HourOfDay BucketMinuteOfHour DayOfWeek source
| eval lowerBound=(avg-stdev*exact(2)), upperBound=(avg+stdev*exact(2))
| fields lowerBound,upperBound,HourOfDay,BucketMinuteOfHour,DayOfWeek,source
| outputlookup state.csv

Ciao.

Giuseppe

[gcusello]

Hi @POR160893,

this search works if you have the "count" field, but also in your search you haven't it.

How do you want to calculate avg and std_dev?

Ciao.

Giuseppe

[POR160893]

OK.

So, the count is supposed to be the number of HTTP requests for a given day.

So, I set the count variable with the following then:
| timechart span=1d count(httpMessage.requestId) as count

Thanks!

[POR160893]

Sadly, it did not solve my issue as my current query where count is set outputs no fields.
The query is as such:
index="akamai" sourcetype=akamaisiem
| timechart span=1d count(httpMessage.requestId) as count
| bin _time span=15m
| eval HourOfDay=strftime(_time, "%H")
| eval BucketMinuteOfHour=strftime(_time, "%M")
| eval DayOfWeek=strftime(_time, "%A")
| stats avg(count) as avg stdev(count) as stdev by HourOfDay BucketMinuteOfHour DayOfWeek source
| eval lowerBound=(avg-stdev*exact(2)), upperBound=(avg+stdev*exact(2))
| fields lowerBound,upperBound,HourOfDay,BucketMinuteOfHour,DayOfWeek,source
| table lowerBound,upperBound,HourOfDay,BucketMinuteOfHour,DayOfWeek,source


Let me know what you think or where I went wrong please?

[gcusello]

Hi @POR160893,

if one answer solves your need, please accept one answer for the other people of Community or tell me how I can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[POR160893]

Hey, 

This query helped but I still do not get the upper and lower bounds in the final output:

What do you think?