Splunk Search

Calculating Median of Count over Time

cxr5971
Path Finder

Hello,

I am trying to find outliers on a graph by using the median absolute deviation on a graph. I know that the machine learning toolkit for splunk can do this but I'm not using that right now. Essentially, we have the count of ip addresses over time because we are tracking how many times this ip address generates an event. So we have this count value plotted over time. I need to calculate the median absolute deviation and for some reason it doesn't seem to be able to get the median of this. It seems to grab one of the bigger count values as the median even if this value only shows once. There are clearly other values which should be selected as the median yet they were not. (The first column in the table is the count values. There is more than shown but it is clear that 1209 is an outlier, and should not be the median)

index=data sourcetype=json seen.indicator=X.X.X.X
| bin _time span=30m
| eventstats count(seen.indicator) as "Count" by _time
| eventstats values(Count) as valu
| eventstats count(valu) as help by _time
| eventstats median(Count) as med
| eval newValue = abs(Count-med)
| eventstats median(newValue) as medianAbsDev by seen.indicator
| eval upper = med+(medianAbsDev*1.2)
| eval lower = med-(medianAbsDev*1.2)
| eval isOutlier=if(Count < lower OR Count > upper, 1, 0)
| timechart span=30m count(seen.indicator) as CountOfIndicator, eval(values(upper)) as upperl, eval(values(lower)) as lowerl, eval(values(isOutlier)) as Outliers by seen.indicator usenull=f useother=f
| filldown

alt text

alt text

0 Karma
1 Solution

cxr5971
Path Finder

Here is my answer that ended up working
index=data sourcetype=json
| bin _time span=$bspan$
| stats count(seen.indicator) as Con by _time seen.indicator
| eventstats median(Con) as "med" by seen.indicator
| eval newV = abs(Con-med)
| eventstats median(newV) as medianAbsDev by seen.indicator
| eval upper = med+(medianAbsDev*$sensitivity$)
| eval lower = med-(medianAbsDev*$sensitivity$)
| eval isOutlier=if(Con < lower OR Con > upper, 1, 0)
| timechart span=$bspan$ values(Con) as CountOfIndicator, eval(values(upper)) as upperl, eval(values(lower)) as lowerl, eval(values(isOutlier)) as Outliers by seen.indicator usenull=f useother=f
| filldown

If anyone can answer, how can I make the outliers more visible and outlined? The line that shows the outliers is pointed to by the black box I drew around it. It is 1 where there is an outlier in the count above. Can I somehow outline or highlight the points above?

alt text

View solution in original post

0 Karma

cxr5971
Path Finder

Here is my answer that ended up working
index=data sourcetype=json
| bin _time span=$bspan$
| stats count(seen.indicator) as Con by _time seen.indicator
| eventstats median(Con) as "med" by seen.indicator
| eval newV = abs(Con-med)
| eventstats median(newV) as medianAbsDev by seen.indicator
| eval upper = med+(medianAbsDev*$sensitivity$)
| eval lower = med-(medianAbsDev*$sensitivity$)
| eval isOutlier=if(Con < lower OR Con > upper, 1, 0)
| timechart span=$bspan$ values(Con) as CountOfIndicator, eval(values(upper)) as upperl, eval(values(lower)) as lowerl, eval(values(isOutlier)) as Outliers by seen.indicator usenull=f useother=f
| filldown

If anyone can answer, how can I make the outliers more visible and outlined? The line that shows the outliers is pointed to by the black box I drew around it. It is 1 where there is an outlier in the count above. Can I somehow outline or highlight the points above?

alt text

0 Karma

niketn
Legend

[UPDATED ANSWER] based on further details provided

@cxr5971 the window argument of streamstats command creates the sliding window for calculating outlier. So if your data is periodic for example each saturday 02:00 to 04:00 am is the time for backup job then data during the same time will be similar week over week and should not be considered outlier. Several similar situations like if weekdays working hours vs off hours will have similar trends then you would want to apply a period of 1 week as window, which can be done via streamstats command.

Since your minimum time span is 30 minute, i.e. 2 time window in 1 hour, I created a window of 2*24*7=336 (corrected below as well as I had added *4 for 4 weeks time which should not be considered as window) for 1 week.

Do also go through the Splunk Blog Cyclical Statistical Forecast and Anomalies Part 1, 2 and 3 to try a sample walk-through.

If you want to represent the outliers better you can try one of my older answers: https://answers.splunk.com/answers/747177/how-to-add-a-reference-line-to-an-outlier-chart-cr.html


@cxr5971 even if you can not deploy MLTK on your production system, you should go ahead and deploy the same on your local because MLTK allows you to see the SPL and implement the same in your own dashboard with your data. Standard Deviation queries are based on Splunk Core implementations hence can be directly adopted.

Following the SPL for Mean Absolute Deviation (window of 2*24*7=336 where 30 min=1/2 hour hence 24*2=48 points in a day, and multiplier of 1.25 as per your query, which you can adjust as per your window and time span)

<yourData>
| timechart span=30m max(seen.indicator) as responsetime 
| streamstats window=336 current=true median("responsetime") as median 
| eval absDev=(abs('responsetime'-median))
| streamstats window=336 current=true median(absDev) as medianAbsDev 
| eval lowerBound=(median-medianAbsDev*exact(1.25)), upperBound=(median+medianAbsDev*exact(1.25))
| eval isOutlier=if('responsetime' < lowerBound OR 'responsetime' > upperBound, 1, 0)
| fields _time, "responsetime", lowerBound, upperBound, isOutlier, *
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

cxr5971
Path Finder

@niketnilay
Thank you for your response. I have solved my issue without streamstats as noted to my own answer below but what is the reasoning for using streamstats? I see this usage in quite a few outlier detection SPL queries but it doesn't provide results that are useful for me.

Could you explain its usage beyond the splunk documentation and how it may or may not be useful here?
Also is there a simple way to use my outlier eval to highlight the outliers like the machine learning visualization does?

0 Karma

niketn
Legend

@cxr5971 I have updated my answer as per your further questions. Do accept/up-vote the answer if it helped.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Sukisen1981
Champion

your query looks correct, why do you say 1209 is the median? Can you check this?

index=data sourcetype=json seen.indicator=X.X.X.X
 | bin _time span=30m
 | eventstats count(seen.indicator) as "Count" by _time
 | eventstats values(Count) as valu
 | eventstats count(valu) as help by _time
 | eventstats median(Count) as med
 | eval newValue = abs(Count-med)
 | eventstats median(newValue) as medianAbsDev by seen.indicator| table Count,med,medianAbsDev,newValue

check med n medianAbsDev...how do they look?

0 Karma

cxr5971
Path Finder

See my contributions below. It seemed as though it wasn't working with the count properly. It seemed as though it was using the count number as the number of times as well. So 1209, was essentially "appearing" 1209 times. Which obviously is not the case, and threw off my median.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...