I am searches data that looks like:
violation name email manager
I can do a search like:
"earliest=-7d index=whatHappened | stats count as HowManyTimes by violation | where HowManyTimes>3"
This will tell me how many times there was a violation in a week and give me the results is there were more than 3 violations in a week. Simple.
What I can't figure out it how to give me a table, results... like:
HowManyTimes violation name email manager
Thanks for any help.
You could use the Map command. Here are some examples that play with typical Windows EventCodes:
Use dedup to get the most recent occurance of EventCodes that occured more than 5 times:
index=main EventCode="4*" |stats count by EventCode |where count > 5 |map search="search index=main EventCode=$EventCode$ |dedup EventCode | table _time EventCode Message"
Remove the dedup command to get a list of all occurances of EventCodes that occured more than 5 times.
index=main EventCode="4*" |stats count by EventCode |where count > 5 |map search="search index=main EventCode=$EventCode$ | table _time EventCode Message"
You could use the Map command. Here are some examples that play with typical Windows EventCodes:
Use dedup to get the most recent occurance of EventCodes that occured more than 5 times:
index=main EventCode="4*" |stats count by EventCode |where count > 5 |map search="search index=main EventCode=$EventCode$ |dedup EventCode | table _time EventCode Message"
Remove the dedup command to get a list of all occurances of EventCodes that occured more than 5 times.
index=main EventCode="4*" |stats count by EventCode |where count > 5 |map search="search index=main EventCode=$EventCode$ | table _time EventCode Message"
Cool. I forgot to post the doc link:
http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/map
That worked. Thanks. Never had seen the map command.
If you are looking for no of violations for a violation, name, email and manager combination, simply include them in stats command.
earliest=-7d index=whatHappened | stats count as HowManyTimes by violationname, email, manager | where HowManyTimes>3
The count from your query and this query will match if there is one-to-one relationship between a violation and its corresponding "name, email, manager" combination.
Sample data here could help you get exact answer.
Yes, you can use max or min to get max/min hour and minute value, or your can use first/last to get first/last value in the aggregation. Other options you can try is to list(hour) to get all the values of hour in a multivalued field.
This will work for what I am trying to do. The problem I was having was I had additional fields of which aren't unique (let's assume hour and minute). If I add hour and minute to the stats command then I get a HowManyTimes of 1 for everything. I think I need to join with something like max(hour) and max(minute).
violation name Manager hour minute HowManyTimes
invalidLogin Bob Smith Boss1 20 20 1
invalidLogin Bob Smith Boss1 20 21 1
invalidLogin Bob Smith Boss1 21 45 1
failuretoLogoff John Doe Boss2 19 5 1
failuretoLogoff John Doe Boss2 21 23 1