Software: ALL SQL Server Editions Report

[jeffza]

Hi there

I need to report on all SQL server installs for a Microsoft audit. I am having an issue where some SQL Server installations are not showing on the "Software: All SQL Server Editions" report but they are showing on the "Role: SQL Server" report.

I have checked and the servers being left out are scanned and SQL is being picked up under the software tab for each individual server.

Can you help me with a way to consolidate both the "Role" and the "Software" reports so I can be sure to pick up all SQL installations on my network? I can't just rely on the "Role" report as I need to include DBA PC's that have SQL installations as well.

Thank you

[jeffza]

Thank you, I'll give this a go

[Daniel_B · Accepted solution]

The three reports that you mention are not related to each other, as the information is being pulled from completely different sources.

- The installed Software equals the Add or remove programs list on the client computer

- The report "Role: SQL Server" uses data pulled from a WMI class called Win32_ComputerSystem

- The report "Software: All SQL Server Editions" uses data pulled from another WMI class which is specific for SQL servers. It is a known issue that this WMI class sometimes does not return data.

What you could do to fix this issue is recompile the WMI class that stores the SQL databases on the machines which host the SQL server, but do not appear on the report. For detailed information on how to do this, please refer to the following KB article: http://www.lansweeper.com/kb/37/SQL-server-not-detected.html

To further troubleshoot these kind of issues, the tool WMIDiag from Microsoft can be helpful as well.

After that could you rescan these machines again (click on Assets, select the affected machines and hit "Rescan").



If you just would like to have a list of SQL server installations, you could get this also by filtering the software report to show your SQL servers only. A report like this is likely to contain SQL components as well however:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblSoftwareUni.softwareName Like '%microsoft sql server%' And
  tblSoftwareUni.SoftwarePublisher Like N'%microsoft%' And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Software