Trying to get all fields after a count

[rmorlen]

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.

[lukejadamec · Accepted solution]

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"

[lukejadamec]

Cool. I forgot to post the doc link:
http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/map

[rmorlen]

That worked. Thanks. Never had seen the map command.

[somesoni2]

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.

[somesoni2]

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.

[rmorlen]

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