Graph out an fields daily activity in relation to average usage and stdev

[rwiltzius]

I've been stuck on this for quite some time and I'm hoping someone here can help me. I'm re-purposing a stdev query from one of Splunk's Security Essentials use cases. The purpose of this query is to find accounts / outliers who fall outside what was determined to be their standard deviation. The query works good when outputting the results in a table format. I'll include below the syntax that i'm using.

I would now like to alter this query in a way that will allow me to graph the account in question on a line chart with the their calculated average and also their calculated standard deviation. The graph would stretch for 30 days to see their overall daily pattern. I've attempted to use append, appendcol, and join without much success.

Does anyone have a way to use the synatx below and visualize the data into a line-chart? Please let me know if you have any questions.

index=wineventlog EventCode=5140
| convert mktime(_time) timeformat="%Y-%m-%dT%H:%M:%S.%3Q%z"
| bucket _time span=1d
| stats count by _time Security_ID
| eventstats max(_time) as maxtime
| stats count as num_data_samples max(eval(if(_time >= relative_time(maxtime, "-1d@d"), 'count',null))) as "count" avg(eval(if(_time upperBound) AND num_data_samples >=7, 1, 0)
| table Security_ID, num_data_samples, "count", avg, lowerBound, upperBound, isOutlier
| where isOutlier=1

[DalJeanis · Accepted solution]

UPDATED -

Here's what we're assuming the intent was of the code that got stripped...

index=wineventlog EventCode=5140
| bucket _time span=1d
| stats count as CountForDay by _time Security_ID 
| eventstats max(_time) as maxtime 
| eval CountForToday=if(_time=maxtime,CountForDay,null())
| stats count as NumDays, 
    max(CountForToday) as CountForToday, 
    avg(CountForDay) as AvgForDay, 
    stdev(CountForDay) as StdevForDay 
    by Security_ID 
| eval upperBound=AvgForDay+2*StdevForDay
| eval lowerBound=AvgForDay+2*StdevForDay
| eval isOutlier = case(NumDays<7,0, CountForToday<=lowerBound,1, CountForToday>=upperBound,1, true(),0) 
| table Security_ID, NumDays, CountForToday, AvgForDay, lowerBound, upperBound, isOutlier

---

If you want to see upper and lower bounds over time for one Security_ID, then you could do something like this...

index=wineventlog EventCode=5140 Security_ID="someID"
| bucket _time span=1d
| stats count as CountForDay by _time Security_ID 
| streamstats count as NumSamples, 
    latest(CountForDay) as CountForToday, 
    avg(CountForDay) as AvgForDay, 
    stdev(CountForDay) as StdevForDay 
    by Security_ID 
| eval upperBound=round(AvgForDay+2*StdevForDay,0)
| eval lowerBound=round(AvgForDay-2*StdevForDay,0)
| eval isOutlier = case(NumDays<7,0, CountForToday<=lowerBound,1, CountForToday>=upperBound,1, true(),0) 
| table _time CountForToday lowerBound upperBound
| untable _time series count
| timechart sum(count) as count by series

---

Please use the code button to mark your code when you post it, to avoid having the system delete the tags.

There's also something else missing in the code that was posted. I don't see any command that would create fields upperBound, lowerBound, or isOutlier.

The code looks like it is using whichever is higher, the count for today or the count for yesterday, but then there is some code missing ( stdev( 'count' ) ... by Security_ID should be there, then whatever calculates your bounds and outliers ). I'm fairly sure the cutpoint was between _time and upperBound.

[rwiltzius]

For whatever reason, Splunk is stripping out some of my syntax when I am posting it. I took a screenshot of it, which can be viewed using the link below. Please let me know if you have any questions.

https://drive.google.com/open?id=0B-yUJBdEZ9l8U2FSRGFVRWYxOWc

Thank you,

Robert

[DalJeanis]

There are several methods of telling the interface that code is code.

One is to highlight the code and use the code button (101 010). This works on Chrome, but is persnickety on Internet Explorer.

Another is to indent each line of code at least four spaces. This seems to work on all browsers.

Another, suitable for short snippets, is to use the accent grave () before and after the code. The accent grave is found above the tilde~to the left of the1` on an American keyboard.