billgraziano / xelogstash Goto Github PK
View Code? Open in Web Editor NEWSend SQL Server Extended Events to Logstash, Elastic Search, or JSON
License: Other
Send SQL Server Extended Events to Logstash, Elastic Search, or JSON
License: Other
For wait_info and wait_info external, xecap_description should include the duration, the wait and the SQL statement.
For SQL events, add a duration_sec field that reports the duration in integer seconds
Filtering multiple SQL statements is difficult. XE isn't consistently reporting has values. Consider creating a hash value for each SQL text -- especially for SQL batch completed.. Something like sql_text_meta_hash. Maybe put "meta" in all the column names that I generate. Or maybe sql_text_xe_hash since we're using the "xe" prefix for other things I generate. Maybe only do this for "long text"? Longer than 100 characters?
In the source sessions, allow a "-" prefix or "+" prefix for a session name. This will either add or remove from the default sessions instead of replacing. If a non-adjusting entry is found, then generate an error. That should make it easier to use the defaults in most cases.
Populate this field with the build number
Consider moving the code that removes the lock file later in the process. Make sure any code that runs after this is safe for multiple running instances.
Some events report duration in milliseconds and some in microseconds. We should convert milliseconds ones to microseconds so that our duration column is consistent.
We should ignore this error so we can include all the system_health sessions. Or make it a warning.
[2] *** ERROR: Domain: DOMAIN - FQDN: SQL08 - XE - system_health : validatesession: no file target
goroutine 165 [running]:
github.com/billgraziano/xelogstash/app.(*Program).run.func1()
C:/dev/github.com/xelogstash/app/program.go:165 +0x14f
panic(0xb9cb00, 0x128eef0)
c:/go/src/runtime/panic.go:969 +0x174
github.com/billgraziano/xelogstash/logstash.(*Logstash).setTimeouts(0xc000222300)
C:/dev/github.com/xelogstash/logstash/logstash.go:73 +0x4b
github.com/billgraziano/xelogstash/logstash.(*Logstash).Writeln(0xc000222300, 0xc002b5ec00, 0x5c7, 0xc000006648, 0x195)
C:/dev/github.com/xelogstash/logstash/logstash.go:132 +0x15a
github.com/billgraziano/xelogstash/sink.(*LogstashSink).write(0xc00021f0b0, 0xc001b24d18, 0x5, 0xc002b5ec00, 0x5c7, 0x51b359, 0x12a0560, 0xc4dbc0)
C:/dev/github.com/xelogstash/sink/logstash.go:185 +0x4e
github.com/billgraziano/xelogstash/sink.(*LogstashSink).Write(0xc00021f0b0, 0xd66380, 0xc0000e4340, 0xc001b24d18, 0x5, 0xc002b5ec00, 0x5c7, 0x0, 0x0, 0x0)
C:/dev/github.com/xelogstash/sink/logstash.go:125 +0xbf
github.com/billgraziano/xelogstash/app.(*Program).processSession(0xc0001ba000, 0xd66380, 0xc0000e4340, 0xc, 0xc00246b380, 0xf, 0xc00246b378, 0x4, 0xc00246b3a0, 0xf, ...)
C:/dev/github.com/xelogstash/app/process_session.go:300 +0x1b46
github.com/billgraziano/xelogstash/app.(*Program).ProcessSource(0xc0001ba000, 0xd66380, 0xc0000e4340, 0xc, 0xc00009d160, 0xf, 0xc0000d0780, 0x6, 0x6, 0x0, ...)
C:/dev/github.com/xelogstash/app/process_source.go:64 +0xbb3
github.com/billgraziano/xelogstash/app.(*Program).run(0xc0001ba000, 0xd66380, 0xc0000e4340, 0xc, 0x0, 0x0, 0x0, 0x0, 0xc00009f000, 0x4, ...)
C:/dev/github.com/xelogstash/app/program.go:214 +0x632
created by github.com/billgraziano/xelogstash/app.(*Program).startPolling
C:/dev/github.com/xelogstash/app/program.go:138 +0xa3e
goroutine 1 [syscall, 28 minutes, locked to thread]:
syscall.Syscall(0x7ffa73f8f5f0, 0x1, 0xc0000c5c68, 0x0, 0x0, 0x0, 0x0, 0x0)
c:/go/src/runtime/syscall_windows.go:188 +0xe9
golang.org/x/sys/windows.StartServiceCtrlDispatcher(0xc0000c5c68, 0xc88bc0, 0xc000049ac0)
C:/Users/graz/go/pkg/mod/golang.org/x/[email protected]/windows/zsyscall_windows.go:508 +0x6b
golang.org/x/sys/windows/svc.Run(0xc62055, 0xb, 0xd5d0e0, 0xc00005d140, 0xd60e00, 0xc0001ba000)
C:/Users/graz/go/pkg/mod/golang.org/x/[email protected]/windows/svc/service.go:352 +0x1be
github.com/kardianos/service.(*windowsService).Run(0xc00005d140, 0xc000000006, 0xc658e0)
C:/Users/graz/go/pkg/mod/github.com/kardianos/[email protected]/service_windows.go:261 +0x18a
main.main()
C:/dev/github.com/xelogstash/cmd/sqlxewriter/main.go:146 +0xf4a
goroutine 21 [chan receive]:
github.com/billgraziano/xelogstash/app.ConfigureExpvar.func1()
C:/dev/github.com/xelogstash/app/expvar.go:35 +0xc1
created by github.com/billgraziano/xelogstash/app.ConfigureExpvar
C:/dev/github.com/xelogstash/app/expvar.go:34 +0x6e5
goroutine 20 [chan receive, 28 minutes]:
github.com/kardianos/service.(*windowsService).Execute(0xc00005d140, 0xc000084c50, 0x1, 0x1, 0xc000082120, 0xc000082180, 0x0)
C:/Users/graz/go/pkg/mod/github.com/kardianos/[email protected]/service_windows.go:175 +0x115
golang.org/x/sys/windows/svc.(*service).run.func1(0xc000049ac0, 0xc000084c50, 0x1, 0x1, 0xc000082120, 0xc000082180, 0xc0000821e0)
C:/Users/graz/go/pkg/mod/golang.org/x/[email protected]/windows/svc/service.go:238 +0x71
created by golang.org/x/sys/windows/svc.(*service).run
C:/Users/graz/go/pkg/mod/golang.org/x/[email protected]/windows/svc/service.go:237 +0x203
goroutine 6 [select, 28 minutes]:
golang.org/x/sys/windows/svc.(*service).run(0xc000049ac0)
C:/Users/graz/go/pkg/mod/golang.org/x/[email protected]/windows/svc/service.go:250 +0x379
created by golang.org/x/sys/windows/svc.Run
C:/Users/graz/go/pkg/mod/golang.org/x/[email protected]/windows/svc/service.go:350 +0x1b0
goroutine 152 [IO wait, 14040 minutes]:
internal/poll.runtime_pollWait(0x27930f08, 0x72, 0xd5e200)
c:/go/src/runtime/netpoll.go:203 +0x5c
internal/poll.(*pollDesc).wait(0xc000182948, 0x72, 0x1252500, 0x0, 0x0)
c:/go
I think this is causing the instability I see when the app doesn't exit. It might be slower but that seems a fair trade-off.
We could either put in the TOML file or the command line. That way could run tests to multiple targets easily.
This way I can have a common configuration and a sources per domain or per ingest box.
Consider a better way to handle TCP timeouts. It seems to be hanging after a SEND waiting for a response. Maybe set a deadline of some type.
I keep running into issues where killing the Agent job doesn't stop the executable. Find this if possible.
If not, error out on multiple running copies.
https://github.com/rodolfoag/gow32
That's one way. Or maybe create the mutex on the full path and and EXE name. That's probably better.
Another option is a lock file in the directory.
If it encounters another running copy, error out. That will eventually send an alert.
For environments that have multiple logstash targets feeding to the same ELK stack, consider an array of targets. Try each one in order until one succeeds. Maybe a LogstashPool object as a parent?
There are repeating jobs that create objects. These include tables, statistics, indexes, snapshots, etc. Find a way to flag these as xecap_repeating = true. Everything else gets a repeating = false.
Can I query ELK for this?
Probably build in some type of memory and track which days a given event appears in. If it appears for three consecutive days, then flag it as repeating.
Logstash is loading my app messages out of order. See if I can force them with a sequential message ID. Format would be day_time_sequence. It would have to be fixed width for each. Day and time would be the start time of the application. Almost an execution_id. That way it is sort-able.
I'm wildly curious who is using this and how many servers.
I've got five or six organizations using it. The largest is just under 100 servers spread across a few domains.
How many servers? How has it helped?
Include a way to keep the raw message field for searching. Then all the columns can be switched to keywords. Maybe a "raw_message_field" attribute?
The most common would be server name, login name, application name. This is because Elastic is case-sensitive. This needs to be either upper or lower case.
Right now, $(VARIABLE) is the variable pattern. Consider:
Create a filter that looks like: host:session:event:field:regex
. Each one would be a regular expression and all would have to match. Examples with approximate regexes...
*:*:error_reported:error_number:17830
would filter out 17830's across all servers*:*:*:sql_text|statement:^WAITFOR.*
Any SQL that starts with a WAITFOROr maybe just a way to flag them as not interesting. How to include an action here? Maybe a last column saying "exclude" or "flag" or some such.
MSdistribution_history
MSlogreader_history
MSrepl_errors
Downloaded but unable to find xelogstash.exe file
Assume that we aren't writing directly to logstash anymore. So let's make this entire section optional.
Could you please provide steps for generating xelogstash.exe file. I have tried to generate exe file in windows but lot of dependence like git,make, go language. I tried same thing in the linux but this related to windows So we could not able to make it to xelogstash.exe .
Really very helpful if we provide steps to generate the exe file.
The AG events only report the GUIDs for AGs and databases. Need to look these up ahead of time to map them. Except for SQL Server 2008.
Verify that ElasticSearch has a 32KB limit on fields. Options for large SQL statements...
Consider filtering the following message:
(Msg 3701, Level 11, State 5) Cannot drop the table '#SVer', because it does not exist or you do not have permission.
This appears to be a bug in SSMS (or SQL Server)
I should be able to look back in the AG health session and figure out what the AG role was at any point in time. Use that and add it as a field. mssql_ag_role?
Exclude the log rows for the debug DLL. this is loaded whenever we read an XE file if we're on SQL Server 2012. Maybe include an option to include them?
2018-07-13 07:42:05.46 spid113 Using 'dbghelp.dll' version '4.0.5'
This is mostly for SQL Server 2008 instances so I can get jobs.
If the sessions array is zero length, don't get the defaults and overwrite it. Probably have to check the meta data and see if it was defined. Only get it if there isn't a key defined for the source.
errorlog_category
. Strip the spid number.If we get behind and have to "read past" to past a specific file or offset, consider not reading the event_data. That has to be faster for large numbers of XE events when we're behind.
See if I can hard code a check for this and not error
When catching up a session, consider only reading the file_name and file_offset until we have a good value. Pulling back the event_data generates a lot of network traffic across the WAN.
I try following logstash input:
input {
tcp {
port => 5000
}
}
But I have an warning in Logstash about character encoding
:
[WARN ][logstash.codecs.line ] Received an event that has a different character encoding than you configured. {:text=>"\\u001F r\\b{\\x8F\\xDD\\xFA,,\\xBB\\xF5\\xF0\\x9B\\xA9ฮ\\x85\\u0012\\xBF\\xB2\\xA6\\u00049\\xD0\\xC67\\xA2}S5\\xFE\\xF5", :expected_charset=>"UTF-8"}
Would you please leave here a Logstash input example that is compatible with xelogstash?
Currently a an availability_replica_state_change looks like this:
AGName: PRIMARY_NORMAL -> RESOLVING_NORMAL
Change it to this:
AGName: InstanceName: PRIMARY_NORMAL -> RESOLVING_NORMAL
Also add a counter for XE rows read. I think there's a session that's reading an entire session to try to get caught up and it's taking a LONG time.
Multiple services will eventually want to use this port
I want a way to group multiple events together to make filtering easy.
Create a field named: xe_name_group.
It can have the following values:
Look in sysmessages for "login failed" or "logon failed".
Yes, this will get successful logins if those are being logged to an errorlog. Hopefully no one is doing that.
When I ran .\xelogstash.exe /config start.toml
following Error occurred:
PS C:\Program Files\xelogstash> .\xelogstash.exe /config start.toml
2019-06-05 01:18:20+04:30 INFO ==================================================================
2019-06-05 01:18:20+04:30 INFO app-start version: 0.33; workers 128; default rows: 100; sha1: dev
2019-06-05 01:18:20+04:30 INFO app.logstash is empty. Not logging SQL Server events to logstash.
2019-06-05 01:18:20+04:30 INFO applog.logstash is empty. Not logging application events to logstash.
2019-06-05 01:18:20+04:30 INFO Processing 1 sources...
2019-06-05 01:18:20+04:30 INFO [1] Source: 127.0.0.1; Sessions: 1
2019-06-05 01:18:20+04:30 ERROR [1] - fqdn: 127.0.0.1 err: db.ping: SQLDriverConnect: {28000} [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'blah\backup'.
{28000} [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'blah\backup'.
2019-06-05 01:18:20+04:30 INFO Processed 0 events
2019-06-05 01:18:20+04:30 ERROR *** ERROR ****
How should I get rid of it?
Where should I put sql server credentials?
Allow to populate a field with the time xelogstash generates the JSON object. This will be helpful for an ingest dashboard.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.