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

[anuremanan88]

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.

[anuremanan88]

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

[Sukisen1981]

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

[anuremanan88]

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

[somesoni2]

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

[anuremanan88]

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.

[somesoni2]

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

[anuremanan88]

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

[somesoni2]

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

[anuremanan88]

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?

[somesoni2]

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

[anuremanan88]

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

[somesoni2]

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

[anuremanan88]

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.

[anuremanan88]

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.

[somesoni2]

Above query should work for that case as well, just keep the timerange larger to include all required time ranges. E.g. If you're running this a report every hour, try to run with time range last 2 hours. You may have to add following to last where clause to avoid same breach reported twice, for same example.

.... | where EventType="Security Breach" AND User=prevUser AND _time>=relative_time(now(),"-1h")

[Sukisen1981]

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

[anuremanan88]

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.

[Sukisen1981]

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 User1 Login
10:55 AM User1 SecBrch

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

[somesoni2]

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

[anuremanan88]

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.