Example Advanced EPL Rules

Following are the examples of Advanced ESA rules. Each example has multiple ways of implementing the same use-case.

For best practices on writing advanced EPL rules, see ESA Rule Writing Best Practices.

Example #1:

Create a user account and delete the same user account in 300s. User information is stored in user_src meta.

EPL #1:

Rule Name CreateAndDelete Useraccount1
Rule Description Create a user account followed by an action to delete the same user account in 300 seconds.
Rule Code

@RSAAlert(oneInSeconds=0)
SELECT * FROM Event(ec_subject='User'
AND ec_outcome='Success'
AND user_src is NOT NULL
AND ec_activity IN ('Create', 'Delete')
).win:time(300 seconds)

match_recognize (partition by user_src
​ measures C as c, D as d
pattern (C D)
define
C as C.ec_activity='Create' ,
D as D.ec_activity='Delete');

Note
  • Filter events needed for pattern in given time frame. Filter conditions should be such that only required events are passed to match recognize function. In this case, they are create and delete user account Events. That is, Event(ec_subject='User' AND ec_outcome='Success' AND user_src is NOT NULL AND ec_activity IN ('Create', 'Delete')
  • Partition by creates buckets. In this case, Esper creates buckets per value of user_src. And hence value of user_src is common between both events.
  • Define the pattern you want. Right now it is set to Create Followed by Delete. You can do multiple creates followed by delete (C+ D). Pattern is very similar to regular expression.
  • Most efficient use case.

  • The ‘loose’ pattern match of (C+ D) will result in decreased performance. Unless you need to include all C events within the generated alert, keep the strict pattern match of (C D). See the Esper documentation for more details.

EPL #2:

Rule Name CreateAndDeleteUseraccount2
Rule Description Create a user account followed by an action to delete the same user account in 300 seconds.
Rule Code

@RSAAlert(oneInSeconds=0)
SELECT * from pattern[every (a= Event(ec_subject='User' AND ec_outcome='Success' AND user_dst is NOT NULL AND ec_activity IN ('Create'))

->

( Event(ec_subject='User' AND ec_outcome='Success' AND user_dst is NOT NULL AND ec_activity IN ('Create') AND user_src = a.user_src)
) )where timer:within(300 Sec) ];
Note
  • Lets say same user is created twice and deleted once in that order. Then the above pattern will fire 2 alerts.
  • A thread is created for every User creation.
  • There is no way to control threads. It is important to have time bounds and preferably small intervals.

  • If you do not need every first event to start a new thread and match with the subsequent second event, then add suppression syntax of @SuppressOverlappingMatches after the pattern keyword. See the Esper documentation for more details.

Example #2:

Detect pattern where user created followed by login by same user and user is deleted in end. In case of windows logs user info is stored in either user_dst or user_src depending on event.

user_src(create) = user_dst(Login) = user_src(Delete)

EPL #3:

Rule Name CreateUserLoginandDeleteUser
Rule Description Detect a pattern where a user creates a User account followed by login by the same user followed by deletion of the User account.
Rule Code

@RSAAlert(oneInSeconds=0)
SELECT * FROM Event(ec_subject='User'
and ec_activity in ('Create','Logon','Delete')
and ec_theme in ('UserGroup', 'Authentication')
and ec_outcome='Success'
).win:time(300 seconds)
match_recognize (measures C as c, L as l, D as d
pattern (C L D)
define
C as C.ec_activity = 'Create',
L as L.ec_activity = 'Logon' AND L.user_dst = C.user_src,
D as D.ec_activity = 'Delete' AND D.user_src = C.user_src
);

Note
  • Since user_src/user_dst is not common across all events we can't use partition. It will be 1 single bucket running 1 pattern at a time. For example, for user 1 and 2 if the stream of events are C1C2L1D1, C1L1C2D1, there will be no alert because C1 thread got reset by C2. Alert will be fired only if C1L1D1 are in order and no other event either from same user or other user falls in between.
  • Another solution would be to use Named Window and merge user_dst and user_src into single column and then run match recognize. (EPL #3).
  • Pattern can also be used. You might get more alerts than expected. (EPL #4).

EPL #4: Using NamedWindows and match recognize

Rule Name CreateUserLoginandDeleteUser
Rule Description Detect a pattern where a user creates a User account followed by login by the same user followed by deletion of the User account.
Rule Code

@Name('NormalizedWindow')
create window FilteredEvents.win:time(300 sec) 
(user String, ecactivity string, sessionid Long);

@Name('UsersrcEvents') 
Insert into FilteredEvents 
select user_src as user, ec_activity as ecactivity, sessionid from Event(
ec_subject='User' and ec_activity in ('Create','Delete') and ec_theme in ('UserGroup', 'Authentication') and ec_outcome='Success' and user_src is not null );

@Name('UsrdstEvents') 
Insert into FilteredEvents 
select user_dst as user, ec_activity

as ecactivity, sessionid from Event(
ec_subject='User' and ec_activity in (Logon’) and ec_theme in ('UserGroup', 'Authentication') and ec_outcome='Success' and user_dst is not null 
);

@Name('Pattern')

@RSAAlert(oneInSeconds=0, identifiers={"user"})


select * from FilteredEvents
​ match_recognize (

partition by user

measures C as c, L as l, D as d
pattern (C L+D)

define 
C as C.ecactivity= ‘Create’,

L as L.ecactivity= ‘Logon’,
D as D.ecactivity=’Delete’
);

EPL #5: Using Every @RSAAlert(identifiers={"user_src"})

Rule Name CreateUserLoginandDeleteUser
Rule Description

Detect a pattern where a user creates a User account followed by login by the same user followed by deletion of the User account.

Rule Code

SELECT a.time as time,a.ip_src as ip_src,a.user_dst as user_dst,a.ip_dst as ip_dst,a.alias_host as alias_host from pattern[every (a=Event (ec_subject='User' and ​ec_activity='Create' and ec_theme='UserGroup' and ec_outcome='Success') -> (Event(ec_subject='User' and ec_activity='Logon' and ec_theme='Authentication' and user_src=a.user_dst) -> b=Event(ec_subject='User' and ec_activity='Delete' and ec_theme='UserGroup' and user_dst=a.user_dst))) where timer:within(300 sec)];

Example #3:

Excessive login failures from same sourceIP.

EPL #6: @RSAAlert(identifiers={"ip_src"})

Rule Name ExcessLoginFailure
Rule Description The same user tried logging in from the same Source IP and faced login failures.
Rule Code

@RSAAlert(oneInSeconds=0)
SELECT * FROM Event ( ip_src IS NOT NULL AND ec_activity ='Logon'
AND ec_outcome = 'Failure').win:time_batch(300 seconds) GROUP BY
ip_src HAVING COUNT(*) = 10;

Note

  • Uses time _batch: Looks at events in batches(tumbling window). Every event matching the filter criteria will be kept for the specified time window.

  • “GROUP BY” clause aggregates events within the data window by ip_src and HAVING clause instructs a count of 10 events with the same ip_src must occur within the time window.

  • One of the issues with tumbling windows is that events occurring towards the end of the batch might not lead to an alert.

In the below sequence of events at t=301 even though 10 login failures occurred for the same login in the last 300 secs, there will be no alert because the batch of events was dropped at t=300.

Time t Login Failures for Specific Users Alert Time Batch
0 0 0 1
295 6 0 1
299 3 0 1
301 1 0 2
420 6 0 2
550 3 0 2
600 0 0 3
720 6 0 3
850 3 0 3
900 1 1 3 ends and 4 begins

  • Above problem can be resolved using win:time windows (EPL#7)instead of win:time_length_batch windows.

  • Outer group by is to control events when time elapses. Say you have 9 events at end of 60 secs, Esper engine will push those 9 events to listener. Group by and count will restrict it since count is not equal to 10.

  • Time and count can be modified as needed.

EPL #7: @RSAAlert(identifiers={"ip_src"})

Rule Name ExcessLoginFailure
Rule Description The same user tried logging in from the same Source IP and faced login failures.
Rule Code

@RSAAlert(oneInSeconds=0)
SELECT * FROM Event ( ip_src IS NOT NULL AND ec_activity ='Logon'
AND ec_outcome = 'Failure').win:time(300 seconds) GROUP BY
ip_src HAVING COUNT(*) = 10;

Note
  • This is a sliding window and hence after an alert is fired for a set of events they can be used for another alert as well until time has passed.
  • If 10 events were involved in causing the alert only the last event will appear.
  • Events are not removed from the time window. You could use output rate limiting. See the Esper documentation for more details.

Example #4:

Multiple failed logins from multiple different users from same source to same destination, a single user from multiple different sources to same destination.

EPL #8: using time_batch

Rule Name MultiplefailedLogins
Rule Description There are multiple failed logins for the following cases:
- From multiple users from same source to same destination.
- Single user from multiple sources to the same destination.
Rule Code

@RSAAlert(oneInSeconds=0)
SELECT * FROM Event (

ec_activity='Logon'

AND ec_outcome='Failure'

AND ip_src IS NOT NULL

AND ip_dst IS NOT NULL

AND user_dst IS NOT NULL

)

.win:time_batch(300 seconds)

group by ip_src,ip_dst

having count(distinct user_dst) >= 5;
Note
  • ip.dst and ip.src are common across all events.
  • user_dst is unique for all events.
  • Alert is fired when there are at least 5 different users try to login from same ip.src and ip.dst combination.

Example #5:

No Log traffic from a device in a given timeframe.

EPL #9: using timer:interval

Rule Name NoLogTraffic
Rule Description There is no log traffic observed from a device in a given time frame.
Rule Code

SELECT * FROM pattern [every a = Event(device_ip IN ('10.0.0.0','10.0.0.1') AND medium = 32) -> (timer:interval (3600 seconds) AND NOT Event(device_ip = a.device_ip AND device_type = a.device_type AND medium = 32))];

Note
  • Rule only detects sudden loss of traffic. It won't alert if there is no traffic to begin with. You need at least 1 event for rule to alert.
  • List of device ip address or device hostnames as input. Only these systems will be tracked.
  • Time input is required. Alert is fired when time interval between events exceeds input time.

Example #6:

Multiple Failed Logins NOT followed by a Lockout event by the same user.

EPL #10: using timer and Lockout

Rule Name FailedloginswoLockout
Rule Description There are multiple failed logins that are not followed by Lockout event by the same user.
Rule Code

SELECT * FROM pattern 
[every-distinct(a.user_dst, a.device_ip, 1 msec) (a= Event(ec_activity='Logon' and ec_outcome='Failure' and user_dst IS NOT NULL)
-> [2]( Event( device_ip =a.device_ip and ec_activity='Logon' and ec_outcome='Failure' and user_dst=a.user_dst) 


AND NOT Event( ( ec_activity='Logon' and ec_outcome='Success' and device_ip = a.device_ip and user_dst=a.user_dst) or (ec_activity='Lockout' and device_ip = a.device_ip and user_dst=a.user_dst))))


where timer:within(60 seconds) -> (timer:interval(30 seconds) and not Event(device_ip=a.device_ip and user_dst=a.user_dst and ec_activity='Lockout'))
];

Note
  • Above query detects the absence of a Lockout Event after the occurrence of 2 failed logins from same user.
  • The occurrence of the multiple failed logins are timed and are assumed to occur within a certain period of time. Also, in-practice the Lockout event is assumed to occur within a short time after the occurrence of the last failed login event because the threshold value of Failed logins per user is set in a given domain.
  • In current query, every distinct will suppress new thread for combination of user and device for 1 millisec.
  • Time allowed for 3 failed logins is 60 secs since 1st failed attempt. Wait period for lockout event to occur is 30 secs

Example #7:

Custom functions to perform LIKE and REGEX operations for ARRAY elements.

EPL #11: @RSAAlert(oneInSeconds=0)

Rule Name MatchLikeRegex
Rule Description There are custom functions to perform LIKE and REGEX comparisons of array meta keys.
Rule Code

SELECT * FROM pattern[

e1=Event(matchLike(alias_host, "10.0.0.%")) AND

e2=Event(matchRegex(alias_host, "10\.0\.0\.1[0-9][0-9]"))

where timer:within(5 Minutes)];

Note:
1. “.” in meta keys should be replaced with (”_”).
2. All patterns should be time bound.
3. Use appropriate tags in front of statements, for example:
@RSAPersist:
@RSAAlert:

For additional details you can refer to: