Custom ODBC/SQL Table Monitor

[IndoMie]

Monitoring a single column and row in SQL query is far from enough and effective.

Being able to run a query for multiple columns with one or multiple row result, it would be much better.

There are plenty of objectives for this purpose, in fact this seems to be already available within AppInsight, we just need to make it available for custom table.

[wildersa]

These functionlaity will help me a lot, meanwhile, I found a workaround:

1. I created a "SQL Server User Experience Monitor" with a query, converting the table result to an XML;

2. I created a report converting this XML back in a table.

It's working fine for me.

Example:

Monitor query with table as xml

SELECT '1' test,

(SELECT DISTINCT
volume_mount_point [DiskMountPoint],
file_system_type [FileSystemType],
logical_volume_name as [LogicalDriveName],
CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [TotalSizeinGB],
CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [AvailableSizeinGB],
CAST(CAST(available_bytes as float)/ CAST(total_bytes as float) * 100 as nvarchar) AS [Space]
FROM sys.master_files
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)
FOR XML PATH('')) as result

Query to convert XML back to table:

DECLARE @Xml AS xml

SELECT @Xml =
'<root>'+REPLACE(REPLACE(ErrorMessage, '<DiskMountPoint>', '<Disk><NodeID>'+CAST(NodeID as nvarchar)+'</NodeID><DiskMountPoint>'), '</Space>', '</Space></Disk>')+'</root>'
FROM [APM_CurrentStatistics] (NOLOCK)
WHERE
ApplicationID = '2657'

SELECT
doc.col.value('NodeID[1]', 'NVARCHAR(50)') NodeID,
doc.col.value('DiskMountPoint[1]', 'NVARCHAR(50)') DiskMountPoint,
doc.col.value('FileSystemType[1]', 'NVARCHAR(50)') FileSystemType,
doc.col.value('LogicalDriveName[1]', 'NVARCHAR(50)') LogicalDriveName,
doc.col.value('TotalSizeinGB[1]', 'NVARCHAR(50)') TotalSizeinGB,
doc.col.value('AvailableSizeinGB[1]', 'NVARCHAR(50)') AvailableSizeinGB
FROM @xml.nodes('/root/Disk') doc(col)