PowerQueries - New Features

Introduction

Our Engineering Team recently released a number of useful features and improvements to our PowerQueries. I highly recommend checking them out (if you haven't already had a chance to do so). PowerQueries enable DataSet customers to extract valuable insights from their log data by combining our blazing fast search with the analytical capabilities of SQL. Best of all, PowerQueries can be used immediately. Simply upload your log data -- there are no prerequisites!

timebucket

The timebucket function enables you to convert the timestamp associated with a log into a generalized interval, such as day, hour, minute, etc. You can then use the timebucket to group log events by day.

("error") && $k8s-deployment == 'paymentservice' "Received error status code unknown" |
group count(1) by timestamp = timebucket("1d") 

Returns

Note 1: This function is still pretty new and awaiting additional refinement. For now, it's limited to UTC time. In other words, if I look at the value for today that timebucket("1d") returns, it will be 2020-08-30 17:00 since my account is set to Pacific Standard Time (PST) and this is the equivalent of 2020-08-31 00:00 in UTC 

Note 2: Without the timestamp assignment (above), timebucket will return the associated Unix epoch

transpose

Similar to the SQL function, transpose turns rows into columns. For example,

"error" && k8s-deployment = * | 
group count(1) by k8s-deployment

Returns

With | transpose k8s-deployment as the final line, this becomes

querystart / queryend / queryspan

The querystart and queryend functions enable the start and end time of a query to be extracted in various units (days, hours, seconds). Default is nanoseconds if no unit is provided. I found this to be extremely useful when generating DataSet search URLs from the results of a PowerQuery. Similarly, queryspan is used to return the length of the query timerange.