I have created a query related to account lockouts, but my criteria is if user is continuously coming over last 3 days and has between 5 to 10 lockouts per day, then only should results come up.
if user A has 6, 7, 8 lockouts and user B has 1, 2, 9 lockouts and user C has 8, 8, 8 lockouts on 1-Feb , 2-Feb , 3-Feb respectively.
Then only user A and C should come up in the result.
Currently when i specify earliest and latest in the search it brings up all the users over last 3 days with 5-10 lockouts even if user has locked out for 1 day in between the range specified.
I am a splunk noob here any help might be appreciated.
Think in terms of stages, and add up the information for each stage independently before you go on.
At its lowest level, you need a search that finds lockouts.
At the next level, you need them added up by day.
At the next level, you need to know how many days passed/failed your criteria..
(your search that gets the individual lockout records for the days ) | rename COMMENT as "bin them by day and count how many for each user for each day" | bin _time span=1d | stats count as lockouts by UserName _time | rename COMMENT as "Get rid of days with less than 5 " | where lockouts >= 5 | rename COMMENT as "Find out how many days have 5 or more" | stats count as daysover5 values(_time) as times max(_time) as _time by UserName | rename COMMENT as "Get rid of users with less than 3 " | where daysover5 > = 3
A run anywhere that I'll explain, then I'll try to show you how to implement one for yourself.
| makeresults | eval nums="a,14 a,19 a,33 a,17 a,58 a,14 a,17 b,12 b,47 b,21 b,69 b,10 b,33" | makemv nums | mvexpand nums | makemv delim="," nums | eval control = mvindex(nums, 0), value = mvindex(nums, 1) | fields - nums | eval IsOverThreshold = if(value>15, 1, 0) | streamstats window=3 sum(IsOverThreshold) AS Violations BY control | search Violations>=3
The first three lines - starting with
makeresults and ending with the first
eval command set up a run-anywhere environment. They're not real important, but if you run just this three lines by themselves, the output is structurally how you'll want to make your data look like. More on that later.
eval line creates a field
IsOverThreshold which is only "1" if the value of what you are measuring is above 15. This is where you adjust your threshold to what you need, perhaps >5.
The 5th line is where the magic happens. streamstats takes a "stream" of data and does things with it, much like stats but it doesn't "break" your events up, just adds aggregations as it reads through events. We use it with a window of 3 so it's looking at the last 3 events for its calculation, and what it does is sum our IsOverThreshold. If in the past 3 events (sorted by control) there were three IsOverThreshold, then it's 3. If there were only 2, it's 2. Simple enough. It calls the answer
The last line searches for where
Violations are more than 3.
You should take the time to use this run anywhere example, adding one line at a time, to understand the example provided. Understanding is important or else this will still be a mysterious thing.
Now, to modify to your own.
MY first three lines set up the run anywhere example. Yours will just need to get your data and split it up how the trailing lines need it.
Since you already have a search, use that. If it returns a list of date, username, lockouts then you'll want to modify the above search to something maybe like ...
...your search here | eval IsOverThreshold = if(lockouts>15, 1, 0) | streamstats window=3 sum(IsOverThreshold) AS Violations BY username | search Violations>=3
Give that a try and see how it goes!
If you have specific difficulties, please provide the search you actually have, and maybe paste in a small number of results for us to see. Be sure to a) use the code (101010) button to paste so the formatting and special characters come through, and b) anonymize any usernames that may seem special.