Getting Data In

Mean for Failed Logons to Windows

Contributor

I'm trying to grab the number value of all failed logons on windows logs (eventually will be failed logons per account aka user).

I'm trying to run the mean on the following query:

index=xyz ("EventCode=4625") OR ("EventCode=529" OR "EventCode=530" OR "EventCode=531" OR "EventCode=532" OR "EventCode=533" OR "EventCode=534" OR "EventCode=535" OR "EventCode=536" OR "EventCode=537" OR "EventCode=539") (Logon_Type=*) | stats mean(user)

This returns no value. If I change it to

stats mean(EventCode)

It returns a number that is incorrect. This number corresponds to the mean of the EventCode numbers. Perhaps I simply can't wrap my head around the situation, but any help would be greatly appreciated!

Tags (2)
0 Karma
1 Solution

Legend

Try this:

index=xyz ("EventCode=4625") OR ("EventCode=529" OR "EventCode=530" OR "EventCode=531" OR "EventCode=532" OR "EventCode=533" OR "EventCode=534" OR "EventCode=535" OR "EventCode=536" OR "EventCode=537" OR "EventCode=539") (Logon_Type=*) 
| stats count by user
| stats mean(count) as MeanCountOfUserEvents

The mean function calculates the average of the field that you name. So mean(EventCode) will return the mean of the numeric event codes - and Splunk can't even calculate mean(user) because none of the values for user are numeric. My example counts the number of events for each user, and then takes the mean of that count.

View solution in original post

Legend

Try this:

index=xyz ("EventCode=4625") OR ("EventCode=529" OR "EventCode=530" OR "EventCode=531" OR "EventCode=532" OR "EventCode=533" OR "EventCode=534" OR "EventCode=535" OR "EventCode=536" OR "EventCode=537" OR "EventCode=539") (Logon_Type=*) 
| stats count by user
| stats mean(count) as MeanCountOfUserEvents

The mean function calculates the average of the field that you name. So mean(EventCode) will return the mean of the numeric event codes - and Splunk can't even calculate mean(user) because none of the values for user are numeric. My example counts the number of events for each user, and then takes the mean of that count.

View solution in original post

Legend

I don't understand how you are using the term "mean" - in English, the statistic called "mean" is also called "average." The mean is calculated for a series of numbers by first summing the numbers and then dividing the total by the count of the numbers.

The mean is not a "standard deviation" - for a standard deviation, use the stdev function instead.

Finally, this will work for your count by user

yoursearchhere
| stats count by user

The mean doesn't make sense here, as you have only one value per user

0 Karma

Contributor

Looks like I figured it out on my own.....

stats mean(count) as StandardDeveationOfSuccessfulLogons by user

0 Karma

Contributor

Thank you! This is what I wanted. Can you add to this though, if I wanted to count the mean of the number of events and show it for each user how would I add that in this query?

0 Karma

Legend

I don't follow completely what you're trying to achieve - grab a count of failed logons? If so, just do stats count at the end of the search, instead of stats mean(...). If I misunderstood your intentions, please describe them in more detail.

0 Karma