topic Re: Dedup using time range in Splunk Search

Dedup using time range

jhillenburg — Tue, 20 Jan 2015 20:28:25 GMT

Hi. I am creating a search and dashboard to display our last ten locked account events. This seems to work well as I have it configured. One of the things I am doing is using the dedup command to remove extra occurrences of an event, given that the lockout events often show up on multiple Active Directory domain controllers (outlined in green below). I am using the "Account_Name" and _time values for this purpose. This works well except where the events are on different domain controllers at different times. In this case, I would prefer to dedup using a window of time (say 5 seconds), but I cannot find how to do this. Shown in the example below are some entries outlined in red, where they are the same user but at different times, and I would want to be careful to not exclude those events, so a straight dedup does not help.

Code:

EventCodeDescription="A user account was locked out" Account_Name=* NOT "Guest" Account_Domain=* Caller_Computer_Name=* dvc=* source="WinEventLog:Security" _time=* | eval Account_Name=mvindex(Account_Name,1)  | dedup Account_Name _time  | rename dvc AS "Domain Controller" | rename Account_Domain AS "Domain Name" | rename Caller_Computer_Name AS "Client Host" | rename Account_Name AS "Account Name" | table _time "Account Name" "Client Host" "Domain Controller" "Domain Name" | sort -_time

Thanks.

EDIT: Apparently, I am not allowed to attach images. Please see the Evernote link below:

https://www.evernote.com/shard/s26/sh/9082054d-788a-491f-92c2-66718d443740/cc2de893310866299d291983497de0dc/deep/0/Locked-Accounts---Last-10.png

Re: Dedup using time range

trsavela — Tue, 20 Jan 2015 20:33:39 GMT

Take a look at the bucket command:

http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/bucket

Re: Dedup using time range

jhillenburg — Tue, 20 Jan 2015 20:41:34 GMT

That certainly groups them together, though if an administrator were trying to search for the events in the logs, they would not find a precise match. Having a dedup threshold would achieve the most desirable behavior.

Re: Dedup using time range

sideview — Tue, 20 Jan 2015 22:17:03 GMT

using the bin command (aka bucket), and then doing dedup _time "Domain Controller" is a good solution.

One problem though with using bin here though is that you're going to have a certain amount of cases where even though the duplicate events are only 5 seconds away, they happen to cross one of the arbitrary bucketing boundaries. To take a worst case scenario if the user gets locked out at 11:59:57 PM on one DC and 12:00:02 AM on another dc.

| bin _time span=5sec | dedup _time "Domain Controller"

wont dedup them. Nor will any other reasonable arguments to bin.

An odd solution that seems to avoid the problematic cases that I can think of, would be

| bin _time span=5sec | eval _time=_time+10 | bin _time span=10sec | dedup _time "Domain Controller"

Re: Dedup using time range

jhillenburg — Wed, 28 Jan 2015 21:18:26 GMT

What about a way, for the purposes of deduplication (though not display), to round to the nearest one minute? I'm really talking about rounding, not bucketing, which are different things.

Re: Dedup using time range

sideview — Thu, 29 Jan 2015 20:49:53 GMT

This is exactly what the bin command does. bin _time span=1m will round down to the nearest minute. I prefer to use it as "bin" rather than the command alias "bucket", partly for this reason. The two functions are often conflated, because you often see the bin command used with stats such as " | bin _time span=1m | stats count by _time"