Freigeben über

How to find out what you need to know

  • Artikel

For as long as I can remember I have come across problems where I need to know the range of valid options for a setting so that I can make an appropriate change, but I haven’t know the right values to use.

For  example - If you wanted  to change the powerplan of your computer you might know that there is a 'High Performance' setting. Perhaps you know there is a 'Balanced' option – it’s the default on all Windows OS since 2008 but, would you guess that there is a 'Low Performance' or 'Power saver' or 'battery saver.

How about SQL Server Login Mode options? We talk about SQL Server Authentication and Mixed Mode but what are the actual values to use when you want to change them programmatically and what other values are there?

Until recently I used to start searching online and perhaps encounter a blog where someone has done the investigation already or I would find a MSDN or TechNet article that explain it all.

However, you can do this all from the safety of your own script editor. Let's see how.

Example 1 – Getting the valid values for the SMO LoginMode property of a SQL Server.

Step 1 – create an SMO object that represents the server we  are interested in and then set a different variable to  be the LoginMode

 $SMOServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "$env:computername\sql2016"

$LMode = $SMOServer.LoginMode

So, $LMode is now representing the LoginMode for our server, we can now see the variable type by running

 $LMode.GetType()

For me this returns

 IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     ServerLoginMode                          System.Enum

Step 2 - We can see in last output of Step 1 that the Name of the variable type is ServerLoginMode. We know that we are using the SMO object Microsoft.SqlServer.Management.Smo.Server (it’s the object type we used when we created $SMOServer) so we can now get all the valid values for LoginMode by executing [System.Enum]::GetValues.

 [System.Enum]::GetValues([Microsoft.SqlServer.Management.SMO.ServerLoginMode])

The results of this are:

 Normal 
Integrated 
Mixed 
Unknown

Which is exactly what we were looking for.

Example 2 – getting the valid values for a Windows Service status

Step 1 – In PowerShell the most common interaction with a Windows Service is via the Get-Service cmdlet. Running this gives us mostly services that are Running or Stopped but perhaps we want to build a pester test that is checking that a service is running or at least starting up. How can we check for the right values?

Working once again with a SQL Server service we run

 Get-Service | ? Name -match mssql\$

And we get a list of SQL Server database services that are present on the local computer

 Status   Name               DisplayName
------   ----               -----------
Stopped  MSSQL$SQL2008A     SQL Server (SQL2008A)
Stopped  MSSQL$SQL2008B     SQL Server (SQL2008B)
Running  MSSQL$SQL2014      SQL Server (SQL2014)
Running  MSSQL$SQL2016      SQL Server (SQL2016)

Step 2 – as in example 1 we need to create a variable that represents the service so that we can investigate it so we don’t need all 4 services getting in the way from the above so lets alter the code a little

 $svc = Get-Service | ? Name -match mssql\$ | select -First 1

Step 3 – we now have $svc as a single service, lets set another variable to the service status

 $svc_status = $svc.Status

And then we just check its type as before

 $svc_status.GetType()

 IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     ServiceControllerStatus                  System.Enum

Ok, this makes sense, we have a Name that is ServiceControllerStatus so lets pump this through [System.Enum]::GetValues …

 Unable to find type [ServiceControllerStatus].

Oops. I have trimmed the error a bit, but we get an error, not a nice message about the values we can use. We need to get more information about the $svc_status variable...

To see all the information we need to get past the defaultpropertyset of the GetType() method, we do this with

 $svc_status.GetType() | select *

The resulting list is huge and I'll leave the review of all those details as a step for the readers that are interested. In there however, there are a couple of property names that look relevant to our investigations – NameSpace and FullName in particular look promising. Let's get the values that they hold

 $svc_status.GetType() | select fullname, namespace

This gives us:

 
FullName                                      Namespace
--------                                      ---------
System.ServiceProcess.ServiceControllerStatus System.ServiceProcess

Step 4
We've got what we need, let's make the final attempt at getting the information we came here for

 [System.Enum]::GetValues([system.ServiceProcess.ServiceControllerStatus])

And it indeed gives us the list of valid Service Status values that we were after:

 Stopped 
StartPending
StopPending
Running
ContinuePending
PausePending
Paused

Hopefully this has shown you the technique needed to investigate variable and object types using .GetType() and [System.Enum]::GetValues and how to locate valid values to be used in your code. Please let us know in the comments if this is useful to you or if you have any questions.

Comments