Forum Discussion

vishalkakkar's avatar
vishalkakkar
Copper Contributor
Jun 06, 2019

Getting Logs data across different components/ name

We have two Log Analytics  Workspace and under this workspace , we have two names eastusprodanalytics and westusprodanalytics   Which query should i use to union data across two names at category ...
Azure Log Analytics
azure monitor
CliveWatson's avatar
CliveWatson
Former Employee
Jun 06, 2019

vishalkakkar 

This show how to get the EVENTS table from two workspaces.

union withsource = SourceApp
workspace('eastusprodanalytics').Event,  
workspace('westusprodanalytics-IT').Event 
| parse SourceApp with * "('" applicationName "')" *  
// add query here

You could add:

 

union withsource = SourceApp
workspace('Fabrikamltdprod').SecurityEvent,  
workspace('contosoretail-IT').SecurityEvent 
| parse SourceApp with * "('" applicationName "')" *  
| summarize count() by applicationName, EventID

You could also SAVE the first example as a Function to call in other queries

For more see: https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/cross-workspace-query

 

Union within a workspace is here:  https://docs.microsoft.com/en-us/azure/kusto/query/unionoperator

 

 

 

 

vishalkakkar's avatar
vishalkakkar
Copper Contributor
Jun 06, 2019

Thanks CliveWatson 

 

I mistakenly put as workspace. 

I have two resources Under LogAnalytics workspace group. and under those two resources, there are two names . For eg:

 

Log Analytics workspaces 

 -> Name                      Type
eastusprodloganlytics eastus-prod-rg
westusprodloganlytics west-prod-rg

 

Is there a way to union till name and type

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Jun 06, 2019

    vishalkakkar 

     

    Are you really asking for a Union?  This will Union the Table called Event with the Table called SecurityEvent (using 10 records from each)

     

     

    union isfuzzy=true
(Event | limit 10), 
(SecurityEvent | limit 10)

     

    This is a great question to ask, if you supply the data in a datatable:, are you trying to concatenate the two values?

     

     

    let dummyData = datatable(Name:string, Type:string)
[
    "eastusprodloganlytics" , "eastus-prod-rg" ,
    "westusprodloganlytics" , "west-prod-rg" 
];
dummyData
| where Name !=""
| project theMergedStringIs = strcat(Name, " " , Type)

     

    If this isn't right, can you share a screenshot, as its hard to tell what your data looks like and what you want it to change to?  e.g.

     

     

    Thanks Clive

     

     

     

    • vishalkakkar's avatar
      vishalkakkar
      Copper Contributor
      Jun 10, 2019

      CliveWatson 

       

      Please find below screenshot, am tying to concatenate logs from two different workspaces.

      So there is one more workspace which i need to add to concetenate logs from two different workspace

       

      -VIshal Kakkar

       

       

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Jun 10, 2019

        Hello vishalkakkar 

         

        So to merge two workspaces (or more) in single query we can UNION them.  You will have to amend the code to put in your names lines 4 & 5 and change the orignalhost_s line to match your setup. 

         

        union withsource = SourceApp
//workspace('eastusprodanalytics'   ).AzureDiagnostics,  
//workspace('westusprodanalytics-IT').AzureDiagnostics, 
workspace('ContosoRetail-IT').AzureDiagnostics, 
workspace('Fabrikamltdprod' ).AzureDiagnostics 
| parse SourceApp with * "('" applicationName "')" *  
| where Category == "ApplicationGatewayAccessLog" 
| where originalHost_s == "13.85.25.40"
| summarize count() by bin(TimeGenerated, 1h) , applicationName
| sort by count_ desc

         

        the output looks like this (only one of my workspaces had these entries) - so only one is shown in the "applicationname" column.

         

        TimeGenerated applicationName count_
        2019-06-04T09:00:00Z ContosoRetail-IT 1525
        2019-06-07T07:00:00Z ContosoRetail-IT 1424
        2019-06-08T21:00:00Z ContosoRetail-IT 520
        2019-06-09T06:00:00Z ContosoRetail-IT 295
        2019-06-06T00:00:00Z ContosoRetail-IT 269
        2019-06-05T11:00:00Z ContosoRetail-IT 220
        2019-06-05T12:00:00Z ContosoRetail-IT 216
        2019-06-05T10:00:00Z ContosoRetail-IT 216
        2019-06-09T16:00:00Z ContosoRetail-IT 80
        2019-06-04T20:00:00Z ContosoRetail-IT 58
        2019-06-07T16:00:00Z ContosoRetail-IT 58
        2019-06-10T16:00:00Z ContosoRetail-IT 54
        2019-06-08T16:00:00Z ContosoRetail-IT 52
        2019-06-06T17:00:00Z ContosoRetail-IT 50
        2019-06-05T16:00:00Z ContosoRetail-IT 50
        2019-06-04T16:00:00Z ContosoRetail-IT 20
        2019-06-07T01:00:00Z ContosoRetail-IT 16
        2019-06-07T08:00:00Z ContosoRetail-IT 16
        2019-06-10T06:00:00Z ContosoRetail-IT 16
        2019-06-09T05:00:00Z ContosoRetail-IT 14
        2019-06-04T02:00:00Z ContosoRetail-IT 10
        2019-06-05T03:00:00Z ContosoRetail-IT 8
        2019-06-03T21:00:00Z ContosoRetail-IT 5
        2019-06-06T03:00:00Z ContosoRetail-IT 4
        2019-06-07T06:00:00Z ContosoRetail-IT 4
        2019-06-03T19:00:00Z ContosoRetail-IT 3
        2019-06-06T14:00:00Z ContosoRetail-IT 2
        2019-06-06T23:00:00Z ContosoRetail-IT 2
        2019-06-06T13:00:00Z ContosoRetail-IT 2
        2019-06-10T14:00:00Z ContosoRetail-IT 2
        2019-06-05T13:00:00Z ContosoRetail-IT 2
        2019-06-10T09:00:00Z ContosoRetail-IT 2
        2019-06-09T18:00:00Z ContosoRetail-IT 2
        2019-06-09T15:00:00Z ContosoRetail-IT 2
        2019-06-04T15:00:00Z ContosoRetail-IT 2
        2019-06-10T13:00:00Z ContosoRetail-IT 2
        2019-06-03T18:00:00Z ContosoRetail-IT 2
        2019-06-05T22:00:00Z ContosoRetail-IT 2
        2019-06-04T17:00:00Z ContosoRetail-IT 2
        2019-06-10T00:00:00Z ContosoRetail-IT 2
        2019-06-05T08:00:00Z ContosoRetail-IT 2
        2019-06-07T23:00:00Z ContosoRetail-IT 2
        2019-06-05T01:00:00Z ContosoRetail-IT 2
        2019-06-04T13:00:00Z ContosoRetail-IT 2
        2019-06-04T01:00:00Z ContosoRetail-IT 2
        2019-06-09T04:00:00Z ContosoRetail-IT 2
        2019-06-09T23:00:00Z ContosoRetail-IT 2
        2019-06-07T05:00:00Z ContosoRetail-IT 2

         

         

    • vishalkakkar's avatar
      vishalkakkar
      Copper Contributor
      Jun 10, 2019

      CliveWatson 

       

      Please find below screenshot, am tying to concatenate logs from two different workspaces.

      So there is one more workspace which i need to add to concetenate logs from two different workspace

       

      -VIshal Kakkar

       

       

Resources