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?

 

EDIT:

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


**EDIT2**

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')
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • You will probably need the exact title of the SQL software, if possible.
    • There are multiple versions/years, and I want to get all of them consolidated to one report
    • Disregarding SQL, how can I return just the software title and version that I am looking for? I can filter, but that doesn't remove all of the other software, it just filters to include a machine with that software installed.
      • Here's a post where someone was looking to accomplish the same thing but with Office:
        http://www.itninja.com/question/ms-office-version-with-sp-reporting
    • Thanks for the assistance! That article helped me get rolling and now I just have to change one thing. I can't return the Core Count as a simple field because it contains the entire processor information. I have a CIF that returns Core Count with WMIC but now I need to incorporate that into my SQL query...I'll paste the query I have into the question as an edit.
      • Do you have the core count in a custom inventory rule? If so then you can join to the MACHINE_CUSTOM_INVENTORY with the following statement:

        JOIN MACHINE_CUSTOM_INVENTORY MCI ON MCI.ID = MACHINE.ID and MCI.SOFTWARE_ID = <software_id>

        where <software_id> is the software ID of the custom inventory rule.

        If you don't have a custom inventory rule setup then the following SQL pulls just the CPU Core Count from the processors column:

        SUBSTRING_INDEX(SUBSTRING_INDEX(PROCESSORS, "CPU Core Count:", -1), "CPU0", 1) AS "Core Count"

        Note this only works for machines with one processor, it basically grabs the text from the PROCESSORS column between the first instance of "CPU CORE COUNT:" and "CPU0".
Please log in to comment

Answers

0

With the help of chucksteel (http://www.itninja.com/user/chucksteel), I came up with the following query to accomplish my goal.

 

SELECT 

    MACHINE.NAME AS 'System Name',

    A42.NAME AS 'Environment',

    ASSET_DATA_5.FIELD_23 AS 'Pysical or Virtual',

    OS_NAME AS 'Operating System',

    SUBSTRING_INDEX(SUBSTRING_INDEX(PROCESSORS, 'CPU Core Count:', - 1),

            'CPU0',

            1) AS 'Core Count',

    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')

Answered 10/15/2013 by: easterdaymatt
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity