Add collection time bucket to GroupedTableAppenderResponse

[zikato]

I'd like to trend aggregated data for insights over time.

Example:

XE definition

create event session wait_info on server
add event sqlos.wait_info
(
    where opcode = 'end'
)

json config (simplified)

{
    "Target": {
        "ServerName": "server",
        "SessionName": "wait_info ",
        "FailOnProcessingError": false,
        "Responses": [
            {
                "__type": "GroupedTableAppenderResponse",
                "ServerName": "localhost",
                "DatabaseName": "XeSmartTarget",
                "TableName": "WaitInfoHistogram",
                "AutoCreateTargetTable": true,
                "UploadIntervalSeconds": 10,
                "Events": [
                    "wait_info"
                ],
                "OutputColumns": [
                    "database_id",
                    "MAX(collection_time) AS max_collection_time", 
                    "SUM(duration) AS sum_duration_ms"
                ]
            }
        ]
    }
}

Current

It will group by database_id sum duration of waits. For each collection it will then merge with what's in the table so I get total waits per DB since the collection started (similar to how SQL Server tracks wait stats since last restart)

Ideal

Introduce a date bucket syntax for the collection_time column For example:

• DATE_BUCKET syntax from SQL Server 2022
• bin() from Kusto
• C# equivalent (first search result - https://stackoverflow.com/a/8856405/4609186)

So instead of collection_time , I'd have something like bin(collection_time, 5m) and have the aggregates in 5-minute intervals.

[zikato]

Another option: the UploadIntervalSeconds would be the bin size, and I could have the option not to merge with the target table. So it would still be grouped and aggregated within the XE Smart Target, but only appended to the target table.

[spaghettidba]

That's an interesting feature. I tried to achieve the same with SUBSTRING(CONVERT(collection_time,'System.String'),1,16) but I understand that this depends on the time format of the machine where XESmartTarget is running. The main problem is that the expressions are based on what the DataTable class can accept. It has its own syntax and there are things it can't do. I'll see if I can find a way. Thanks for filing this issue!

[zikato]

Thank you! In that case, I think my second post could be used as a workaround - appending to the target table instead of merging and using the Upload interval as the time bucket (along with max(collection_time))

[spaghettidba]

Sure, this can be done! Let me work out something and I'll get back to you