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:
- Predicates to filter before it hits the target
- Using a histogram to store in buckets.
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'
ORDER BY name
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?
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(
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? : )