topic Re: How do I compare the same search from two separate weeks? in Reporting

How do I compare the same search from two separate weeks?

splunkfuinator — Sat, 05 Dec 2015 16:06:12 GMT

Hello all,

I have a search that I have scheduled to run twice as a report: once for last week (Sunday to Sunday) and once for the week prior (also Sunday to Sunday).

The search looks for Logins, and then lists the number of unique computers by the user who logged into them and the type of authentication that was used.

I need to compare these two reports - preferably with a third one, that way I can schedule them all after hours. When they are compared, I need it to show:
1. usernames that show up on one report but not the other and
2. usernames that logged into a different number of hosts than in the other report -- like if a user logs into 20 hosts one week and 50 the next - I need the difference. It can a percentage or just the number (like 50-20=30).

Here is the search I'm currently running:

sourcetype="Login"
| eventstats dc(host) as distinct_hosts by UserName, Event, Authentication
| where distinct_hosts >= 2
| dedup host, UserName, Authentication
| stats dc(Computer) by UserName, Event, Authentication

It doesn't much matter how it gets done within Spunk, it just needs to be schedule-able. I've looked around a bunch, but haven't found anything that addresses this and this is beyond my Splunk knowledge level.

Thanks in advance for any help!

Re: How do I compare the same search from two separate weeks?

sundareshr — Sat, 05 Dec 2015 20:41:28 GMT

Have you looked at the appendcols command? You could try something like this

sourcetype=* earliest=-1w@w0 latest=@w0 | stats count as week1 by host ... | appencols [search sourcetype=* earliest=earliest=-2w@w latest=-1w@w | stats count as week2 by host ... ] | eval diff=week2-week1

Re: How do I compare the same search from two separate weeks?

splunkfuinator — Wed, 09 Dec 2015 14:22:09 GMT

Thanks for the suggestion. I tried adding this to my search, but had to add a "d" to "appencols". It keeps telling me that whatever follows "appendcols" is an unknown search command ("Unknown search command 'sourcetype'").

Here's the updated search:

sourcetype="Login" earliest=-1w@w0 latest=@w 
| stats count as week1 by host
| eventstats dc(host) as distinct_hosts by UserName, Event, Authentication
| where distinct_hosts >= 15 
| dedup host, TargetUserName, AuthenticationPackageName 
| stats dc(Computer) by UserName, Event, Authentication
| appendcols [sourcetype="Login" earliest=-1w@w0 latest=@w 
| stats count as week2 by host
| eventstats dc(host) as distinct_hosts by UserName, Event, Authentication
| where distinct_hosts >= 15 
| dedup host, UserName, Event, Authentication 
| stats dc(Computer) by UserName, Event, Authentication] 
| eval diff=week2-week1

Re: How do I compare the same search from two separate weeks?

pradeepkumarg — Wed, 09 Dec 2015 14:55:49 GMT

You will have to put a keyword 'search' before the sourcetype inside the subsearch

....| appendcols [search sourcetype="Login" earliest=-1w@w0 latest=@w

Re: How do I compare the same search from two separate weeks?

sundareshr — Wed, 09 Dec 2015 19:00:58 GMT

That's right. Please add searchso your command will look like this

| appendcols [search sourcetype="Login" earliest=-1w@w0 latest=@w

Re: How do I compare the same search from two separate weeks?

splunkfuinator — Wed, 09 Dec 2015 20:11:34 GMT

Got it, thanks! Ok, so the only part I'm fuzzy on it how the time works - I'd like the weeks to line up to the week prior (Sunday thru Sunday) and the week prior to that (also Sun thru Sun). For example, if we were running it today (9 Dec 2015), one week would be 29 Nov - 6 Dec and one week would be 22 Nov - 29 Nov. I've read the Splunk article on time modifiers and a few forum posts, and I just don't get the logic behind it. The earliest=-1w@w0 latest=@w above looks like it searches the same time period (previous week to now) in both the main search and the subsearch - can anyone explain this?
URL for the time modifier page should be http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/SearchTimeModifiers

Re: How do I compare the same search from two separate weeks?

sundareshr — Fri, 11 Dec 2015 18:50:33 GMT

You're right, the sub-search should be earliest=-2w@w latest=-1w@w. This will give you 11/22 to 11/29. The main search should be earliest=-1w@w0 latest=@w0 for 11/29 - 12/6. I've edited the original post