Thursday, May 16, 2013

[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  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:

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

Fortunately, I found this blog post which has a lot of details about it:
(yup, a 2-year-old post that's now incredibly relevant)


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'

Which lead me to:

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.


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

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

ADD EVENT sqlserver.login(
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?  : )

No comments: