Getting Data In

Mean for Failed Logons to Windows

hagjos43
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

lguinn2
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

lguinn2
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.

lguinn2
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

hagjos43
Contributor

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

stats mean(count) as Standard_Deveation_Of_Successful_Logons by user

0 Karma

hagjos43
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

Ayn
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...