Getting Logs data across different components/ name

[vishalkakkar]

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 level logs

[#2]

@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

[#3]

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

[#4]

@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

[#5]

@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

[#6]

@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

[#7]

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