Customize the XEvent Log Display

lego_giftIn the previous article I showed a couple of methods to merge Extended Event Log files. In that article I mentioned the ability to customize the display in the GUI for the merged log files.

Today, I am going to explore how to configure the GUI with filters and different column sets or orders. To proceed, it will require a refresher course via the prior article. If you need a primer in how to merge the XEL files, or to open the XEL files from within the GUI, I recommend taking the time now to read that prior article.

Getting Sticky Again

This should be a rather quick foray into the world of the GUI and how to customize. Just bear in mind that it is a small building block.

Taking up from the prior article, I will continue to use the same sample session and sample data.

Let’s start with a screenshot where we left off the last time:

merge_gui_display

By itself, this is not the prettiest of layouts. Sure there is good information, but the layout is rather simple and leaves one wanting more. The “more” comes easily enough after some familiarity with the GUI. Suppose you desire to change the order of the columns presented in this view, or to even select different columns, there are two methods to achieve that goal. The first option would be to do as shown here:

method1_xe_gui_options

By right clicking an empty space in the title bar, a context menu will be made available. There are several options here that can be a bit fun to peruse. I will just focus on the “Choose Columns” option for now.

The alternative for this method is to look in the toolbar. There will be an XE specific toolbar available when looking at an XE log file. In this toolbar there is a “Choose Columns” button. This method will get us to the same end result as the first.

method2_xe_gui_options

Selecting the “Choose Columns” from either method just presented will open a new window such as the following:

choosecolumns

This kind of screen should be fairly familiar and easily discernible. One the left is a set of available columns that can be added in the display. On the right is the list of columns currently being displayed in tabular format. In between there is a set of buttons to add, remove, add all, or remove all columns. In the top right is a means of re-ordering the columns as presented in the display table. Pick a few and sort the columns to a suitable display mode and you are all set.

Recall from the image showing method one that there is an option to remove the column. This same function can be performed from the context menu or from this “Choose Columns” window.

For the order of the columns, there is another fancy means to do that as well other than through the “Choose Columns” window. Columns can be dragged and dropped from the initial screen to change the presentation order easily enough. Sometimes it is far easier to use the “Choose Columns” window to do that. Pick your poison here and run with it.

Super Stickey

This is fantastic if the manipulation of the display settings is a one-off adventure. What if you need to do this multiple times? Maybe, just maybe, this needs to be done for 100 servers. The process I have just shown can become exceedingly tedious. In addition, every time an XEL file is opened, it defaults back to the same settings I have already shown. What do you do in cases like that?

Beyond using a script (the recommended method by the way), there is an alternative.

displaysett_xe_gui_options

The alternative is found in the toolbar discussed earlier in this article. The option is “Display Settings.” After picking the columns and the order and getting the display dialed in just right, the view can be saved. The view settings will be saved as an XML file with the extension of viewsetting. This view can then be used to immediately apply to a freshly opened log file. Sure it is still an extra step or two to open that view and can still become a bit tedious, but it is far better than resetting the view every time. And a bonus is that the saved viewsetting file will also save merged columns, groupings, aggregations, column order, and filters defined in the view.

Then again, all of this is easily achieved through a tsql script which is far more scalable when dealing with multiple servers or the need to review the data more than once. The choice is yours when using XE.

Though I mentioned the creation of a filter for the displayed log data, I will not be delving into that topic today. It would be a very suitable exercise for the reader to figure out how to apply a filter through this tool. Or wait until the next time when that topic will be covered along with aggregations and groups. In the meantime, I recommend trying to figure it out with a little picking and plucking through the GUI-ness.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.

 

Log Files from Different Sources

Well, it has been quite a pause between articles in the series. It is well past time to continue the series. With such a delay in the series, it is prudent to remind all that there is a series on Extended Events I had done over the course of two months. The complete table of contents for this series can be found here. yule logTruth be told, I had intended to start this series again as my usual 12 days of Christmas series. Sadly, I am a bit behind on that series. Maybe some of there will be multiple posts a day to help catch up.

As was the case throughout the course of the series, I intend over the next few articles to continue to produce building blocks that may be helpful for later articles, or concepts. With that in mind, the chosen topic today is very basic in nature. While basic, it is a rather important topic.

While working with Extended Events, or with any sort of logging or tracing utility, it becomes critical to consume the data from disparate sources or even to consume the data from a source server on a different server. The question becomes: How to consume that data?

Getting Sticky

When it comes to working with log files from different sources, or most tasks within SQL Server for that matter, there are two general methods. The first method is of the type frequently frowned upon by the more senior of database professionals, sysadmins and so forth. This first method is to use the graphical user interface or GUI. This is more of a point and click method. This is also a method that I will generally shy away from for most tasks. That said, this method is easily performed through the GUI and is reasonably safe.

The first step is illustrated with the following image:

XE_merge

From within Management Studio, navigate the file menu as shown in the preceding image. After clicking on “Merge Extended Event Files”, a dialog will open as shown (in the background) of the next image:

XE_merge_opendialog

Clicking add from the “Merge Extended Event Files” window will open a dialog that permits you to navigate to the location of the XEL files that need to be opened and merged. Once located, a single file or multiple files can be selected. One should proceed carefully here to ensure against opening too many files or files that may be too large. Opening too many files or files that are too large can cause various unwanted effects.

Once the file(s) is(are) open, a grid like window will be open in SSMS that looks like the following:

merge_gui_display

From here, one can manipulate the display to something more suitable to his/her display preferences (e.g. filtering or column layout). I will not cover that here but will be saving it (the display customization tutorial) for another time. That aside, I have just shown how easy it is to look into an Extended Event Log file through the use of the GUI.

Not So Sticky

For the less sticky  (less GUI) method, I have shown a similar routine in the past that lays the groundwork for how to do this via script. You can read a little about that here. Building on that method, there is a tweak to be made – we must specifically name the path to the XEL files to be merged rather than try to build it dynamically.

Using the same session files as shown in the GUI examples, I will show how to proceed with the merge via script.

This script lays out a rather simple process. That process being to load the log data into a table (in a database of your choosing) and then once loaded, query the data to better understand what has been captured. In the preceding script I left a few notes. It is important to understand that if trying to pull in the logs from a SQL 2008 or R2 instance then the XEM file must be included (as I have done for this particular example).

To parse the data into a friendly format that I can use for analysis, I would do something like the following:

From here it is really easy to add/remove columns or re-order the columns into a more friendly format for the person reviewing the data. This is incredibly easy – especially given the appropriate recipe / script / process. I would also venture that this method will require fewer resources and lead to less chance of error.

Enjoy working with evaluating and merging these XEL files from different sources. It should be fun!

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.

 

page 1 of 1








Calendar
December 2015
M T W T F S S
« Oct   Jan »
 123456
78910111213
14151617181920
21222324252627
28293031  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, March 28, 2017