Spotlight on the ADX Time Pivot Visualization
Published Jun 10 2020 01:56 AM 4,982 Views
Microsoft

No matter whether you're a developer, a support engineer, or a security analyst, you must have found yourself on more than one occasion feeling lost and overwhelmed. You’d be staring at multiple log files or too many open query tabs on your monitor, feeling utterly stuck, with nothing making any sense, and your investigation leading you nowhere.

 

We've all been there, and we've all longed for a simple view into the environment that would help us to easily find the common path, get down to the root cause, and solve the case in a simple and elegant way.

 

Azure Data Explorer Time Pivot visualization is the ultimate tool to do just that.

 

Time Pivot visualization

The Time Pivot chart is a powerful interactive navigation tool that lets you analyze your data over an events timeline, that pivots on a time axis.

Time Pivot makes it easy to identify event flows, and beautifully tells the story of “what happened”.

It helps you to generate multiple views of the same data by letting you pick multiple levels of data slicing.

Time Pivot visualization is your essential tool for analyzing data across hundreds of different tables.

 

timepivot.png

 

 

NOTE:   Time pivot visualization is currently available only on Kusto Explorer, the desktop edition of the Azure Data Explorer.

 

How it works?

You can use the time pivot visualization in one of several ways:

  • Run a query In the Kusto Explorer and then click “Time Pivot” when the results are back.

OlgaGold_1-1591858298876.png

 

  • Add a “| render timepivot” suffix to a Kusto query and run it. This will switch to the time pivot visualization over the result table.

Unlike other visualizations, the time pivot visualization doesn’t replace the query result pane, but rather the query pane, and the result pane is still visible. In fact, the result pane becomes a subordinate to the time pivot pane, showing only result records that are selected in the time pivot.

Let's take it for a ride. We'll start with a simple query over the Storm Events table.

StormEvents
| render timepivot

 

This query will yield the following:

TP1.png

 

The upper half of the display shows the time pivot, to the left is a control tree with the distinct values of the default group key, EventType, and at the top we have the StartTime column automatically binned.

You can now add additional group keys by clicking on the Add Level button at the top, and drag-and-drop them into place, to reorder the grouping and get different views of the data:

TP5.png

 

 

Now that the data is arranged on the pivot chart, you can select specific values of the group key and/or time, and have the lower half of the display show only the corresponding records.

For example, expanding INDIANA and selecting a specific time for a specific event (Flood), will give the following:

TP2.png

 

You can also select to see all flood events in Indiana by selecting the entire Flood row:

TP3.png

 

 

Another option to run the time pivot visualization is to use parameters as group keys:

StormEvents
  | render timepivot with (series = EventType, State)

 

This will yield a chart with the selected group keys in place:

TP6.png

 

 

What is it good for?

When investigating a customer request failure, the single request "story" consists of tens of thousands of records and reading through them without proper visualization can be very time consuming, and quite frankly hard. Time Pivot visualizes the data for you, using dynamic grouping and filtering and makes the data available for easy exploration.

 

In the following example we’re looking for a request failure on ADX Help cluster.

 

KustoLogs
| where Timestamp between(datetime(2020-06-04 03:00) .. 1h)
| where Source == 'HELP'
| where ClientActivityId == "KustoWebV2;62f4c6c0-39ea-48da-8003-5a3d4235347b"
| render timepivot with (series= Level, ActivityType)

 

This query returns ~27K records.

 

Time Pivot allows logical storytelling, placing data on a timeline and grouping it by Level and ActivtyType (logical activities) – so that error prone activities can be immediately pinpointed.

 

TP4.png

 

 

You can get a high-level overview of the events, or dive right in and see the error texts, inspect all events before, during and after the moment of error, and more.

 

You can also add additional group keys to gain a better understanding of what happened. For example, adding Machine and reordering the group keys will show you the specific machines that generated these errors.

 

In conclusion

Time Pivot saves you time and simplifies your work, by telling the story in a visual way that makes it easy to identify events and patterns and draw the right conclusions.

 

How to get started?

Go out and try it yourself!

Download Kusto Explorer

Add the publicly available ADX Help cluster https://help.kusto.windows.net and you’re all set to go!

 Learn more about Azure Data Explorer (Kusto):

  1. Azure Data Explorer
  2. Documentation
  3. Course – Basics of KQL
  4. Query explorer
  5. Azure Portal
  6. User Voice
  7. Cost Estimator
  8. ADX End to End Lab

Join us to share questions, thoughts, or ideas about Azure Data Explorer (Kusto) and receive answers from the Azure Data Explorer community.

 

2 Comments
Version history
Last update:
‎Jun 11 2020 12:05 AM
Updated by: