Connect Looker Studio with Azure — Part I

What is a Looker dashboard

Looker is a data analytic platform that allows you to create and manage dashboards and reports interactively with other organization members. Loker provides the capability to manage both real time and non real time dashboard. That allows users to take data driven decisions based on the performances or trends.

A Looker dashboard is a visual representation of data, presenting key metrics and insights in a clear and accessible manner. Those dashboards can be shared between users and multiple users can collaboratively work on the dashboard at the same time.

How to create a looker dashboard

To create a new dashboard in looker, you need to login to your looker studio. You can use the link below to sign in to the looker.

https://lookerstudio.google.com/

After login to the looker space, click the create icon in the left top corner. There you can choose the type you want to create. It can be a “Report”, “Data source” or “Explorar”. In there click the “Report” section to create a new report.

Then it will automatically prompt you to link your data to the report that you created. In there you can link google analytics, looker data sources, local CSV files, google sheets etc. Click google sheet and it will show all the google sheets that you have the read access or a higher permission. Then click on the required spreadsheet and choose the worksheet that you want to connect.

Go to the google sheet and create a fresh google sheet to connect with the looker dashboard that you created.

Link Looker dashboard with google sheet

Then click on the Add data button to add the data source to the google sheet.

After you click on the Add data button it will allow you to add data using google connectors or your data sources. Go to the Google connectors and select google sheet and it will show the available google sheets to link with your dashboard.

Fetch data from Azure Log Analytics Workspace

To fetch data from Azure Log Analytics (LA) using Google Apps Script, you can make use of the HTTP service in Google Apps Script to send HTTP requests to the Azure Log Analytics API. For this one you need to Azure AD application to authenticate with Azure and that AD application should have the log reader permission on the Azure Log Analytics workspace.

You can use the following code section to fetch the data using Azure Log Analytics workspace.

function updateSheetWithData() {
  // Set the API endpoint URL
  var apiUrl = "https://login.microsoftonline.com/<tenant ID>/oauth2/token";
  // Set the request payload
  var payload = {
    "grant_type": "client_credentials",
    "client_id": "<Client ID>",
    "client_secret": "<Client Secret>",
    "resource": "https://api.loganalytics.io"
  };

  // Set the headers for the API request
  var headers = {
  "Content-Type": "application/x-www-form-urlencoded"
  };
  var urlEncodedPayload = Object.keys(payload)
  .map(key => encodeURIComponent(key) + "=" + encodeURIComponent(payload[key]))
  .join("&");

  // Make the API request with headers and payload
  var options = {
    "method": "POST",
    "headers": headers,
    "payload": urlEncodedPayload
  };
  var response = UrlFetchApp.fetch(apiUrl, options);
  var data = JSON.parse(response.getContentText());
  var accessToken = data.access_token;
  var accessToken = data.access_token;
  var encodedQuery = "Base 64 encoded query";
  
  var headers = {
    "Authorization": "Bearer " + access_token
  };
  
  UpdateSheet(encodedQuery, accessToken, "Organization", "Event", "customer-details", "US")
}

function UpdateSheet(encodedQuery, access_token, appCount, event, sheetName, region) {
  var headers = {
    "Authorization": "Bearer " + access_token
  };
  
  // Make the API request with headers
  var options = {
  "method": "GET",
  "headers": headers
  };
  
  var apiUrl = "https://api.loganalytics.io/v1/workspaces/<Workspace ID>/query?query="+encodedQuery;
  var response = UrlFetchApp.fetch(apiUrl, options);
  var data = JSON.parse(response.getContentText());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  sheet.clear();
  var values = [["Date", appCount, event, "Region"]];
  var rows = data.tables[0].rows;
  rows = values.concat(rows);
  var startCell = sheet.getRange("A1:C31"); // Modify the cell as per your requirement
  
  for (var i = 0; i < rows.length; i++) {
  var rowData = rows[i];
  // Check if the 3rd column is empty
  if (rowData[3] == null) {
    rowData[3] = region;
    }
    // Define the target range for each row
    var targetRange = startCell.offset(i, 0, 1, rowData.length);
    // Set the values of the target range with the API data
    targetRange.setValues([rowData]);
  }
}

updateSheetWithData

Fetch data from Azure Application Insights

To fetch data from Azure Application Insights using Google Apps Script, you can use a similar approach by making HTTP requests to the Application Insights API. You can use the AD application to authenticate and get the required data using Azure application insights.

Following is a example

function updateSheetWithInsights() {
  // Set the API endpoint URL
  var apiUrl = "https://login.microsoftonline.com/<Tenant ID>/oauth2/token";
  // Set the request payload
  var payload = {
    "grant_type": "client_credentials",
    "client_id": "Client ID",
    "client_secret": "Client Secret",
    "resource": "https://api.applicationinsights.io/"
  };

  // Set the headers for the API request
  var headers = {
    "Content-Type": "application/x-www-form-urlencoded"
  };

  var urlEncodedPayload = Object.keys(payload)
  .map(key => encodeURIComponent(key) + "=" + encodeURIComponent(payload[key]))
  .join("&");

  // Make the API request with headers and payload
  var options = {
    "method": "POST",
    "headers": headers,
    "payload": urlEncodedPayload
  };

  var response = UrlFetchApp.fetch(apiUrl, options);
  var data = JSON.parse(response.getContentText());
  var accessToken = data.access_token;
  var kqlQuery = `KustoQuery let lastActivity = customEvents`;

  UpdateInsightsSheet(kqlQuery, accessToken, "app-insights")
}

function UpdateInsightsSheet(kqlQuery, access_token, sheetName) {
  var headers = {
    "Authorization": "Bearer " + access_token
  };
  var payload = {
    'query': kqlQuery,
  };
  var options = {
    'method': 'POST',
    'headers': headers,
    'payload': JSON.stringify(payload),
    'contentType': 'application/json',
    'muteHttpExceptions': true
  };
  var apiUrl = "https://api.applicationinsights.io/v1/apps/<App insights ID>/query";
  var response = UrlFetchApp.fetch(apiUrl, options);
  var data = JSON.parse(response.getContentText());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.clear();
  if (data && data.tables && data.tables.length > 0 && data.tables[0].rows.length > 0) {
    var rows = data.tables[0].rows;
    // Define the values to be set in the sheet
    var values = [["Organization", "Count", "Last_Activity_TimeStamp", "Last_Activity_Country", "Activity"]].concat(rows);
    // Set the values in the sheet starting from the first cell (A1)
    sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  }
}

updateSheetWithInsights