MSSQL DB discovery Issue - Servers are discover but No DBs getting discovered

[Gokul Explorer]

Executing WMI query on host: 10.1.75.XXX with namespace: root/cimv2 and query: SELECT name FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases
Error during execution of Windows command: executeQuery -Namespace root/cimv2 -Query "SELECT name FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases" due to com.snc.automation_common.integration.exceptions.InvalidCommandException: executeQuery : Could not get query SELECT name FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases on namespace=root/cimv2. Original Exception: Invalid query
At line:1 char:5

Overview:

In our environment, multiple database servers share identical access credentials, and no firewall restrictions block access or connections. The WMI port (135) is open on all servers. While Windows Server and Microsoft SQL Server instances are consistently discovered and updated, certain databases on specific servers are not being discovered.

Initial Findings:

During investigation, we identified that the SQL Server "Auto Close" feature was enabled for some databases. When enabled, this feature causes databases to shut down when not in use, preventing discovery. Disabling "Auto Close" resolved the discovery issue for those databases.

Persistence Issue:

However, for a subset of servers, databases are still not being discovered despite the following:

• "Auto Close" is confirmed to be disabled.

• Windows Server and SQL Server instance Configuration Items (CIs) are successfully discovered and updated.

• No access, credential, or firewall-related issues are present.

• WMI port (135) remains open.

Can anyone from the Community, help me with this ?

Your assistance will greatly appreciated!!

Regards,

Gokul Subramaniam

@sukran

@Sohail Khilji 

@AJ-TechTrek 

 

[Sohail Khilji]

Hi @Gokul Explorer,

Did you check your patterns for these issues ? any findings from patterns execution ?

WMI Class Availability – Ensure that the Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases class actually exists on the affected servers. You can verify this by running 
 
or 

if you're using a named SQL Server instance, the WMI class name might be different.

[AjinA]

If Auto Close is already disabled and you’ve confirmed credentials, ports, and firewall settings are fine, the issue might be related to WMI class availability on those servers. Sometimes the Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases class won’t return results if the SQL Server performance counters are corrupted or not registered. You can try rebuilding the SQL Server performance counters using lodctr /R and then restarting the WMI service. Also check if the instance name in your query matches exactly, especially for named instances. If you still can’t discover those databases, there’s a chance the system database or metadata is partially corrupted  in that case, a repair tool like Stellar Repair for MS SQL  can scan and restore them without data loss.

[sukran]

This is bug from ServiceNow PRB1859960 and they fixed in the recent visibility content plugin

Please upgrade the latest version of 6.28.0 . that could fix the issue

https://store.servicenow.com/store/app/4e996b221b246a50a85b16db234bcb4d

Note - As per ServiceNow , the fixes available in this patch but release notes doesn't have information

I cross verified with them and it have the fix solution