Reporting

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

splunkfuinator
New Member

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!

0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

sundareshr
Legend

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

splunkfuinator
New Member

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
0 Karma

pradeepkumarg
Influencer

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

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

splunkfuinator
New Member

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

0 Karma

sundareshr
Legend

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

0 Karma

sundareshr
Legend

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

| appendcols [search sourcetype="Login" earliest=-1w@w0 latest=@w 
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...