How to find out what you need to know
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.