Jun 06 2019
08:53 AM
- last edited on
Apr 07 2022
05:51 PM
by
TechCommunityAP
Jun 06 2019
08:53 AM
- last edited on
Apr 07 2022
05:51 PM
by
TechCommunityAP
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
Jun 06 2019 12:28 PM
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
Jun 06 2019 12:47 PM
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
Jun 06 2019 04:04 PM
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
Jun 10 2019 10:21 AM
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
Jun 10 2019 10:21 AM
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
Jun 10 2019 10:52 AM
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 |