→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jeffza
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
jeffza
Engaged Sweeper
Thank you, I'll give this a go
Daniel_B
Lansweeper Alumni
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