Query Log Analytics from the Command Line

I’ve been doing some work with Azure Log Analytics lately and found the az tool limiting as it relates to querying Log Analytics. So like anyone who gets an itch and needs to scratch it, I decided to put something together myself to help make it a bit easier for me. So using Python and some awesome modules I wrote a little utility that could query Log Analytics and provide data back in a few different formats.

Problem

Querying Log Analytics using the az command line can be challenging in a Unix toolchain. A KQL query can’t be passed in via stdin and the resulting JSON output is not meant for ease of use but for efficient network transport. A query tool should allow input from stdin so that KQL can be piped in to the command like a normal Unix utility. Additionally, the format of the output should be configurable so you can get return results in json, txt or csv

Solution

az-query-log-analytics takes a Kusto query (KQL) and passes it to the Log Analytics REST API. It is NOT designed for large sets of data as it doesn’t attempt to break the return values up in any way. What it does do though is make the return values useful. The REST API for Log Analytics provides a network friendly JSON document format that minimizes bandwidth by listing the column names seperately from the row data. This produces a more compact JSON document by reducing redundancy in the JSON at the cost of usefulness. This script will refactor the results into more useful formats like csv, table and verbose JSON. You need to either pass a KQL file or pass it on stdin like below. It also requires an Azure auth file as well as the workspace id for Log Analytics.

Options include: -t, –timespan -a, –azure-auth -o, –output -w, –workspace_id

The following environment variables can be used rather than passing on the command line…

• AZURE_AUTH_LOCATION
• LOG_ANALYTICS_WORKSPACE_ID

Let’s look at some examples of what it can do

echo "Heartbeat | project Computer, OSType, OSMajorVersion | limit 2" \
    | python az-query-log-analtyics.py -o json | jq
[
  {
    "Computer": "idp",
    "OSType": "Linux",
    "OSMajorVersion": "18"
  },
  {
    "Computer": "idp",
    "OSType": "Linux",
    "OSMajorVersion": "18"
  }
]

Or if you have more complicated queries that wouldn’t work so well with simple echo statements you can use HERE documents or even cat KQL in from a file.

>> python az-query-log-analtyics.py -t PT12H \
    -w xxxxxxxxxxxxxxxx \
    -a ~/.azureauth \
    -o json <<EOF |  
Heartbeat  
| project Computer, OSType, OSMajorVersion  
| limit 2  
EOF  
jq 

[  
  {  
    "Computer": "lin-7599",  
    "OSType": "Linux",  
    "OSMajorVersion": "18"  
  },  
  {  
    "Computer": "lin-7599",  
    "OSType": "Linux",  
    "OSMajorVersion": "18"  
  }  
]

Here is an example that uses a file that has the KQL defined in it. Basically, you can use the script just like any other unix tool for querying Log Analytics.

cat test.kql | python az-query-log-analtyics.py -o json | jq

[
  {
    "Computer": "linux123",
    "OSType": "Linux",
    "OSMajorVersion": "18"
  },
  {
    "Computer": "sp9",
    "OSType": "Linux",
    "OSMajorVersion": "18"
  }
]

In addition to normal JSON you can also export csv and table formats…

>> python az-query-log-analtyics.py -t PT12H -o csv <<EOF                                              
Heartbeat
| project Computer, OSType, OSMajorVersion
| limit 2
EOF

Computer,OSType,OSMajorVersion
lin-7599,Linux,18
lin-7599,Linux,18

And if you want a standard table format

❯ python az-query-log-analtyics.py -t PT12H -o table <<EOF
Heartbeat                                                                                                           
| project Computer, OSType, OSMajorVersion                                                                          
| limit 2                                                                                                            
EOF                                                                                                                  

====================================================================
KQL:
Heartbeat
| project Computer, OSType, OSMajorVersion
| limit 2
====================================================================
 Computer OSType OSMajorVersion
 lin-7599  Linux             18
 lin-7599  Linux             18

So hopefully this helps others out there and if you want to contribute back just open a pull request.