So far in this series I have introduced a couple of tools that are suitable for helping with the management of Extended Events. The tools introduced so far are the GUI and TSQL scripts. While the GUI is suitable, using a script really is a far better means of managing Extended Events.

That said, these are not the only tools that could be in the shed for helping in the maintenance and management of Extended Events. One really cool tool that can be added is PowerShell. Isn’t that just amazing?

PowerShell can pack a pretty big punch when dealing with Extended Events. With using PowerShell to manage Extended Events, there could be a bit of a learning curve, and it may not be the right tool all the time. I will cover some uses of PowerShell with XEvents over the next handful of articles.

Management Tools

Since I will be discussing the use of PowerShell to help manage Extended Events, a working knowledge of PowerShell would be somewhat advantageous. If PowerShell is a new endeavor, then I recommend checking out a few tutorials prior to proceeding. Here are a couple of options: Microsoft PowerShell Introduction, Month of Posh by Wayne Seffield, or this Month of SQLPS by Mike Fal (as of publication of this article, this series is ongoing). For my demos, I will be using the Windows PowerShell ISE. Feel free to use whichever tool you feel most comfortable, whether it be an ISE or direct to PowerShell.exe.

After spinning up my ISE, I am going to dive straight into SQLPS.

The first thing I need to do is to change the security policy so I can load the SQLPS. After loading SQLPS, I then revert the security policy back to what it was prior to the change. Here is that code snippet:

$execpol = GET-ExecutionPolicy
$execpol
Set-ExecutionPolicy RemoteSigned -Force
GET-ExecutionPolicy
Import-Module SQLPS
Set-ExecutionPolicy $execpol -Force
GET-ExecutionPolicy

As shown in the previous image, I now have SQLPS loaded. Notice the directory change after the module load, going from C:\Windows\System32> to SQLSERVER:\>. With SQLPS loaded, I can start exploring a little bit.

Part of the exploration technique is to figure out what is available at each level. In this case, I use dir to figure out what is available to me as I progress through the structures. After the first dir from the SQLSERVER:\ , I can see the possible paths to follow and that one of them is XEvent. That is the path (for the default instance) that I want to follow, and I did as shown with this particular script.

cd \XEvent\$env:computername\default\sessions

Now, after I run another dir, I can see the beginnings of a wealth of information. I will get the names of all Extended Event Sessions, if the session is started and when that session was started. This is pretty good news. Seeing the list of sessions, I will pick a session and explore further.

$sessionobj = dir | Where-Object {$_.name -eq 'demosession'}
$sessionobj | gm

Using the Get-Member method against the object I created into which I attached the session of interest, I can peruse the options available to work with the session.

Unlike doing this from within TSQL where views are used to expose the metadata, PoSH takes advantage of Methods and Properties that will permit the perusal of the various components of a session. To explore a little further, I can take advantage of the Metadata property.

$sessionobj.Metadata

Because of the way Extended Events is architected, there is some consistency between the way investigating Extended Events via PoSH vs. TSQL. Here, attached as an object to the Session, there are the Events and Targets objects. This also tells me that I can browse from within the session object down through the Events and the Targets.

To investigate the properties, like I did in the article on exploring session metadata, I can do so in PoSH by doing something like this (bear in mind, that these are scriptlets designed to work with prior examples):

$sessionobj.Properties | ft Name,Value

And this would show me the following output:

These properties should be rather familiar. These are the same settings (the ones in grey outline) that are exposed via the sys.server_event_sessions catalog view. The variable here being that the StartTime property is not one of the properties in that view. Rather, this particular information can be handy and is easily accessed via PowerShell. The ID on the other hand, though not a setting that can be configured, is exposed via that view and is the same ID whether seen through the view or through PowerShell (just as are the properties outlined in grey).

There is a lot of information that is exposed through PowerShell for Extended Events. I have only begun to tap into the available information as I have begun to introduce this tool. I have just shown how to expose the various session settings as was done previously via TSQL in the aforementioned article.

This is just another quick tutorial in the series about Extended Events. Feel free to peruse the growing recap – here.