Alert for Unique and distinct meta.

[MohdSaadKhan]

I have created an alert for a event in that it will create a window of 30 mins and grab my required events and output only unique/distinct meta but I am getting duplicate meta (duplicate device ip) despite of putting required condition;

SELECT distinct * FROM Event(
/* Statement: Virus Detected on system and Left alone */
(device_type IN ( 'symantecav' ) AND ( 'Left alone' = ANY( action ) ))
).win:time(30 min)

and also tried this as I want unique and distinct device.ip from a pool of 30 mins;

SELECT * FROM Event(
/* Statement: Virus Detected on system and Left alone */
(device_type IN ( 'symantecav' ) AND ( 'Left alone' = ANY( action ) ))
).std:unique(device_ip).win:time(30 min)

Can anyone correct this issue?

[AndrewOjha]

Hi Mohd,

Not exactly replicating the use-case you're developing here, but, below are examples of similar AV focused EPL Rules which you may find useful to consider:

Watchlist ESA Rule

E.g. Device flagged in a virus event during the last 20 mins, connecting to a known malicious website / IP (IOC hit)

CREATE WINDOW WatchList.win:time(20min) (ip_src string);

INSERT INTO WatchList SELECT ip_src from Event(virusname IS NOT NULL);

SELECT * FROM Event(threat_source IS NOT NULL)

WHERE ip_src IN (SELECT ip_src FROM WatchList);

Filter Statement ESA Rule

E.g. Device flagged in a virus event, signature is not equal to sample generic name, where signature is present on 3 more unique source IP addresses – within 3 mins

SELECT * FROM Event (

ip_src IS NOT NULL

AND

virusname IS NOT NULL

AND

virusname !='generic_virus_name'

AND

filename IS NOT NULL

).std:groupwin(virusname)

.win:time_length_batch(180 seconds,3)

.std:unique(ip_src)

GROUP BY virusname HAVING COUNT(DISTINCT ip_src) > 3;

Filter Statement ESA Rule

 E.g. Device flagged in a virus event, where virus name is present on 3 or more unique source IP addresses within 25 mins

SELECT * FROM Event(

(event_source .toLowerCase() IN ( 'oas' ) AND

virusname IS NOT NULL))

.win:time_batch(25 min)

GROUP BY virusname HAVING COUNT(DISTINCT ip_src) > 3;

[LeeKirkpatrick]

Hey Mohd,

Try something like the following:-

SELECT * FROM Event
(device_type IN ( 'symantecav' ) AND ( 'Left alone' = ANY( action ) ))
GROUP BY device_ip
OUTPUT LAST every 30 min

Cheers,

Lee

[MohdSaadKhan]

Hi lee,

Thanks for your reply. I have applied the same EPL, though the rule is valid but it's not getting triggered as it is not meeting up the desired criteria.

[MohdSaadKhan]

Hi Andrew,

Thanks for your apply. I have below rule as 

SELECT * FROM Event(
/* Statement: Virus Detected on system and Left alone */
(device_type IN ( 'symantecav' ) AND ( 'Left alone' = ANY( action ) ))
).std:unique(device_ip).win:time(30 min)

and I want distinct/unique device_ip within 30 mins.

[RaviKrishnanand]

Lee,

I also tried your suggestion, it seems that "LAST OUTPUT" command works but not "GROUP BY". Let me know if you know the way to make "GROUP BY" command works. Thanks!!

[LeeKirkpatrick]

Hi Both,

Could you explain your requirements once again? Maybe I am misinterpreting them.

Cheers,

Lee

[RaviKrishnanand]

I'm trying to group the alerts by Meta Key (Ex. ip_dst or device_ip, etc.) for 15 minutes & fire only after every 15 minutes.As I mentioned in the previous update, 15 minutes aggregation works with command "OUTPUT LAST" but it's not grouping the alerts by Meta Key. Thanks!!

[MohdSaadKhan]

Hi Lee,

My requirement is;

I want to create a window of n minutes lets take 20 mins. and want to collect the data with condition device_ip ="symantec" and action = "leftalone".

Now after 20 mins when there is output of alert then there should not be any duplicacy of device_ip meta.

ex.:

if device_ip collected under 20 mins are; (10.6.1.1,10.6.1.1,10.6.1.1.10.6.2.2.10.6.3.3) then it output only device ip (10.6.1.1,10.6.2.2,10.6.3.3) removing rest duplicates of 10.6.1.1

[LeeKirkpatrick]

Hey Ravi,

The following should meet your needs. This will group the user_dst value for 15 minutes and output a separate alert for each grouped user (change meta key and filters as required):-

INSERT INTO AlertStream
SELECT window(*) FROM Event(user_dst IS NOT NULL).std:groupwin(user_dst).win:time_batch(15 min) GROUP BY user_dst HAVING COUNT(*) >= 1;

@RSAAlert

SELECT * FROM AlertStream

Cheers,

Lee