Deployed Action and Predicate Metadata with PoSH

Comments: No Comments
Published on: October 8, 2015

posh_DBI 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 3d_toolestablished, 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.

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.

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:

ps_eventgm

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.

ps_predicates

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.

And with better formatting, here are those results:

ps_predcompare

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:

And the results of that query:

ps_basicActions

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:

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:

ps_actiontable

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:

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:

ps_actioneav

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.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
October 2015
M T W T F S S
« Sep   Dec »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp

  • @live_sql: #sqlhelp Is there a way to allow users attach DB's using the GUI and them not being SA. I crested custom server roles but still no GUI?
  • @joachimpr: Are your reports at work always taking forever to show you data? This could be the reason why! #SQLServer #sqlhelp
  • @NickyvV: Did anyone ever find a solution for memory consumtion of PP in Excel 2010 that @Kjonge made for 2013 and SSAS? #sqlhelp #ssashelp
  • @Ranjeeth: Can I have DBSer01, DBServ02 having replicas in DBServ03(2 AGs, diff clusters one target). #sqlhelp #alwayson trying to save testing time.
  • @jdanton: @Sir_NiN_Sir both free, but dev=enterprise, feature wise, express, much more limited. For learning, I’d go dev, or just use Docker. #sqlhelp
  • @Sir_NiN_Sir: How's different MSSQL Developer edition and Express edition? both edition for free licensed or not? If I need to practice t-sql.#sqlhelp
  • @jdanton: @Spinner_ Nope. Added a bunch of features to it, as it of SP1 2016. #sqlhelp
  • @Spinner_: #sqlhelp are Microsoft's deprecating sql server express?
  • @SQLHA: @ke0mms Would need to know about your issue before I could even think about giving an answer. #sqlhelp
  • @SQLHA: @ke0mms What do you mean by availability server? That's not a "thing" :) #sqlhelp

Welcome , today is Wednesday, July 26, 2017