topic Re: How to count number of events and length along with the median value ? in Splunk Search

How to count number of events and length along with the median value ?

zacksoft_wf — Wed, 26 Oct 2022 08:14:25 GMT

I want to be able to able to count the number of events and the median length of events per sourcetype in Splunk ?

I'm trying to figure out the average/median size of evets for each sourcetype.
By size, I mean the charachter length of the raw events. and then multiply the count of events with the median size to get an idea of what sourcetypes contain big events , so that I can use the data for event size reduction if that is possible.

Re: How to count number of events and length along with the median value ?

yuanliu — Wed, 26 Oct 2022 08:13:11 GMT

On the surface, this is all it takes

| stats count median(length) by sourcetype

provided the field length contains the length of each event. But I'm nearly certain that some information is missing.

Re: How to count number of events and length along with the median value ?

zacksoft_wf — Wed, 26 Oct 2022 08:39:48 GMT

index=* sourcetype=*
| eval length = len(_raw)
| stats count median(length) as median_length by index, sourcetype
| sort median_length

I tried something like this.
Can I count the number of events per sourcetype ? the "| stats count" doesn't give me the right result.

Re: How to count number of events and length along with the median value ?

yuanliu — Wed, 26 Oct 2022 08:44:36 GMT

| stats count by sourcetype gives you number of events by sourcetype. If you ask for | stats count by index, sourcetype as your illustrated code shows, it will give you number of events by index by sourcetype. Which one do you want?

Re: How to count number of events and length along with the median value ?

ITWhisperer — Wed, 26 Oct 2022 08:57:35 GMT

| eval length=length(_raw) | stats count median(length) as median by sourcetype

Having said that, why not sum the lengths

| eval length=length(_raw) | stats count sum(length) as total by sourcetype

Re: How to count number of events and length along with the median value ?

zacksoft_wf — Wed, 26 Oct 2022 09:05:54 GMT

Thanks @ITWhisperer for the suggestion.
What I am trying to figure out is, the median length of events for a sourcetype , to see which sourcetypes have abnormally long events, and if they unnecessary can we trim it.
Hence I wanted a SPL that would give me the median lenght of events and count of events per sourcetype and possibly another field that will multiply median * event_count to tell me which sourectyped I need to look at for log volume/event size reduction analysis.

Re: How to count number of events and length along with the median value ?

ITWhisperer — Wed, 26 Oct 2022 09:14:46 GMT

The median may not necessarily be useful.

For example, if you had ten events with lengths of 5, 10, 10, 10, 10, 10, 10, 10, 25, 100, your median would be 10, so median * count =100, but if you add them up you get 200. The mean however is 20, so mean * count = 200.

However, if you were interested in deviations from the "norm" to find which source types have outliers, median would be better as 25 is 2.5 times the median and 100 is 10 times the median, and only 1.25 and 5 times the mean.

Re: How to count number of events and length along with the median value ?

zacksoft_wf — Wed, 26 Oct 2022 09:28:42 GMT

In that case, it seems finding the median and the events that are far away from the median could be a good way to identify which sourcetypes need attention to look at for 'log volume reduction analysis'.

This is the SPL, I am using,
any suggestion to improve it to look at the culprit sourcetypes?

index=* sourcetype=*
| eval eventSize=len(_raw)
| table eventSize sourcetype index _raw
| sort - eventSize
| stats median(eventSize) as median_eventSize, avg(eventSize) as avg_eventSize , count(_raw) as numberOfEvents by index, sourcetype
| sort median_eventSize | head 10

Re: How to count number of events and length along with the median value ?

ITWhisperer — Wed, 26 Oct 2022 10:25:45 GMT

I might approach it like this:

Find the median size by index and source type

Determine deviation of each event as a multiple of the median

Count the number of events and the number of events where the deviation is twice the median (you could use 3 times or something else depending on how diverse your normal data is)

Determine the percentage of outlier events your source type have.

index=* sourcetype=* | eval eventSize=len(_raw) | eventstats median(eventSize) as median_eventSize by index, sourcetype | eval deviation = eventSize / median_eventSize | stats count count(eval(deviation>2)) as count_doubleMedian by index, sourcetype | eval percentage_outliers = 100*count_doubleMedian/count | sort 0 -percentage_outliers

Re: How to count number of events and length along with the median value ?

zacksoft_wf — Wed, 26 Oct 2022 12:34:07 GMT

Thank you so much @ITWhisperer