[Extended Events] list of predicates that you can use to filter actions

I've been using XE lately to get a list of servers that connect to a server I'm going to upgrade (see http://thebakingdba.blogspot.com/2013/04/extended-events-what-servers-are.html).  That way I know what the downstream effects are beforehand.

However, these are busy servers - even with connection pooling, we can get hundreds of connections a minute. Which means even minimal logging will get big, fast.

So there are two ways to prevent that:

1. Predicates to filter before it hits the target
2. Using a histogram to store in buckets.

However, a histogram can only store one value and a count.  I haven't figured a way to combine the fields (and according to Jonathan Kehayias you can't), so I requested Microsoft add a multi-field histogram (which got closed as Won't Fix: http://connect.microsoft.com/SQLServer/feedback/details/785063/extended-events-a-better-histogram-multiple-value-fields)

So, back to  #1, and looking at Predicates. 

Fortunately, I found this blog post which has a lot of details about it:
http://blogs.msdn.com/b/extended_events/archive/2010/06/24/today-s-subject-predicates.aspx
(yup, a 2-year-old post that's now incredibly relevant)


Specifically,

SELECT name, description,
    (SELECT name FROM sys.dm_xe_packages WHERE guid = o.package_guid) package
FROM sys.dm_xe_objects o
WHERE object_type = 'pred_compare'
ORDER BY name


Which lead me to:

sqlserver.like_i_sql_ansi_string
sqlserver.like_i_sql_unicode_string


From the blog post:
"Calling a pred_compare directly follows this syntax:

package_name.pred_compare(field/pred_source, value)

[...]ADD EVENT wait_info (WHERE package0.greater_than_max_int64(duration, 500)
 "


Okay, I can do that.

 WHERE (
              (sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )

which gives me everything from our laptops.  Close.  Can I do a NOT LIKE?

 WHERE (
              (NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )


Which, yes, works.  
So my query to look for logins not from our laptops, and excluding certain servers, looks like...

CREATE EVENT SESSION [Logins] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_name)
WHERE ([sqlserver].client_hostname NOT LIKE '%-SERV-%')
              AND (NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )
ADD TARGET package0.ring_buffer(SET max_memory = 4096)

And, typing this in... I see I'm already excluding using a LIKE.  Why'd I go through all this effort again?  Um, knowledge, I guess?  : )