In the last article I introduced a power tool that can be used to help manage Extended Events. That tool is PowerShell. In that article, I focused primarily on introducing PowerShell as a power tool to help in discovering the Extended Event Sessions deployed to the server as well as some of the settings that are associated with a session. This is the same as thing as calling these settings the Session metadata introduced here.

Now that some of the basics concerning how to access Extended Events via PowerShell have been covered, it is appropriate to start digging in a little deeper. This deeper dive will continue in the direction of metadata discovery and familiarity with this powerful tool.

More Power

Picking up from where I left off, and following the same pattern as has already been established, I want to now start diving into the metadata for the events that are tied to a deployed 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'}

In the previous article I noted that within the metadata for the session, there is a listing of some objects that can be further perused, which are related to the session itself. Those objects are Targets and Events (shown in the next image). I want to immediately jump into the Events object and see what needs to be done within PowerShell to access the metadata for the deployed Events.

Luckily, as with how things work from TSQL queries into the metadata, there are patterns that can be established within PowerShell as well. In this case, to get to the Events within the Deployed Sessions, I would need to “query” that object by adding it to the “query” that I use to list the contents of the “Sessions” object already declared (the $sessionobj instantiated previously). This is a lot easier to explain via the code.

$sessionobj.Events

See the pattern? This is the same sort of pattern that was used when querying the various deployed Session settings in the previous article. With the Session loaded in an object, I can query properties or sub-objects by appending that “object” or “property” at the end of the $sessionobj object I created. Running that last snippet will produce the following output.

Immediately visible are a couple of interesting tidbits. Beyond the query returning the list of all of the events in the session, I can see the package names, the predicates and the descriptions of the events. This is similar to the sys.server_event_session_events catalog view excluding the descriptions. Unfortunately, the formatting is a bit awkward so extra measures would be needed in order to get that description to be useful – just the same as would be needed when querying via TSQL.

Additionally, there is a bit of redundancy with this object. There is a listing for the package name for each of the events. Now, take a look at the event names in these results. I cheated a little bit and highlighted the interesting parts. Each event will have the package name as part of the event name within the PowerShell results. Again, just a note because it is one of those things that could cause a bit of grief later when trying to build more complex queries via PowerShell.

Looking a little bit further, if I continue to follow patterns established thus far, I can check the Metadata for  this “object” and learn a little bit more.

$sessionobj.Events.Metadata

And the results:

This should come as no surprise. Actions are tied to specific events so it makes sense that it is an object accessible via the events object. In addition, I can see how the “SET” operations are tied into the Event. The difference here being that the object is called “EventFields”. This should make sense since this metadata is exposed in TSQL via the sys.server_event_session_fields catalog view.

And if I invoke a Get-Methods on the Events Object as follows:

$sessionobj.Events | gm

I will get a clearer picture of the metadata and what I can do with the Events.

In addition to the Actions and EventFields objects, I can see the Name and Predicate properties just the same as I would if I were to query the session events through the catalog view – sys.server_event_session_events.

With the connection to the event fields being so much more obvious through PowerShell and thanks to the prior article on the event fields / “SET Operations”, I want to explore this metadata at this point.

$sessionobj.Events.EventFields

The results of this will be:

From these results, I can see that the “customizable” data point “collect_database_name” for this particular event has been enabled.

In this article I have shown how to begin the exploration into the metadata for deployed Session Events. Additionally, I demonstrated how to quickly get to the set operations tied to those events. I have not yet covered the core concepts of Actions and Predicates which were exposed through some of the queries demonstrated in this article. Tune in for the next article where I will cover both of those topics in greater detail.

With all of the information that has been revealed through this series, it is easy to have either missed some  of the information or to have sensed a bit of information overload. If a recap is needed for one of these or any other reason, feel free to catch up here.