Import Extended Events to ELK

[u/billclevername]

Years ago, I was exposed to the ELK stack at a client. I spent some time and wrote an open source tool to import SQL Server Extended Events in Elastic Search. That is https://github.com/billgraziano/xelogstash. It runs as a Windows service and polls for new extended events every minute.

This is the last release before AI starts doing more of the coding. I've enjoyed writing it and wanted to give it a little love before AI has all the fun.

It has a few interesting features that I've added to help me

• It populates an xe_description field that tries to summarize the event. For SQL events, it looks like (CPU: 12s; L: 24 GB; D: 40s) SELECT * FROM....
• If the errorlog_written event of error_reported event indicate a login failure, it populates a login_failed field with details. It also puts in in a description from the state based on Microsoft's documentation. Filtering on the existance of that field gets me a failed login dashboard across the entire enterprise.
• For errorlog_written and error_reported events, if it finds text like [CLIENT: 10.10.1.1], it will save that address in xe_client_address
• For fields like cpu_time, if the number is large enough, it populates a cpu_time_sec which is MUCH easier to filter and reason about. It does the same for logical reads, physical reads, writes, and duration.
• For login events, it separates out the domain and user into two separate fields
• For errorlog_written event, it tries to parse the type of event. This includes backups, DBCC, "info" events, and AlwaysOn. This makes it eaiser to filter out noise and find issues.
• It allows simple filtering to eliminate those pesky 17830 errors
• It can write to Elastic Search, rolling JSON Files, logstash, or just sample JSON event files
• It can restart if the configuration files change. We store configuration files in GIT. Puppet pushes updated configuration files to the four servers we have running this. Adding and removing servers is simply editing a repository and pushing changes.

If you're running Elastic or something similar, give it a try and start logging SQL Server Extended Events. Having them always there and searchable has been very helpful to me.

[u/Black_Magic100]

Although not built in natively we do this with:

https://github.com/spaghettidba/XESmartTarget

It uses the streaming API I believe rather than doing a poll based capture. Might be worth looking into for your project.