Azure Log Analytics has collaborated with query language which was extensively used in Application Insights. The integration of query language with Log Analytics has opened up the ways of new capabilities and it’s known as advanced analytics. In the article, we are going to cover some useful features of Log Analytics and its use.

What is Pipe-away?

The pipe-away queries collect data which the users have stored in tables and give the output after accomplishing a complete event.

Event

It’s a simple yet efficient query which returns everything in the Event Table and you can also review a bunch of records from the same table. The user can zoom the relevant data and this process gets easier when you use “take” query with it.

Event
| take 10

The general structure of queries asks you to separate the multiple queries and the separation is done with the help of pipes. The second table treats as input to the output of the first of table. To make the output more precise and specific, ‘where’ query is used to filter the entire data.

Event
| where EventLevelName == “Error”

Here, you need to summarize the data which means the identification of groups of records by common value and it returns the event record.

Event
| where EventLevelName == “Error”
| summarize count() by Computer

What is Search Query?

It’s not necessary that every time you need some specific information from the code as there are chances that you need to search something from the entire code. ‘Search’ keyword is used for this type of search and its output contains a specific IP address.

search “212.92.108.214”
| where TimeGenerated > ago(1h)

Searching the entire data takes much time and one should remember that search term is case sensitive by default.

What is Query Time Custom Fileds?

We often need custom fields as we want to use this data in the analysis process. The best way to check this data is to use your own or specifically created name for the automatically-created columns. In the example, we are using ErrorCount as a field name.

Event
| where EventLevelName == “Error”
| summarize ErrorsCount=count() by Computer
| sort by ErrorsCount

Use extend query instead of summarizing query. Project query uses data from projected fields only.

Event
| where TimeGenerated > datetime(2017-09-16)
| where EventLevelName == “Error”
| extend PST_time = TimeGenerated-8h
| where PST_time between (datetime(2017-09-17T04:00:00) .. datetime(2017-09-18T04:00:00))

Event
| where EventLevelName == “Error”
| project TimeGenerated, Computer, EventID, RenderedDescription

What is Joins Query?

Joins query not only makes comparison between the two columns, but also matches their values before merging them. This is how it merges the two tables by using Update and SecurityEvent:

Update
| where TimeGenerated > ago(1d)
| where Classification == “Security Updates” and UpdateState == “Needed”
| summarize missing_updates=makeset(Title) by Computer
| join (
SecurityEvent
| where TimeGenerated > ago(1h)
| summarize count() by Computer
) on Computer

Update
| where TimeGenerated > ago(1d)
| where Classification == “Security Updates” and UpdateState == “Needed”
| summarize missing_updates=makeset(Title) by Computer

SecurityEvent
| where TimeGenerated > ago(1h)
| summarize count() by Computer

The output shows the listed records of the computers which have missed the security update.