I have recently shown that using PowerShell can be extremely powerful in obtaining insight into how to investigate deployed Extended Event Sessions. Throughout the demos I have used, I hope that it has also shown that PowerShell can be very easy to use.

Querying this metadata through TSQL is easy, but using PowerShell does seem to simplify this just a step more. The simplicity of PowerShell is a different kind of easy over TSQL. For me, the simplicity of PowerShell comes in the quantity of code. That said, I want to reiterate what I said previously – there is a bit of a learning curve with PowerShell. PowerShell exposes Extended Events differently than what one would expect due to familiarity with Extended Events through the catalog views and the DMVs I have previously discussed.

I have demonstrated how to access session information as well as how to access Event metadata. That said, I left the discussion of Actions and Predicates for this article. As I expose this information, the simplicity of PowerShell will continue to be reinforced – along with the patterns that I hope have started to become apparent in the previous articles.

Regrouping

Picking up from where I left off, and following the same pattern as has already been established, it is time to dive into the metadata for the Predicates and Actions that are tied to an Event deployed with a session using PowerShell. This means that I will need to ensure I have a session established that has the SQLPS module loaded. Then I need to ensure I browse back to the sessions folder.

Just in case, here is the working directory for the sessions out of the default instance.

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

Also, from this point, I will go ahead and load the “demosession” session into an object again for demonstration purposes throughout the remainder of this article.

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

With that loaded, I just need to remember to reference the Events object (as was introduced in the prior article).

Predicates

Looking back at some of the metadata that was revealed for the Events object in the previous article, I have the following:

If I query the Events directly, I should be able to see that there is a Predicate returned. Unfortunately, the formatting of the output is not very friendly and much of the information is truncated. Seeing the Predicate listed as another item that can be queried direct is somewhat useful. In addition there is a PredicateExpression property that could be useful.

In this following image, I show both the queries used for each (Predicate and PredicateExpression) as well as the corresponding output.

Notice how the PredicateExpression is a bit more familiar in the output? With the wonky formatting of the output for the predicate, one could also presume that this output would be the XML type of formatting for a pred_compare object. With a little extra effort, this can be confirmed.

$sessionobj.Events.Predicate | ft Operand
$sessionobj.Events.Predicate | ft Value
$sessionobj.Events.Predicate | ft Operator

And with better formatting, here are those results:

These results would reinforce the prior presumption. And it also helps to reinforce the differences between how the Predicate is represented in PowerShell over the sys.server_event_session_events view where the predicate column is closer in value to the PredicateExpression in PowerShell. Additionally predicate_xml in the the view more closely fits the Predicate property within PowerShell. As long as one can keep these differences in mind, it will make working with PowerShell and Extended Events a tad easier.

Actions

Accessing the metadata for Actions via PowerShell is a little bit more straightforward in that there aren’t multiple objects like with Predicates. Unfortunately, with Actions, it is right back to the EAV data model with the Actions object which can cause a different level of complexity and possibly confusion.

Starting with a very basic query to the Actions:

$sessionobj.Events.Actions

And the results of that query:

In addition to a problem noted with the Events (package name being listed twice), there is an additional problem with the initial output here. The problem with the default output is that the Event name is missing. Granted, this is also a problem with the Predicates examples too. This is one of those areas where extra effort has to be made when using PowerShell to explore Extended Events. Luckily, the data is there. All that is needed is to know how to get to the data.

So how does one figure out the Event for which the Action (or Predicate) was added? I have that in this next example:

$sessionobj.Events.Actions | ft Parent, Name

This query is simply requesting the Parent (Event) of the Action, and then the Action name to be returned in a table format. Here is what the output should look like:

Having the Event name is a critical piece, especially in a more complex Session such as the system_health default session. After-all, an Action (yes it must be repeated because it is a core concept) is only attached to an Event and has to be configured for each individual Event.

Circling back around to the EAV concept. One of the areas within Actions where this is more evident is when trying to look at the properties of the Actions module. Starting with a sample query such as this:

$sessionobj.Events.Actions.Properties | ft Name,Value

Again, I am instructing the results to be returned in a table format. If I don’t do that, then everything is returned in a single column and it is far more difficult to pick out the needed bits of information. Forcing the results to a table format, I get the following:

Looking at these results, I see Name as a value under the Name column and the associated value for it under the Value Column. Then there is a lot of repeating. This means that a little extra work is going to need to be done to parse things a bit further.

In this article I have covered two of the core concepts related to deployed sessions in Extended Events – Actions and Predicates. To be able to effectively use PowerShell, I have also covered a few of the nuances necessary in figuring out where essential metadata is exposed via this tool.

Stay tuned as I continue to work through some of the Target metadata in the next article. To recap the series, please visit the table of contents.