Elastic-Agent: Microsoft SQL Server Integration (or other) - Needed information per Database

[Danouchka]

Hello

Discussing with MSSQL DBA, would be very important to have out of the box, the following metrics (per named instance and per database - both user-defined databases and systems) :

• SQL named instance
• Database Name
• Database filename (path to mdf and ldf files)
• Database online/offline status
• Database file Size in MB
• Database Empty Space in MB
• Database used space percentage

Although those metrics would be available using metricbeat SQL Module, some of them would need to list the databases present in a named instance, and then for each query its status for example. More over, the SQL module cannot chained SQL requests

For instance, those metrics are very important to make sur a database was disconnected for maintenance operations, or to make sur of the allocated file space, to be warned when the file space is missing and anticipates issues

those results can be part of another metricset similarly to "Performance" or "Transaction Logs" metrics

Thank you

[elasticmachine]

Pinging @elastic/elastic-agent (Team:Elastic-Agent)

[Danouchka]

I tried this on my MSSQL server but my requests are not perfect though. Something to study more

For db files sqlcmd -S localhost -U SA -P sqlMS200675# -Q "SELECT name, size/128.0 FileSizeInMB,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS EmptySpaceInMB FROM sys.master_files; " name FileSizeInMB EmptySpaceInMB

---

master 4.437500 .812500 mastlog 2.000000 1.132813 tempdev 8.000000 NULL templog 8.000000 NULL tempdev2 8.000000 NULL tempdev3 8.000000 NULL tempdev4 8.000000 NULL modeldev 8.000000 NULL modellog 8.000000 NULL MSDBData 13.437500 NULL MSDBLog .500000 NULL Microsoft.eShopOnWeb.CatalogDb 8.000000 NULL Microsoft.eShopOnWeb.CatalogDb_log 8.000000 NULL Microsoft.eShopOnWeb.Identity 8.000000 NULL Microsoft.eShopOnWeb.Identity_log 8.000000 NULL

sqlcmd -S localhost -U SA -P sqlMS200675# -Q " select db_name(dbid) dbname, filename from sys.sysaltfiles" dbname filename

---

master /var/opt/mssql/data/master.mdf master /var/opt/mssql/data/mastlog.ldf tempdb /var/opt/mssql/data/tempdb.mdf tempdb /var/opt/mssql/data/templog.ldf tempdb /var/opt/mssql/data/tempdb2.ndf tempdb /var/opt/mssql/data/tempdb3.ndf tempdb /var/opt/mssql/data/tempdb4.ndf model /var/opt/mssql/data/model.mdf model /var/opt/mssql/data/modellog.ldf msdb /var/opt/mssql/data/MSDBData.mdf msdb /var/opt/mssql/data/MSDBLog.ldf Microsoft.eShopOnWeb.CatalogDb /var/opt/mssql/data/Microsoft.eShopOnWeb.CatalogDb.mdf Microsoft.eShopOnWeb.CatalogDb /var/opt/mssql/data/Microsoft.eShopOnWeb.CatalogDb_log.ldf Microsoft.eShopOnWeb.Identity /var/opt/mssql/data/Microsoft.eShopOnWeb.Identity.mdf Microsoft.eShopOnWeb.Identity /var/opt/mssql/data/Microsoft.eShopOnWeb.Identity_log.ldf NULL mssqlsystemresource.mdf NULL mssqlsystemresource.ldf

I dont have request for testing if a DB is online or not but this important as well

[lalit-satapathy]

@SubhrataK Can you help close the specific query and let's know if the query is ready to use.

[Danouchka]

Hi I have asked at the same time a DBA the corresponding SQL requests because above are not comlpete. WIll keep you updated

[Danouchka]

Hello I got the following SQL request from customer DBA It works as expected, the only caveat is that for user DB, the returned FreeSpaceMB value is NULL. Asking the DBA how to correct.

SELECT DB_NAME() AS DbName,
        name AS FileName,
       type_desc,
        size/128.0 AS CurrentSizeMB, 
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
        State_desc as State_desc,
        physical_name as physical_name,
        (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)*100./(size/128.0 ) as percentfree
FROM sys.master_files WHERE type IN (0,1); 

[botelastic[bot]]

Hi! We just realized that we haven't looked into this issue in a while. We're sorry! We're labeling this issue as Stale to make it hit our filters and make sure we get back to it as soon as possible. In the meantime, it'd be extremely helpful if you could take a look at it as well and confirm its relevance. A simple comment with a nice emoji will be enough :+1. Thank you for your contribution!