PowerShell - SQL Audit

PowerShell
, ,
1

Hi all,

I’m battling a bit with PowerShell… ?

I have written the following scripts – One for SQL 2012 and the other for SQL 2014

The bit I’m struggling with is to get the memory allocated to that server hosting the SQL instance

The memory can be called from the following class:-
get-scomclass -name ‘Microsoft.Windows.Server.OperatingSystem’

Script 1

$SQLDBs=get-scomclass -name “microsoft.sqlserver.2012.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=”IPAddress”;Expression= {$_.’[Microsoft.Windows.Computer].IPAddress’}},
@{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=”SPVersion”;Expression= {$SQLDB.’[[Microsoft.SQLServer.DBEngine].ServicePackVersion’}},
@{Label=”Edition”;Expression= {$SQLDB.’[Microsoft.SQLServer.DBEngine].Edition’}},
@{Label=”AuthMode”;Expression= {$SQLDB.’[Microsoft.SQLServer.DBEngine].AuthenticationMode’}},
@{Label=”Cluster”;Expression= {$SQLDB.’[Microsoft.SQLServer.DBEngine].Cluster’}},
@{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 C:\Users\schoemana\Desktop\SQL\SQLVersion\SQLServer2012DBEngineGroup.csv -append
}

Script 2

$SQLDBs=get-scomclass -name “microsoft.sqlserver.2014.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=”IPAddress”;Expression= {$_.’[Microsoft.Windows.Computer].IPAddress’}},
@{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=”SPVersion”;Expression= {$SQLDB.’[Microsoft.SQLServer.2014.DBEngine].ServicePackVersion’}},
@{Label=”AuthMode”;Expression= {$SQLDB.’[Microsoft.SQLServer.2014.DBEngine].AuthenticationMode’}},
@{Label=”Cluster”;Expression= {$SQLDB.’[Microsoft.SQLServer.2014.DBEngine].Cluster’}},
@{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 C:\Users\schoemana\Desktop\SQL\SQLVersion\SQLServer2014DBEngineGroup.csv -append
}
1 Like
2

Please provide a finished example if you don’t mind. :slight_smile:

3

The PhysicalMemory property is associated with the Operating System object. You could find this by doing:

Get-SCOMClass -DisplayName 'Windows Server Operating System' | Get-SCOMClassInstance | Where-Object {$_.Path -eq $SQLDB.Path} | Select @{Name="PhysicalMemory";Expression= {$_.'[Microsoft.Windows.OperatingSystem].PhysicalMemory'.Value}}
4

Updated your question to use the <> code sections.

5

It now displays the memory however by changing the class I have now lost other fields that was provided by the Microsoft.Windows.Computer class.

So basically I need the class information from

Microsoft.Sqlserver.Dbengine
Microsoft.Windows.Computer
Windows Server Operating System
Is the only way to keep nesting foreach loops and using get-scomclassinstance?

$SQLDBs=get-scomclass -name “microsoft.sqlserver.dbengine”| get-scomclassinstance
foreach($SQLDB in $SQLDBs)
{
Get-SCOMClass -DisplayName ‘Windows Server Operating System’ | Get-SCOMClassInstance | Where-Object {$.Path -eq $SQLDB.Path} | Select displayname,
@{Name=“PhysicalMemory”;Expression= {$.’[Microsoft.Windows.OperatingSystem].PhysicalMemory’.Value}},
@{Label=“Computer”;Expression= {$.’[Microsoft.Windows.Computer].NetbiosComputerName’}},
@{Label=“IPAddress”;Expression= {$.’[Microsoft.Windows.Computer].IPAddress’}},
@{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=“SPVersion”;Expression= {$SQLDB.’[Microsoft.SQLServer.DBEngine].ServicePackVersion’}},
@{Label=“AuthMode”;Expression= {$SQLDB.’[Microsoft.SQLServer.DBEngine].AuthenticationMode’}},
@{Label=“Cluster”;Expression= {$SQLDB.’[Microsoft.SQLServer.DBEngine].Cluster’}},
@{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 C:\SQLServer2012DBEngineGroupNew.csv -append
}