Systems Management Question
Reporting Help - Software
I want a report that shows some fields about our SQL servers. OS, Core Count, Physical or Virtual, and SQL version. When I run the report I created with the wizard it shows me all that I need, but when I chose Software and filtered by "Contains SQL" It listed all of the software installed and also the versions of all of that software. I just want to show the SQL information within that column for each server...Thoughts?
For clarification, here is what I'm looking for...
Excel file with the following columns:
Server Name | OS | Core Count | Physical or Virtual (asset) | Environment (asset) | SQL Version Installed
Here is my SQL query thus far. I need to alter the "PROCESSORS" table info to contain ONLY the core count. I created a Custom Inventory Rule (that isn't perfect yet) that uses WMIC to return what I need, but I need to get that into the SQL query...Unless someone can tell me how to parse out only the core count from KACE and not the rest of the processor info...
SELECT MACHINE.NAME AS 'System Name', A42.NAME AS 'Environment', ASSET_DATA_5.FIELD_23 AS 'Pysical or Virtual', OS_NAME AS 'Operating System', PROCESSORS AS 'Processors', SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.NAME AS 'SQL Version Installed' FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID = 5 LEFT JOIN ASSET_ASSOCIATION J42 ON J42.ASSET_ID = ASSET.ID AND J42.ASSET_FIELD_ID = 42 LEFT JOIN ASSET A42 ON A42.ID = J42.ASSOCIATED_ASSET_ID LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID LEFT join SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE ON MACHINE.ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.MACHINE_ID WHERE SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.NAME IN ( 'SQL Server 2005 Developer', 'SQL Server 2005 Compact Edition', 'SQL Server 2005 Enterprise','SQL Server 2005 Express Edition','SQL Server 2005 Standard','SQL Server 2008 Developer','SQL Server 2008 Enterprise','SQL Server 2008 R2 Developer','SQL Server 2008 R2 Enterprise','SQL Server 2008 R2 Express Edition','SQL Server 2008 R2 Standard','SQL Server 2008 Standard','SQL Server 2012 Enterprise','SQL Server 2012 Express Edition','SQL Server 2012 Standard')