topic Re: Help with writing a join command that joins a security breach to the previous login in Splunk Search

Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 20 Sep 2017 16:39:07 GMT

This is the requirement. I need to join two events based on a common field “User”. The Event with EventType “Security Breach” should be joined with Eventtype “Login”. The condition is User1 who have a “Security Breach” at 10:55 AM should be joined to the login at 10:54 AM, not with the login at 10:57 AM and login at 10:49 AM. Similarly the User1 who have a “Security Breach” Event at 10:50 AM should be joined to Login event at 10:49AM, not with 10:54 AM. Hope this clarifies.

_time User EventType
10:55 AM User1 Security Breach
10:53 AM User2 Security Breach
10:50 AM User1 Security Breach
10:48 AM User1 Security Breach

_time User EventType
10:57 AM User1 Login
10:55 AM User2 Login
10:54 AM User1 Login
10:53 AM User2 Login
10:49 AM User1 Login

Any one can help me in Writing query for this. I tried using join with earlier=true option. But that doesnt give me the right result.

Re: Help with writing a join command that joins a security breach to the previous login

somesoni2 — Wed, 20 Sep 2017 17:23:05 GMT

What's the final expected output (based on your sample events here)?

Re: Help with writing a join command that joins a security breach to the previous login

Sukisen1981 — Wed, 20 Sep 2017 17:42:27 GMT

I am guessing you want to find the time difference between login and security breach.
Do you really need a join for this?
What if I do this instead?
| sort - User, - _time , - EventType
that should give me for User1
_time User EventType
10:48 AM User1 SecBrch
10:49 AM User 1 Login
10:50 AM User1 SecBrch
10:54 AM

Re: Help with writing a join command that joins a security breach to the previous login

Sukisen1981 — Wed, 20 Sep 2017 17:45:59 GMT

10:57 AM User1 Login

Now use streamstats to get the previous _time value , eval to check if current eventtype is secbrch if yes then subtract current _time-prev _time..This is better than using a join if that is what you want...

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 20 Sep 2017 18:01:29 GMT

Ok.. Let me explain it much better. Some of the fields are missing in Security Breach event which is available in the Login event. I need to join those fields from login event to SecBreach event. The fields are Organization Orgcode. Organization remains unique for an user. But OrgCode will may not be the same.

Here is my login event

_time User EventType Organization OrgCode
10:57 AM User1 Login CIA 12345
10:55 AM User2 Login HIS 765555
10:54 AM User1 Login CIA 53211
10:52 AM User2 Login HIS 12233
10:49 AM User1 Login CIA 765555
10:47 AM User1 Login CIA 234555

Here is my Security breach event.

_time User EventType
10:55 AM User1 Security Breach
10:53 AM User2 Security Breach
10:50 AM User1 Security Breach
10:48 AM User1 Security Breach

After Joining I should get

_time User EventType Organization OrgCode
10:55 AM User1 Security Breach CIA 53211
10:53 AM User2 Security Breach HIS 12233
10:50 AM User1 Security Breach CIA 765555
10:48 AM User1 Security Breach CIA 234555

Hope this clarifies

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 20 Sep 2017 18:06:34 GMT

Ok.. let me explain it much better way. I have few fields missing in Security Breach Event which is available in the login event. I need to join those fields to the Security event. Organization and OrgCode are the fields. Organization will be unique for an user but OrgCode may change .

Here is my Security Breach Event

_time User EventType
10:55 AM User1 Security Breach
10:53 AM User2 Security Breach
10:50 AM User1 Security Breach
10:48 AM User1 Security Breach

Here is my Login Event
_time User EventType Organization OrgCode
10:57 AM User1 Login CIA 12345
10:55 AM User2 Login HIS 765555
10:54 AM User1 Login CIA 53211
10:52 AM User2 Login HIS 12233
10:49 AM User1 Login CIA 765555
10:47 AM User1 Login CIA 234555

My output after joining should be
_time User EventType Organization OrgCode
10:55 AM User1 Security Breach CIA 53211
10:53 AM User2 Security Breach HIS 12233
10:50 AM User1 Security Breach CIA 765555
10:48 AM User1 Security Breach CIA 234555

Hope this clarifies.

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 20 Sep 2017 18:07:16 GMT

Here is my Security Breach Event

_time User EventType
10:55 AM User1 Security Breach
10:53 AM User2 Security Breach
10:50 AM User1 Security Breach
10:48 AM User1 Security Breach

Hope this clarifies.

Re: Help with writing a join command that joins a security breach to the previous login

Sukisen1981 — Wed, 20 Sep 2017 18:16:52 GMT

again do you really need the join command for this? you can still get this by sort, streamstats and eval functions to check the previous values....do not use a join unless absolutely necessary... @somesoni2 will agree. Now, I have not really tried this but if you don't get a solution I will try this out without joins, looks very much possible

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 20 Sep 2017 18:29:56 GMT

Here the problem is I have the filter criteria as Organization OrgCode. If I give an Organization/OrgCode as input I should get the number of Security Breach events for the Organization/OrgCode. In that case will your method works? Please suggest

Re: Help with writing a join command that joins a security breach to the previous login

somesoni2 — Wed, 20 Sep 2017 18:47:09 GMT

I believe you can achieve this without join. Extending solution from @Sukisen1981, you could do like this

your base search which includes events from both EventTypes
| sort 0 -User, _time
| streamstats current=f window=1 values(EventType) as prevEventType values(Organization) as prevOrg values(OrgCode) as prevOrgCode by User
| where EventType="Security Breach"
| rename prevOrg as Organization prevOrgCode as OrgCode
| table _time User EventType Organization OrgCode

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 20 Sep 2017 19:43:06 GMT

Will this give accurate value. Because if the user is having more than one Security Breach Events after a login, keeping window=1 will not work. If I am not keeping window=1 argument, I am getting Organization and OrgCode mapped to all event. But I want to make sure it is mapping to the right events.

Re: Help with writing a join command that joins a security breach to the previous login

somesoni2 — Wed, 20 Sep 2017 19:45:06 GMT

In case there are multiple security breach events, you want to list them all? OR just keep earliest/latest?

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 20 Sep 2017 19:50:33 GMT

I need to list them all and map the Organization and OrgCode to it from the corresponding Login Event

Re: Help with writing a join command that joins a security breach to the previous login

somesoni2 — Wed, 20 Sep 2017 19:57:42 GMT

Lets try this alternative

 your base search which includes events from both EventTypes
 | table _time User EventType Organization OrgCode
 | sort 0 -User, _time
 | eval prevUser=if(EventType="Login",User,null()
| filldown prevUser Organization OrgCode
 | where EventType="Security Breach" AND User=prevUser

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Thu, 21 Sep 2017 14:50:16 GMT

Thank You. Very much appreciated. You made my work much easier. I have been testing it from yesterday. I have a small problem now. In the above scenario, some of my login events does not have Organization and OrgCode in it. I need to fill those fields with some static value and then map it to the SecBreach event. Can you help me?

Re: Help with writing a join command that joins a security breach to the previous login

somesoni2 — Thu, 21 Sep 2017 14:56:51 GMT

Try this

your base search which includes events from both EventTypes
  | table _time User EventType Organization OrgCode
  | eval Organization=coalesce(Organization,"YourStaticValue")
  | eval OrgCode=coalesce(OrgCode,"YourStaticValue")
  | sort 0 -User, _time
  | eval prevUser=if(EventType="Login",User,null()
 | filldown prevUser Organization OrgCode
  | where EventType="Security Breach" AND User=prevUser

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Thu, 21 Sep 2017 15:15:34 GMT

The coalesce is filling out my SecurityBreach events also with the Static Value.

Re: Help with writing a join command that joins a security breach to the previous login

somesoni2 — Thu, 21 Sep 2017 15:19:29 GMT

I missed taking that into consideration. Try this

your base search which includes events from both EventTypes
   | table _time User EventType Organization OrgCode
   | eval Organization=if(EventType="Login" AND isnull(Organization),"YourStaticValue", Organization)
   | eval OrgCode=if(EventType="Login" AND isnull(OrgCode),"YourStaticValue", OrgCode)
   | sort 0 -User, _time
   | eval prevUser=if(EventType="Login",User,null())
  | filldown prevUser Organization OrgCode
  | where EventType="Security Breach" AND User=prevUser

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 04 Oct 2017 18:10:48 GMT

Thank You so much. We were testing this for a week. Now we found another issue. If an user logins as 8:00 AM does some security breach events between 8:05 AM to 8:15 AM and when apply a time filter from 8:01 AM to 8:15 AM we will are not getting the correct data.

Re: Help with writing a join command that joins a security breach to the previous login

anuremanan88 — Wed, 04 Oct 2017 18:14:43 GMT

Or in other ways. If I want to search login alone 1 Hrs earlier than the time i selected for security breach events, how can i do it.