Using OMS Search API to create 100’s of queries in 2 min

OMS log analytics allows you to save your preferred search queries and organize them under categories .  All what you need is to figure out your search query then click save button in the Search bar  then enter the Name and Category then hit save.  How about if you have 100 of queries that you need to save in OMS ?

image

In this blog I will show you how to upload hundreds of queries saved in csv file to OMS using PowerShell and the latest OMS API.  before you start please read Tiander Turpijn blog this will give you good understanding about OMS API and the prerequisites to use OMS PowerShell cmdlets.

In my example I have used the Service Principal to login and authenticate to my OMS workspace instead of typing my user name and password . if you need to know more how to create Service principal name , please read Tom FitzMacken’s blog

First let’s prepare the CSV file that will have the queries in excel type the following as a header: SavedSearchId, DisplayName, Category, Query

Your Excel Sheet will look similar to the following:  image Column D is the concatenation of   Colum E, F and G

then copy Column D and paste in the same place  (select Paste Value) then delete Column E,F and G.
your Excel Sheet will look similar to the following: 
imageimageSave the file as CSV “OMSQueries.CSV”

PowerShell Time:

Line 8 to 11 : this is where I connect prepare the credential to connect as the service principal name.

Line 16 and 18: Initialize the variable for the Resource Group containing the workspace name you can get this information from your azure portal

image

Line 20: Identify the Search version for log analytics , in the MSDN it says not required, however I verified and it is required otherwise you will receive an error (Thanks to Tiander Turpijn  and Jim Britt )

   1: <;#

   2:     Author :Islam Gomaa 

   3:     Purpose :save bulk queries in OMS Saved searched 

   4:     Date :- April 13th 2016

   5:     Reference : - https://msdn.microsoft.com/en-us/library/mt603851.aspx

   6: #>;

   7: 

   8: $SecPasswd = ConvertTo-SecureString "qg1t491LBlye6nkJupST54iEoxCHu1KuPhS1T/6N4OG=" -AsPlainText -Force

   9: $UserName= "2FF75X4G-6179-3067-358d-3d269df0f57a" # Client ID

  10: $TenantId ="dd9ed1c8-6445-68c3-as3c-72b00s45b45f"

  11: $Creds = New-Object System.Management.Automation.PSCredential($UserName, $SecPasswd)

  12:  

  13:  

  14: Login-AzureRmAccount -ServicePrincipal -TenantId $TenantId -Credential $Creds >;$null

  15:  

  16: $OIRessourceGroup= "RG-Azure1"

  17:  

  18: $OIWorkspace = "ContosoIG"

  19:  

  20: $version =1 

  21:  

  22: $CSVFile = Import-Csv C:\Users\isgomaa\OneDrive\Desktop\OMSQueries.csv

  23: $FileLenth = $CSVFile.Length

  24:  

  25:  

  26: for ($i=0; $i-lt $CSVFile.Count-1 ;$i++)

  27: { 

  28:    $SavedSearchId = $CSVFile[$i].SavedSearchId

  29:    $DisplayName = $CSVFile[$i].DisplayName

  30:    $Category= $CSVFile[$i].Category

  31:    $Query= $CSVFile[$i].Query

  32:    Set-AzureRmOperationalInsightsSavedSearch -ResourceGroupName $OIRessourceGroup -WorkspaceName $OIWorkspace `

  33:                                           -SavedSearchId $SavedSearchId -DisplayName $DisplayName `

  34:                                           -Category $Category  -Query $Query -Version $version

  35:                                          

  36: }

Line 22 : read the file and store it in an array

From line 26 to the end of the script looping in the array and creating the Queries in OMS

The Final result on PowerShell ISE

image

The Final result when you login to OMS 

image

Thanks

Islam G.

Comments are closed.