Column 'X' does not belong to underlying table 'events'

[zikato]

Column 'X' does not belong to underlying table 'events'

[spaghettidba]

Thanks! I'll have a look as soon as possible (might take a while...)

[spaghettidba]

Hi Tom, thanks for reporting the issue.
Regarding the possibility to return NULL when the column is missing, I'm quite skeptic. The data type of the column is based on the data type of the underlying field/action, so I would have nothing base the choice on.
Maybe I can work on a solution if I have an example.
Could you please share the session script and json file you used?

[zikato]

I've found the problem with the Cannot find column [is_system]. The column has to be in the OutputColumns section to be recognized even though I only use it for filtering.

I can share the session and json, but the XE data will be hard to reproduce. It monitors Linked server calls through a Service Broker activation.

[zikato]

This is the XE session (I'm planning to blog about it after SQLbits ;) )

CREATE EVENT SESSION [LinkedServer_Outgoing]
ON SERVER
ADD EVENT sqlserver.oledb_query_interface
(
	ACTION
	(
		sqlserver.client_app_name
		, sqlserver.client_hostname
		, sqlserver.server_principal_name
		, sqlserver.server_instance_name
		, sqlserver.database_name
		, sqlserver.query_hash_signed
		, sqlserver.sql_text
		, sqlserver.tsql_stack
		, sqlserver.is_system /* to distinguish ServiceBroker Activation */
		)
	WHERE 	
		[opcode]='Begin'
		AND [sqlserver].[like_i_sql_unicode_string]([parameters],N'%CommandWithParameters%')
)

And this is the json

{
    "Target": {
        "ServerName": "$ServerName",
        "SessionName": "LinkedServer_Outgoing",
        "FailOnProcessingError": false,
        "Responses": [
            {
                "__type": "GroupedTableAppenderResponse",
                "ServerName": "localhost",
                "DatabaseName": "tempdb",
                "TableName": "LinkedServer_OutgoingAudit",
                "AutoCreateTargetTable": false,
                "OutputColumns": [
                    "server_instance_name",
                    "client_app_name",
                    "client_hostname",
                    "server_principal_name",
                    "database_name",
                    "query_hash_signed",
                    "linked_server_name",
                    "provider_name",
                    "is_system",
                    "COUNT(collection_time) AS count_occurance",
                    "MAX(collection_time) AS last_occurance"
                ],
                "Events": [
                    "oledb_query_interface"
                ],
                "Filter": "query_hash_signed <> 0 OR is_system = 'True'"
            },
            {
                "__type": "GroupedTableAppenderResponse",
                "ServerName": "localhost",
                "DatabaseName": "tempdb",
                "TableName": "LinkedServer_OutgoingAudit",
                "AutoCreateTargetTable": false,
                "OutputColumns": [
                    "server_instance_name",
                    "client_app_name",
                    "client_hostname",
                    "server_principal_name",
                    "database_name",
                    "query_hash_signed",
                    "linked_server_name",
                    "provider_name",
                    "is_system",
                    "sql_text",
                    "COUNT(collection_time) AS count_occurance",
                    "MAX(collection_time) AS last_occurance"
                ],
                "Events": [
                    "oledb_query_interface"
                ],
                "Filter": "query_hash_signed = 0 AND is_system = 'False'" 
            }
        ]
    }
}

And the table definition

DROP TABLE IF EXISTS dbo.LinkedServer_OutgoingAudit
CREATE TABLE dbo.LinkedServer_OutgoingAudit
(
	server_instance_name sysname NOT NULL
	, client_app_name nvarchar(100) NOT NULL
	, client_hostname nvarchar(50) NOT NULL
	, server_principal_name sysname NOT NULL
	, database_name sysname NOT NULL
	, query_hash_signed bigint NULL
	, linked_server_name sysname NOT NULL
	, provider_name varchar(30) NOT NULL
	, sql_text nvarchar(max) NULL
	--, tsql_stack xml NULL /* xml is not supported  */
	--, tsql_stack nvarchar(max) NULL /* nvarchar is supported, but the stack uses handle, which is unique  */
	, count_occurance bigint NOT NULL
	, is_system bit NULL
	, last_occurance datetime2(3) NULL
)

I've tried to use the is_system = 'False' to get around the sql_text missing in "system" events

[spaghettidba]

Huh, sorry, which version?

[zikato]

According to GitHub - Latest 1.4.5 (downloaded yesterday).
Even though I'm not sure how to check it on the installed version

[spaghettidba]

Ah, gotcha. So it didn't work in 1.4.3 but it works in 1.4.5.
Thanks for confirming.

[zikato]

Sorry, it was probably confusing.
I only had the 1.4.5 version.

Problem 1 - I didn't realize filtering requires the column to also be on the output (that was mistake on my part)
Problem 2 - The missing sql_text. Non-system XE rows have that column and system XE rows don't have it (the shape of the events table changes based on that).

I thought maybe doing filter on the is_system and splitting it into two responses could get around that. But the events table check is probably before the filter check. It still fails.

The only workaround I can think of is to split my Extended event into two session (one for system and one without)

[zikato]

Ok, I had another wrong assumption.
is_system show either True or False in the SSMS or export, but it really is 1 or 0 respectively.

Updating my filter to is_system = 0 helped.

I think the issue can be closed as there is a workaround by using several responses with the relevant filter.

[spaghettidba]

Awesome, thanks for the feedback.
I guess I have to document the limitations you ran into. I'll do that ASAP

[zikato]

Update: Sadly it's not fixed for me. For some events, the sql_text is just not collected and I can't see any pattern explaining why.

You said the data type is based on the underlying field.
Would it be possible to always map the global fields? They should have static data types and when the underlying data isn't there a NULL can be passed.