Closed lalit-satapathy closed 1 year ago
The short-term solution to this would be to add an optional input in transaction_log for users to enter a customer DB name. The metrics can be extracted for this customer DB along with all system DBs. CC @ManojS-shetty
Hi @lalit-satapathy , I am interested in the short term solution you describe. Do you have any details on how we can add an optional input to the transaction_log so we can capture our custom databases? Thanks.
Hi @lalit-satapathy , I am interested in the short term solution you describe. Do you have any details on how we can add an optional input to the transaction_log so we can capture our custom databases? Thanks.
HI @VF-JustinMolnar We would like to inform you that as Lalit suggested we will be providing this optional input in transaction_log as a part of package release.
We will be updating the progress of the development and testing on this issue thread.
Thank you
Hi @lalit-satapathy for me it's a bug compared to Metricbeat capabilities , user called it a regression. Why on that point, Metricbeat retrieves metrics for all DBs and Elastic-Agent only system DBs ? Additional input , even on short term, does not make sense because it supposes to enter manually each DB name. But the idea is also to monitor Customer DBs for which the DBA or infra team would not be aware of.
Hi @Danouchka,
This is only a "short term solution" as mentioned in here. The long-term solution is TBD.
Hi, similar to metricbeat, I am working on retrieving metrics for all the DBs using agent as well. Will be updating the progress here.
Short-term solution where user database can be manually provided is done. https://github.com/elastic/integrations/pull/4869.
@ritalwar , lets update the latest on this issue.
Working on long term solution approach where we can get all user DBs metrics for transaction log. Able to resolve it for sys.dm_db_log_stats table data and able to get data for all the DBs. Trying to find a way to get all Dbs data from sys.dm_db_log_space_usage table.
Considering a strategy where a first integration can be added to obtain a list of all databases. The user can then edit the integration to add database names from that list and obtain the corresponding metrics.
Hi
In that case, when you retrieve the list of all database, you have also to get some useful information (asked by customer DBA) such as if they are online or not, their file path, the occupied size of the file etc.. I had written an ER for that here https://github.com/elastic/integrations/issues/4260
But why not retrieving all of databases metrics ? For instance, When you retrieve metrics from GCE Compute and the customer has thousands of VM, we dont enter the hostnames we want to monitor one by one in the GCP integration So why would it be different approach here ?
Thanks, Dan
Dan Abitbol Solutions Architect SEMEA, elastic.co Search. Observe. Protect. +33662168617

Le 8 févr. 2023 à 06:06, Richa Talwar @.***> a écrit :
Considering a strategy where a first integration can be added to obtain a list of all databases. The user can then edit the integration to add database names from that list and obtain the corresponding metrics.
— Reply to this email directly, view it on GitHub https://github.com/elastic/integrations/issues/4108#issuecomment-1422021270, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALSQI4XJ7WZFK63EY2SNT4DWWMSWXANCNFSM6AAAAAAQB6L4ZQ. You are receiving this because you were mentioned.
Hi
Your strategy may be good but please
1/ explain why it's different from GCP compute metrics 2/ do you agree then that we have to implement #4260 before
Thank you
Hi,
I'm not certain about the comparison, so currently looking into the other possibilities as well.
Additionally, as mentioned earlier, trying to find a means to collect data for all Dbs from the sys.dm_db_log_space_usage table, making some headway with the use of the "DBCC SQLPERF (LOGSPACE)" query, which is giving Log Size (MB) and Log Space Used (%) data for all the dbs.
Trying to further get total_log_size_in_bytes and log_space_in_bytes_since_last_backup data for all databases.
Hi @ritalwar
Please combine your query with the latest one mentionned in https://github.com/elastic/integrations/issues/4260 As we need those information (path to filename, online status) as well. Thank you
For reminder, The query mentionned in https://github.com/elastic/integrations/issues/4260. (just one issue with this one below, SpaceUsed is null for user DBs)
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);
Hello
Regarding latest elastic-agent integration "Microsoft SQL Server" , version 1.13.0, we have still issues. Although we can add user databases, some metrics are not retrieved well regarding the transaction log Elastic agent is 8.6.1
In elastic-integration, I added the 2 following user DBs
I have 2 issues regarding transaction log metrics
a) I have part of the metrics for Microsoft.eShopOnWeb.Identity. You can compare with metricbeat.
Elastic agent integration
Metricbeat mssql module
b) I have data only for Microsoft.eShopOnWeb.Identity and not Microsoft.eShopOnWeb.CatalogDb
Elastic agent integration
Metricbeat mssql module
c) In Elastic Agent integration, I have the following error
[elastic_agent.metricbeat][error] Error fetching data for metricset sql.query: fetch table mode failed: mssql: Database ‘Microsoft’ does not exist. Make sure that the name is entered correctly.
Does elastic agent have issues with User DB dotted names ?
Elastic Agent diagnostics available in MP
My integration settings cc @lalit-satapathy
Separate issue filed to support user dbs with special characters. Please track here: https://github.com/elastic/integrations/issues/5528
@ritalwar , can we close this too?
We currently support user databases in such a way that customers can add a list of user databases to observe performance and transaction-log metrics. However, it would take some time to support all user databases by default; @lalit-satapathy can we create a separate issue to provide that support, or should we keep this issue open and decide priority for this in 8.9?
Hi @ritalwar
Please combine your query with the latest one mentionned in #4260 As we need those information (path to filename, online status) as well. Thank you
For reminder, The query mentionned in #4260. (just one issue with this one below, SpaceUsed is null for user DBs)
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);
For user database(s), querying from sys.master_files
and getting NULL
for the concerned filled seems expected. From the documentation of sys.master_files
:
Contains a row per file of a database as stored in the master database.
So it does not say user databases and that's why I think you are getting NULL
. To get the relevant info I think you have to use sys.database_files
instead.
Example:
cc: @Danouchka
Hi @shmsr I will give a try thank you ! and let you know
I get this below. Strange, I get some NULL regardless using sys.database_files or sys.master_files.
sqlcmd -S localhost -U SA -P sqlMS200675# -Q "SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0 AS FreeSpaceMB, State_desc as State_desc, physical_name as physical_name, CAST(max_size AS bigint)/128.0 AS max_file_size_mb, (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0)*100./(size/128.0 ) as percentfree FROM sys.master_files WHERE type_desc IN ('ROWS','LOG') ;"
DbName FileName type_desc CurrentSizeMB FreeSpaceMB State_desc physical_name max_file_size_mb percentfree
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------- ----------------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- ----------------------------------------
master master ROWS 4.437500 .812500 ONLINE /var/opt/mssql/data/master.mdf -.007812 18.3098591
master mastlog LOG 2.000000 1.351563 ONLINE /var/opt/mssql/data/mastlog.ldf -.007812 67.5781500
master tempdev ROWS 8.000000 NULL ONLINE /var/opt/mssql/data/tempdb.mdf -.007812 NULL
master templog LOG 8.000000 NULL ONLINE /var/opt/mssql/data/templog.ldf -.007812 NULL
master tempdev2 ROWS 8.000000 NULL ONLINE /var/opt/mssql/data/tempdb2.ndf -.007812 NULL
master tempdev3 ROWS 8.000000 NULL ONLINE /var/opt/mssql/data/tempdb3.ndf -.007812 NULL
master tempdev4 ROWS 8.000000 NULL ONLINE /var/opt/mssql/data/tempdb4.ndf -.007812 NULL
master modeldev ROWS 8.000000 NULL ONLINE /var/opt/mssql/data/model.mdf -.007812 NULL
master modellog LOG 8.000000 NULL ONLINE /var/opt/mssql/data/modellog.ldf -.007812 NULL
master MSDBData ROWS 13.437500 NULL ONLINE /var/opt/mssql/data/MSDBData.mdf -.007812 NULL
master MSDBLog LOG .500000 NULL ONLINE /var/opt/mssql/data/MSDBLog.ldf 2097152.000000 NULL
master Microsoft.eShopOnWeb.CatalogDb ROWS 8.000000 NULL ONLINE /var/opt/mssql/data/Microsoft.eShopOnWeb.CatalogDb.mdf -.007812 NULL
master Microsoft.eShopOnWeb.CatalogDb_log LOG 8.000000 NULL ONLINE /var/opt/mssql/data/Microsoft.eShopOnWeb.CatalogDb_log.ldf 2097152.000000 NULL
master Microsoft.eShopOnWeb.Identity ROWS 8.000000 NULL ONLINE /var/opt/mssql/data/Microsoft.eShopOnWeb.Identity.mdf -.007812 NULL
master Microsoft.eShopOnWeb.Identity_log LOG 8.000000 NULL ONLINE /var/opt/mssql/data/Microsoft.eShopOnWeb.Identity_log.ldf 2097152.000000 NULL
(15 rows affected)
[root@sa-da-webserver ~]# sqlcmd -S localhost -U SA -P sqlMS200675# -Q "SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0 AS FreeSpaceMB, State_desc as State_desc, physical_name as physical_name, CAST(max_size AS bigint)/128.0 AS max_file_size_mb, (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0)*100./(size/128.0 ) as percentfree FROM sys.database_files WHERE type_desc IN ('ROWS','LOG') ;"
DbName FileName type_desc CurrentSizeMB FreeSpaceMB State_desc physical_name max_file_size_mb percentfree
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------- ----------------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- ----------------------------------------
master master ROWS 4.437500 .812500 ONLINE /var/opt/mssql/data/master.mdf -.007812 18.3098591
master mastlog LOG 2.000000 1.351563 ONLINE /var/opt/mssql/data/mastlog.ldf -.007812 67.5781500
(2 rows affected) [root@sa-da-webserver ~]#
@Danouchka Do you have any user-defined DB? I just see master. Also, with the second command, there are just 2 rows and that too of master and I do not any NULL
there. Do you mean something else?
Good Point ! Microsoft.eShopOnWeb.CatalogDb was supposed to be a user DB ...let me check how it was created !
To summarize, we already have a solution in place which enables users to manually feed the user databases along with the already set system databases (as default). But to close this issue we’d ideally want to have a solution that gets all accessible databases on a server.
I have a proposal that suggests a simple approach to how we can achieve the same instead of introducing SQL chaining similar to that of HTTP JSON chaining in beats. Implementation is quite simple and it'd help us close this issue with a solid and simple solution without affecting older functionality.
Even if we introduce SQL chaining which will introduce some kind of chaining where we use the response of one query into another, I hardly have any use case that cannot be achieved with a simpler alternative. Considering, integrations only fetch metrics, logs, etc. (for instrumentation/ observability needs) from the databases; the requirement of some kind of chaining seems unnecessary. If someone has a solid use case, I am all ears. In case, we have solid use cases then we'll definitely explore adding some kind of chaining.
Proposal: Recommended approach
Fetch
and names of the databases are collected and then given queries are executed for each and every database.Please see https://github.com/elastic/beats/pull/35688 for more details.
The feature requested is released in the 2.2.0 version of Microsoft SQL Server. For more info see: https://github.com/elastic/integrations/pull/7500
Please ensure to use the 8.10 stack or above in order to use the fetch_from_all_databases
feature.
The MSSQL Integration currently loads "transaction_log metrics" from following dbs:
The integration code should be updated to load from all the dbs.