Using Context Hub Lists for whitelisting in ESA rules

[CraigCameronWei]

Just looking for a quick answer on the correct syntax for this.

I currently have a rule deployed that will alert only when a specific meta key is found in a CH list that's used as an enrichment in the rule. Syntax looks like this:

@RSAAlert(oneInSeconds=0)
@UsesEnrichment(name="Critical_Groups")

SELECT * from Event(
    medium = 32
    AND event_cat_name = 'User.Management.Groups.Modifications.User Added'
    AND EXISTS (SELECT * FROM Critical_Groups WHERE (LIST = Event.`group`))
)‍‍‍‍‍‍‍‍

What I'd like to do with a different use case is to alert when the meta key does NOT have a value from a list. Assuming I wanted to modify the above rule in this way (ie to trigger only when 'group' does not equal any value in the Critical_Groups enrichment), what's the right way to do it? Adding NOT in front of EXISTS didn't seem to do the trick, but I can't say for sure there's not some other issue with how I've set my test case up.

[JoshRandall]

That sounds like it should work, but there's always the possibility of some outlier scenario messing with the results.  Are you able to share any specific details about your other use case, or the data you're using to test against it?

With that said…your rule above is the basic blacklist syntax, whereas adding a NOT would change it to whitelist syntax:

        SELECT * FROM Event(

            /* Statement: whitelist syntax */

            (`group` IS NOT NULL AND NOT EXISTS (SELECT * FROM Guest_Accounts WHERE ( LIST = Event.`group` ) ))

            OR

            /* Statement: blacklist syntax */

            (`group` IS NOT NULL AND EXISTS (SELECT * FROM Guest_Accounts WHERE ( LIST = Event.`group` ) ))

        );

There is an issue with how the Rule Builder creates these statements, though, that might help in your case.  If you look at any of the RSA Live ESA rules that leverage CH lists, you'll see they account for both upper- and lower-case meta:

        SELECT * FROM

            Event(

                        (

                          (ec_subject='User' AND ec_activity='Lockout')

                           OR

                          (device_class = 'Windows Hosts' AND reference_id IN ('4740', '644'))

                        )

                        AND

                        medium = 32

                        AND user_dst IS NOT NULL

                        AND NOT EXISTS (SELECT * FROM User_Whitelist WHERE (LIST = Event.user_dst.toLowerCase()))

                        AND NOT EXISTS (SELECT * FROM User_Whitelist WHERE (LIST = Event.user_dst))

               ).win:time_length_batch(600 sec, 10) HAVING COUNT(*) = 10;

So you could try converting your Basic rule to Advanced and adding that .toLowerCase() condition.

[CraigCameronWei]

Right - I'll test some more and provide specifics if I can't get it working. And using .tolowercase() like that - means the entries in the list all have to be lowercase, yes?

[JoshRandall]

The way its used here means that your meta value can be unchanged or converted lowercase.

Example:

Meta:

            group = "Domain Admins”

Context Hub List:

            Critical_Groups = "domain admins”

SELECT * FROM Event(

            /* Statement: whitelist syntax */

            (`group` IS NOT NULL

            AND NOT EXISTS (SELECT * FROM Guest_Accounts WHERE ( LIST = Event.`group` ) ))

        );

            -this will alert because there is no match between the meta `group` and the CH List due to the different cases

SELECT * FROM Event(

            /* Statement: whitelist syntax */

            (`group` IS NOT NULL

            AND NOT EXISTS (SELECT * FROM Guest_Accounts WHERE ( LIST = Event.`group`.toLowerCase() ) ))

        );

            -this will not alert because there is a match between the meta `group` and the CH List due to the meta being converted to lower case

So just adding the two to a single statement covers instances where meta values match List values without any case conversion, as well instances where meta values only match List values after a meta value case conversion:

SELECT * FROM Event(

            /* Statement: whitelist syntax */

            (`group` IS NOT NULL

            AND NOT EXISTS (SELECT * FROM Guest_Accounts WHERE ( LIST = Event.`group` ) ))

            AND NOT EXISTS (SELECT * FROM Guest_Accounts WHERE ( LIST = Event.`group`.toLowerCase() ) ))

        );