billgraziano / xelogstash Goto Github PK

For wait_info and wait_info external, xecap_description should include the duration, the wait and the SQL statement.

• Write events to SQL Server
• search for a table name matching the event name at start up with xe prefix (dbo.xe_login)
• start with logins
• Need to support cross-domain so that means a login and a password
  • consider integrating the vault for this
• SQL Server 2017+ supports VARCHAR(MAX) in a column store index

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

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.

• xelogstash.toml and xelogstash_domain_descriptor.toml (I like this one -- except DEV and PROD in the same domain)
• pointer from one file to the other
• command line configuration -- service?

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.

https://gist.github.com/hongster/04660a20f2498fb7b680

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?

• Create a map[string]string with XE events for the key
• The value is a GO template
• Use the key value of fields from the event to generate the template
• Read in a JSON file with keys and templates so people can generate (or override) the settings

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:

• $(VARIABLE | lower)
• $(VARIABLE | upper)
• {{ .variable | upper)
• Maybe just two config parameters upper = [] & lower = []. This goes through all those fields and sets the casing. That's certainly the easiest.

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 WAITFOR

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

@billgraziano

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

1. Split them into multiple fields statement_0, statement_1, etc.
2. Strip out long strings and replace with '{long_string}'
3. Truncate at 32KB. Which may truncate the WHERE clause.

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.

• session not found is warning instead of silent

• For xe_description: Pull out the time stamp and category/spid and put those at the back in parentheses
• Put the category in new field named errorlog_category. Strip the spid number.
• Maybe add some smarts like looking for DBCC

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:

• sql: for all four SQL events
• login: for errorlog_written and errorlog_process:logon OR error_reported and any error number associated with login failures (184546, etc.)

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.