none
SCOM - Extract Selected columns from Discovered Inventory RRS feed

  • Question

  • Hi Pros,

    I have been given a task to compile the following for SQL Server

    ServerNames, InstanceNames,  IsClustered, Version, LogicalProcessors, PhysicalProcessors, IsVirtual.

    I'm new to SCOM and have tried exhaustively since yesterday morning to come up with this, so far no luck.

    Here's the command that I have compiled but it is not returning IntanceName, ConnectionString, Version, and Edition columns into CSV. All other columns are returned fine. I would appreciate if someone can help me with this, also if you can tell me how I can order it by Server Names Ascending. Also I'm not sure what command to use for IsClustered?

    Get-SCOMClass –Name Microsoft.Windows.Computer | Get-SCOMClassInstance | Select @{Label="Computer";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosComputerName'}},
     @{Label="Instance";Expression= {$_.'[Microsoft.SQLServer.ServerRole].InstanceName'}}, @{Label="ConnectionString";Expression= {$_.'[Microsoft.SQLServer.DBEngine].ConnectionString'}}, @{Label="Version";Expression= {$_.'[Microsoft.SQLServer.DBEngine].Version'}}, @{Label="Edition";Expression= {$_.'[Microsoft.SQLServer.DBEngine].Edition'}}, @{Label="DomainName";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosDomainName'}}, @{Label="VirtualMachine?";Expression= {$_.'[Microsoft.Windows.Computer].IsVirtualMachine'}}, @{Label="AD Site";Expression= {$_.'[Microsoft.Windows.Computer].ActiveDirectorySite'}},@{Label="LogicalProcessors";Expression= {$_.'[Microsoft.Windows.Computer].LogicalProcessors'}}, @{Label="PhysicalProcessor";Expression= {$_.'[Microsoft.Windows.Computer].PhysicalProcessors'}} | export-csv D:\temp\AAAA.csv

    I got the command from this source and I modified it according to my requirement.

    Source: http://www.bictt.com/blogs/bictt.php/2014/05/22/getting-sql-information-from-scom


    Wednesday, November 25, 2015 1:55 AM

Answers

  • shahid

    The modified version is

    difference: In previous powershell, the select statment missing displayname which i used to identify the windows server instance in which SQL server is installed (where-object{$_.displayname -eq $SQLDB.path})

    *****************************************

    $SQLDBs=get-scomclass -name "MIcrosoft.SQLServer.DBengine"|get-scomclassinstance
    foreach($SQLDB in $SQLDBs)
    {
    Get-SCOMClass –Name Microsoft.Windows.Computer | Get-SCOMClassInstance | Select displayname, @{Label="Computer";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosComputerName'}},
      @{Label="Instance";Expression= {$SQLDB.'[Microsoft.SQLServer.Serverrole].InstanceName'}}, @{Label="ConnectionString";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].ConnectionString'}}, @{Label="Version";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].Version'}}, @{Label="Edition";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].Edition'}}, @{Label="DomainName";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosDomainName'}}, @{Label="VirtualMachine?";Expression= {$_.'[Microsoft.Windows.Computer].IsVirtualMachine'}}, @{Label="AD Site";Expression= {$_.'[Microsoft.Windows.Computer].ActiveDirectorySite'}},@{Label="LogicalProcessors";Expression= {$_.'[Microsoft.Windows.Computer].LogicalProcessors'}}, @{Label="PhysicalProcessor";Expression= {$_.'[Microsoft.Windows.Computer].PhysicalProcessors'}} |where-object{$_.displayname -eq $SQLDB.path} |export-csv D:\temp\SqlInventroy.csv -append
    }

    ***********************************************

    roger

    • Marked as answer by Shaddy_1 Wednesday, December 2, 2015 8:30 PM
    Monday, November 30, 2015 7:36 AM

All replies

  • Hi, for sql server properties you should use sql server classes like

    Microsoft.SQLServer.DBEngine
    Microsoft.SQLServer.2012.DBEngine

    as example

    $insts=Get-SCOMClass -Name Microsoft.SQLServer.2012.DBEngine | Get-SCOMClassInstance $insts.'[Microsoft.SQLServer.DBEngine].Version'.value

    $insts.'[Microsoft.SQLServer.DBEngine].Cluster'.value




    Wednesday, November 25, 2015 11:18 AM
  • Hi Alexis,

    Thanks for the reply but after changing the class the Intance, ConnectionString, Version, and Edition columns are still not returning any results.


    Here's my powershell query

    Get-SCOMClass –Name Microsoft.Windows.Computer | Get-SCOMClassInstance | Select @{Label="Computer";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosComputerName'}},
     @{Label="Instance";Expression= {$_.'[Microsoft.SQLServer.DBEngine].InstanceName'}}, @{Label="ConnectionString";Expression= {$_.'[Microsoft.SQLServer.DBEngine].ConnectionString'}}, @{Label="Version";Expression= {$_.'[Microsoft.SQLServer.DBEngine].Version'}}, @{Label="Edition";Expression= {$_.'[Microsoft.SQLServer.DBEngine].Edition'}}, @{Label="DomainName";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosDomainName'}}, @{Label="VirtualMachine?";Expression= {$_.'[Microsoft.Windows.Computer].IsVirtualMachine'}}, @{Label="AD Site";Expression= {$_.'[Microsoft.Windows.Computer].ActiveDirectorySite'}},@{Label="LogicalProcessors";Expression= {$_.'[Microsoft.Windows.Computer].LogicalProcessors'}}, @{Label="PhysicalProcessor";Expression= {$_.'[Microsoft.Windows.Computer].PhysicalProcessors'}} | export-csv D:\temp\SqlInventroy.csv

    If you can please guide me where I'm wrong.

    Wednesday, November 25, 2015 8:24 PM
  • I modify your script as
    **************************************
    $SQLDBs=get-scomclass -displayname "SQL Server 2012 DB engine"|get-scomclassinstance
    foreach($SQLDB in $SQLDBs)
    {
    Get-SCOMClass –Name Microsoft.Windows.Computer | Get-SCOMClassInstance | Select @{Label="Computer";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosComputerName'}},
      @{Label="Instance";Expression= {$SQLDB.'[Microsoft.SQLServer.2012.Serverrole].InstanceName'}}, @{Label="ConnectionString";Expression= {$SQLDB.'[Microsoft.SQLServer.2012.DBEngine].ConnectionString'}}, @{Label="Version";Expression= {$SQLDB.'[Microsoft.SQLServer.2012.DBEngine].Version'}}, @{Label="Edition";Expression= {$SQLDB.'[Microsoft.SQLServer.2012.DBEngine].Edition'}}, @{Label="DomainName";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosDomainName'}}, @{Label="VirtualMachine?";Expression= {$_.'[Microsoft.Windows.Computer].IsVirtualMachine'}}, @{Label="AD Site";Expression= {$_.'[Microsoft.Windows.Computer].ActiveDirectorySite'}},@{Label="LogicalProcessors";Expression= {$_.'[Microsoft.Windows.Computer].LogicalProcessors'}}, @{Label="PhysicalProcessor";Expression= {$_.'[Microsoft.Windows.Computer].PhysicalProcessors'}} |where-object{$_.displayname -eq $SQLDB.path} |export-csv D:\temp\SqlInventroy.csv append
    }
    $SQLDBs=get-scomclass -displayname "SQL Server 2014 DB engine"|get-scomclassinstance
    foreach($SQLDB in $SQLDBs)
    {
    Get-SCOMClass –Name Microsoft.Windows.Computer | Get-SCOMClassInstance | Select @{Label="Computer";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosComputerName'}},
      @{Label="Instance";Expression= {$SQLDB.'[Microsoft.SQLServer.2014.Serverrole].InstanceName'}}, @{Label="ConnectionString";Expression= {$SQLDB.'[Microsoft.SQLServer.2014.DBEngine].ConnectionString'}}, @{Label="Version";Expression= {$SQLDB.'[Microsoft.SQLServer.2014.DBEngine].Version'}}, @{Label="Edition";Expression= {$SQLDB.'[Microsoft.SQLServer.2014.DBEngine].Edition'}}, @{Label="DomainName";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosDomainName'}}, @{Label="VirtualMachine?";Expression= {$_.'[Microsoft.Windows.Computer].IsVirtualMachine'}}, @{Label="AD Site";Expression= {$_.'[Microsoft.Windows.Computer].ActiveDirectorySite'}},@{Label="LogicalProcessors";Expression= {$_.'[Microsoft.Windows.Computer].LogicalProcessors'}}, @{Label="PhysicalProcessor";Expression= {$_.'[Microsoft.Windows.Computer].PhysicalProcessors'}} |where-object{$_.displayname -eq $SQLDB.path} |export-csv D:\temp\SqlInventroy.csv append
    }
    **************************************
    Roger
    Thursday, November 26, 2015 5:09 AM
  • Hi Roger,

    Thanks for editing the script but I'm having trouble when I execute this. I pasted the above into powershell the whole thing  and using two liner. The two line returns no error but it return an empty excel file. I created the script and executed it, but still returns error. Sorry I'm not very fluent in powershell. 

    Any suggestion? I have even tried removing the append word but still the output is empty file.

    


    • Edited by Shaddy_1 Thursday, November 26, 2015 7:37 PM
    Thursday, November 26, 2015 7:33 PM
  • Just realized it was missing minus sign before the word append after adding minus sign it works but the output file is still empty.
    • Edited by Shaddy_1 Thursday, November 26, 2015 8:14 PM
    Thursday, November 26, 2015 8:13 PM
  • Hi, what version of SQL server do you use?

    as I said you had to find neccesary class first

    try 

    get-scomclass -name *dbengine* | select name
    then

    $insts=Get-SCOMClass -Name "ClassName" | Get-SCOMClassInstance

    then

    $insts[0] | select *

    to see all properties

     



    Thursday, November 26, 2015 10:24 PM
  • Hi Alexis,

    Thanks for the explanation, it clears up some confusion. So how can I pull the information from

    Microsoft.Windows.Computer
    Microsoft.SQLServer.DBEngine 

    classes as I'm trying to do above into one csv file? If I pull the information separately it worked fine and I got two different files, but I have to manually match the rows which is a big job. It would be nice if I can pull all information with one query into a csv file from both classes. Is there a way to join them? 

    Thanks


    • Edited by Shaddy_1 Thursday, November 26, 2015 11:24 PM
    Thursday, November 26, 2015 11:16 PM
  • You can pull information from

    Microsoft.SQLServer.DBengine

    so try to run the powershell first before running th following powershell to extract relevance information

    get-scomclass -name "Microsoft.SQLServer.DBengine" | get-scomclassinstance | select ``[Microsoft.SQLServer.Serverrole`].InstanceName, ``[Microsoft.SQLServer.DBEngine`].ConnectionString, ``[Microsoft.SQLServer.DBEngine`].Version, ``[Microsoft.SQLServer.DBEngine`].Edition

    If the above powershell return data, then you can run the following powershell

    *****************************************

    $SQLDBs=get-scomclass -name "MIcrosoft.SQLServer.DBengine"|get-scomclassinstance
    foreach($SQLDB in $SQLDBs)
    {
    Get-SCOMClass –Name Microsoft.Windows.Computer | Get-SCOMClassInstance | Select @{Label="Computer";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosComputerName'}},
      @{Label="Instance";Expression= {$SQLDB.'[Microsoft.SQLServer.Serverrole].InstanceName'}}, @{Label="ConnectionString";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].ConnectionString'}}, @{Label="Version";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].Version'}}, @{Label="Edition";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].Edition'}}, @{Label="DomainName";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosDomainName'}}, @{Label="VirtualMachine?";Expression= {$_.'[Microsoft.Windows.Computer].IsVirtualMachine'}}, @{Label="AD Site";Expression= {$_.'[Microsoft.Windows.Computer].ActiveDirectorySite'}},@{Label="LogicalProcessors";Expression= {$_.'[Microsoft.Windows.Computer].LogicalProcessors'}}, @{Label="PhysicalProcessor";Expression= {$_.'[Microsoft.Windows.Computer].PhysicalProcessors'}} |where-object{$_.displayname -eq $SQLDB.path} |export-csv D:\temp\SqlInventroy.csv -append
    }

    ***********************************************

    Script explaination;

    1) we need to know which class holding the SQL server , depend on which SQL MP version you imported

    2) Also the following fields of SQLServer.DBEngine class are required

    [Microsoft.SQLServer.Serverrole].InstanceName, [Microsoft.SQLServer.DBEngine].ConnectionString, [Microsoft.SQLServer.DBEngine].Version, [Microsoft.SQLServer.DBEngine].Edition

    3) If you can pull information

    get-scomclass -name "Microsoft.SQLServer.DBengine" | get-scomclassinstance

    runn this powershell to pull information

    get-scomclass -name "Microsoft.SQLServer.DBengine" | get-scomclassinstance | select ``[Microsoft.SQLServer.Serverrole`].InstanceName, ``[Microsoft.SQLServer.DBEngine`].ConnectionString, ``[Microsoft.SQLServer.DBEngine`].Version, ``[Microsoft.SQLServer.DBEngine`].Edition

    if failure, run

    get-scomclass -name "Microsoft.SQLServer.DBengine" | get-scomclassinstance | fl > d:\temp\temp.txt

    view the output and find out the required fieldname

    4) modify the field name of the above script to fit your situation

    Roger

    Friday, November 27, 2015 4:06 AM
  • Roger,

    First of all thank you for spending time to help me. As per your instruction I ran the following:

    so try to run the powershell first before running th following powershell to extract relevance information

    get-scomclass -name "Microsoft.SQLServer.DBengine" | get-scomclassinstance | select ``[Microsoft.SQLServer.Serverrole`].InstanceName, ``[Microsoft.SQLServer.DBEngine`].ConnectionString, ``[Microsoft.SQLServer.DBEngine`].Version, ``[Microsoft.SQLServer.DBEngine`].Edition

    It did return the result as following, sorry I had to hide some information.

    But when i run the second part, it runs for almost two minutes and created the cvs file but with no information whatsoever. The file returned is empty. 

    Also thanks for explaining the classes, I'm certain now that the information I need is contained in  Microsoft.SQLServer.DBengine and Microsoft.Windows.Computer classes.

    Best,

    Shahid

    Friday, November 27, 2015 10:02 PM
  • shahid

    The modified version is

    difference: In previous powershell, the select statment missing displayname which i used to identify the windows server instance in which SQL server is installed (where-object{$_.displayname -eq $SQLDB.path})

    *****************************************

    $SQLDBs=get-scomclass -name "MIcrosoft.SQLServer.DBengine"|get-scomclassinstance
    foreach($SQLDB in $SQLDBs)
    {
    Get-SCOMClass –Name Microsoft.Windows.Computer | Get-SCOMClassInstance | Select displayname, @{Label="Computer";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosComputerName'}},
      @{Label="Instance";Expression= {$SQLDB.'[Microsoft.SQLServer.Serverrole].InstanceName'}}, @{Label="ConnectionString";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].ConnectionString'}}, @{Label="Version";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].Version'}}, @{Label="Edition";Expression= {$SQLDB.'[Microsoft.SQLServer.DBEngine].Edition'}}, @{Label="DomainName";Expression= {$_.'[Microsoft.Windows.Computer].NetbiosDomainName'}}, @{Label="VirtualMachine?";Expression= {$_.'[Microsoft.Windows.Computer].IsVirtualMachine'}}, @{Label="AD Site";Expression= {$_.'[Microsoft.Windows.Computer].ActiveDirectorySite'}},@{Label="LogicalProcessors";Expression= {$_.'[Microsoft.Windows.Computer].LogicalProcessors'}}, @{Label="PhysicalProcessor";Expression= {$_.'[Microsoft.Windows.Computer].PhysicalProcessors'}} |where-object{$_.displayname -eq $SQLDB.path} |export-csv D:\temp\SqlInventroy.csv -append
    }

    ***********************************************

    roger

    • Marked as answer by Shaddy_1 Wednesday, December 2, 2015 8:30 PM
    Monday, November 30, 2015 7:36 AM
  • Rogers,

    Thanks a lot, this works fine now. You help is appreciated.

    Wednesday, December 2, 2015 8:31 PM