Azure Log Analytics: Queries, the basics explained – Part 2

Now that we have opened our first tab for producing a query in part 1 lets look at, some other capabilities.

I mentioned Schema in the last post, its a good way of finding which types of data you may have and what solutions.  From my workspace you can see the variety of Solutions I have and where I’ve opened [Log Management] you can see high level types such as Event and Heartbeat.  If you roughly know what you are looking for, start to type in the Search field.  I’ve chosen Heartbeat.   I can either type ‘Heartbeat’ into the query windows or double click on the word Heartbeat in the schema.

Lets imagine I’ve typed “Heart” and then intellisense will suggest Heartbeat for you.  You can press [tab] to complete the word, much like other text entry in PowerShell etc…

It will automatically place a pipe character “ | ” in the second line as it expects you to query on more than just Heartbeat.

TIP: If you want you can just highlight the word Heartbeat or any section of text and press [GO] or SHFT+Enter to execute *JUST* the code selected.

I often start with a where operator such as:

Heartbeat
| where TimeGenerated > ago(30m)

The aim here is to reduce the records we return as I test the query, basically only show me data from the last 30mins – see Ago.  You can of course set a global range in the portal, maybe choose 10, 60mins whilst you are testing?

Another good option is to use Limit or Top – there is also take as well.  You can use these in conjunction with TimeGenerated or on their own.  In this example I just TAKE 10 rows of data.  Obviously if there is no data returned you may need to extend the time range.

 

You could as an alternative use a Let operator to set a range, this command only shows data between two values for a 60second period.

let startDatetime = todatetime("2017-12-13 09:00:00.0");
let endDatetime = todatetime("2017-12-13 09:01:0.0");
Heartbeat
| where TimeGenerated between(startDatetime .. endDatetime)

You can also use the Schema or start to type to see what else you can query on.  Here I've used a WHERE and its suggesting some options, I can [tab] complete or use the schema to double click on one I like.  Remember the data this where operator is looking at, is only for the 10 records I’ve asked for, not the whole dataset.  Again this enables us to focus on the relevant data we are seeking.

From here I might do something like this example, where I only see records for Computers that start with “A”

Heartbeat
| where TimeGenerated > ago(30m)
| take 10
| where  Computer startswith "a"

Again this may return a lot of horizontal data (scroll to the right to see the columns) or press the down arrow to the left of each data row.

Which gets you this view:

You can also use the Project operator to just show the table(s) you are interested in, which is especially good for testing – reducing the screen clutter!