Splunk Search

Help joining datasources/queries across a field and during a timeframe

kmcconnell
Path Finder

I have a situation where I need to take a queries result (successful logins of users) and then use each of those events to match against another sourcetype/query on the user and within a two second window. Not sure how clear that is. I’ll see if I can add a little more detail. The first query returns successful login attempts for users. This data will have the userID and when they successfully logged in. I need to then match each of those events against some Windows event logs looking for a particular event code (matching up the userID with what is in the Windows event). The Window event always occurs within two seconds of the successfully login. Actually it seems to always happen with one second, but I’m giving it two just in case. If I get much more than a couple second windows it will start pulling in unwanted Windows events. I haven’t done anything like this before and I haven’t had much luck today coming up with a solution. ANY help would be much much appreciated!!

0 Karma
1 Solution

wpreston
Motivator

One way would be to use the map command, just know that it can take a looong time to run depending on how many searches it runs. Do you have an idea of how many of your initial search results would need to be used as the basis of the 2 second window search? It may need to be scheduled so that you don't have to sit and wait for the results. Maybe something like this?

index=login_events status=SUCCESS
| eval start=_time
| eval end=_time+2
| map search="search starttimeu::$start$ endtimeu::$end$ user::$user$ index=windows_events EventCode=552 (host=server1 OR host=server2 OR host=server3 OR host=server4)" maxsearches=100

Adjust the maxsearches parameter to meet your search needs. Again, keep in mind that map searches can take a while to complete since map is running a new search for every single result passed to it from your base search. I'd start small to make sure the search does what you need before running it over a large timeframe.

The docs for map can be found here: http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Map

Another way would be to use transaction. You can set the window on your transaction by using the maxspan or maxpause options, and you can make sure that results don't get truncated due to memory constraints by using the keepevicted option. It would also help to have the startswith and endswith options, if possible, just to speed things up but it's not mandatory. Something like this:

index=login_events status=SUCCESS OR (index=windows_events (host=server1 OR host=server2 OR host=server3 OR host=server4) EventCode=552) 
| transaction user maxpause=2s maxevents=2 keepevicted=t startswith="Your start condition" endwith="Your end condition"
| search eventcount>1

View solution in original post

wpreston
Motivator

One way would be to use the map command, just know that it can take a looong time to run depending on how many searches it runs. Do you have an idea of how many of your initial search results would need to be used as the basis of the 2 second window search? It may need to be scheduled so that you don't have to sit and wait for the results. Maybe something like this?

index=login_events status=SUCCESS
| eval start=_time
| eval end=_time+2
| map search="search starttimeu::$start$ endtimeu::$end$ user::$user$ index=windows_events EventCode=552 (host=server1 OR host=server2 OR host=server3 OR host=server4)" maxsearches=100

Adjust the maxsearches parameter to meet your search needs. Again, keep in mind that map searches can take a while to complete since map is running a new search for every single result passed to it from your base search. I'd start small to make sure the search does what you need before running it over a large timeframe.

The docs for map can be found here: http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Map

Another way would be to use transaction. You can set the window on your transaction by using the maxspan or maxpause options, and you can make sure that results don't get truncated due to memory constraints by using the keepevicted option. It would also help to have the startswith and endswith options, if possible, just to speed things up but it's not mandatory. Something like this:

index=login_events status=SUCCESS OR (index=windows_events (host=server1 OR host=server2 OR host=server3 OR host=server4) EventCode=552) 
| transaction user maxpause=2s maxevents=2 keepevicted=t startswith="Your start condition" endwith="Your end condition"
| search eventcount>1

kmcconnell
Path Finder

Update:
I started playing with the transaction method since the map functionality was so slow. I was able to get a query to work using transaction, but it’s slightly off from the results of the map query. Over a two day time period, I get 17 results with the map query, but only 15 results with the transaction query. I verified the two missing results were valid. I thought it might have something to do with the “startswith” or “endswidth”, but the events seem to be in the correct order (the events look the same as the other events that work correctly). Since I marked the original post as answered, I’m hoping that wpreston (or someone else), might have some ideas on why I’m missing events when using the transaction query. The query is below.

(index=login_events status=SUCCESS) OR (index=windows_events (host=server1 OR host=server2 OR host=server3 OR host=server4) EventCode=552)
| transaction user maxpause=2s maxevents=2 maxspan=5s keepevicted=true startswith="SUCCESS" endswith="EventCode=552"
| search eventcount>1

0 Karma

wpreston
Motivator

Do the events that are not included match the timing requirements of your transaction (maxpause=2s maxspan=5s)? Or if you remove the

... | search eventcount>1 

part of your search, are the events for the two missing transactions included in the results?

0 Karma

kmcconnell
Path Finder

The map method worked, but it was very slow. I had around 400 events to run the map against and it did take a while to run. I may have to revisit the transaction method if the initial dataset grows any. Thanks

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this

First, combine both searches into one using any one approach
1. your first search (successfull login) OR your second search (windows events)
e.g.

(index=audit "Successfully logged in") OR (sourcetype="Windows:Application")
  1. your first search (successfull login) | append [search your second search (windows events)]
    e.g.

    index=audit "Successfully logged in" | append [search sourcetype="Windows:Application"]

Then, use sort by User and _time

your combine search | sort 0 User, _time | streamstats current=f window=1 last(_time) as prevTime by User | eval diff=_time-prevTime | where diff<120
0 Karma

kmcconnell
Path Finder

It didn't work, at least not the way I needed it to. I'm needing to some how join on the user ID AND then only pull in the Windows events for that user during the two second time frame. The query below is pulling in all the "552" Windows events, which is a lot more than I'm wanting.

(index=login_events status=SUCCESS) OR (index=windows_events (host=server1 OR host=server2 OR host=server3 OR host=server4) EventCode=552)
| sort 0 user, _time
| streamstats current=f window=1 last(_time) as prevTime by user
| eval diff=_time-prevTime
| where diff<120

Is there a way to tweak the query or do I need to try a different approach?

0 Karma

kmcconnell
Path Finder

Thanks, I'll give it try.

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 ...