question

Shawn_Melton avatar image
Shawn_Melton asked

Pulling Instance name to pass to SMO object

OK, I have a headache so there is probably something I am missing here... I have this command: *Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement" -Class ServerSettings | Select InstanceName* From that it will return the instance names on a server that exist for SQL Server. So I now want to issue a command using SMO to pull in if C2 Audit mode is enabled or not. However I need to pass the instance name for that command: *(Code here to load SMO objects)* *$s = New-Object Microsoft.SqlServer.Management.Smo.Server InstanceName* *$s.Configuration.C2AuditMode | Format-Table DisplayName, RunValue, ConfigValue -AutoSize* So I would like to execute it against each instance, but I don't want the script to be dependent upon me hard-coding the instance name. Any ideas? I'm sure yall have some!
scriptpowershellsql-server-smo
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

waushh2 avatar image
waushh2 answered
Try it this way: [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement" -Class ServerSettings | ForEach-Object { $in = $_.InstanceName; if ($in -eq "MSSQLSERVER") { $in = "." } else { $in = (".\{0}" -f $_.InstanceName) }; $s = New-Object Microsoft.SqlServer.Management.Smo.Server $in; $s.Configuration.C2AuditMode | Select-Object @{Name="InstanceName"; Expression={$in}}, DisplayName, RunValue, ConfigValue } | Format-Table * -AutoSize Kind regards, Wolfgang
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yep that worked...now what'd you do?
0 Likes 0 ·
Ah...you put the server name in front of it. What if I wanted to run this against a cluster, where the servername does not come into play?
0 Likes 0 ·
Huh, the class does not exist on a clustered instance.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
Pipe the **Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement" -Class ServerSettings | Select InstanceName** call into a foreach container and pass the instance name in, perhaps, to a function. I'm not near anywhere I can test/code this out right now but I think that would work. EDIT--- Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement" -Class ServerSettings | ForEach-Object -Process {MyFunction $_.InstanceName}
13 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement" -Class ServerSettings | ForEach {"This is the instance name $_.InstanceName"} Gives me the full system object path: servername\\root\\microsoft\\...:ServerSettings.instancename="name".instanceName I don't think that will pass to the SMO object correctly. Is there a way to clean it up?
0 Likes 0 ·
If I throw the Select InstanceName in there I can bring it down to: @{InstanceName=XXXX}.name
0 Likes 0 ·
What versions of SQL, SMO, PowerShell?
0 Likes 0 ·
Have you tried running my code snippet? I don't get a full system object path... just the instance names.
0 Likes 0 ·
AHHH... Try this instead: ForEach {"This is the instance name: " + $_.InstanceName"}
0 Likes 0 ·
Show more comments
Blackhawk-17 avatar image
Blackhawk-17 answered
Okay... what does the following return for you? Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement10" -Class ServerSettings | ForEach-Object -Process {$s = New-Object Microsoft.SqlServer.Management.Smo.Server $_.InstanceName; $s.Configuration.C2AuditMode | Format-Table DisplayName, RunValue, ConfigValue -AutoSize; }
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I get the column headings but no return values.
0 Likes 0 ·
sqlmashup avatar image
sqlmashup answered
this should handle sql server 2005 and sql server 2008 **clusters**... [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null Get-WmiObject -Query "SELECT PrivateProperties FROM MSCluster_Resource WHERE Type = 'SQL Server'" -Namespace "Root\MSCluster" | ForEach-Object { $SqlName = if ($_.PrivateProperties.InstanceName -eq "MSSQLSERVER") {$_.PrivateProperties.VirtualServerName} else {"$($_.PrivateProperties.VirtualServerName)\$($_.PrivateProperties.InstanceName)"}; $SmoServer = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $SqlName; $SmoServer.Configuration.C2AuditMode; } | Select-Object -Property @(@{Name="SqlName";Expression={$SqlName}}, "DisplayName", "RunValue", "ConfigValue");
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.