Forum Discussion
How to query for two events with no common fields?
Hi,
I know one can query for two events that have a common field by the Join Operator, but how can I query two or more separate events that have no common fields?
I'm trying to track a couple of activities that have no common fields in one query.
Thank you.
Deleted
Thanks, that extra Table name and info really helps, I didn't have any 'ALLOW' activities, so line 5 will need changing to match your test. Hopefully this change is easy to understand, essentially I've created a variable to store when we get a success/match in either table and JOIN on that.
Tip: best practice is to use HAS rather than CONTAINS where possible, source.
AzureDiagnostics |where TimeGenerated > ago(1d) |where Category == "AzureFirewallNetworkRule" |where OperationName == "AzureFirewallNetworkRuleLog" |where msg_s startswith "TCP request from " and msg_s endswith "Action: Deny" // change this line back - used to test |project Category , OperationName , msg_s , set_flag =1 // set a flag when we find a match |join (SecurityEvent | where Activity == "4688 - A new process has been created." | where Process == "diskperf.exe" | project Activity ,Computer ,Process , set_flag =1 // set a flag when we find a match ) on set_flag // join on that flag
7 Replies
Deleted
Something like:
Alert | project AlertName, round_time=bin(TimeGenerated, 1m) | join (Perf | project Computer, round_time=bin(TimeGenerated, 1m)) on round_timeI use a rounded down timegenerated (as that is in both Tables)
I'm sorry but I'm new to this, can you please elaborate on that or send me a link to some docs. How can this code help me query for two or more events in one query?
Thank you.
Deleted
Perhaps I'm not fully understanding the question. You asked to Join two activities without a common field. When you say events we would assume the Events table in Log Analytics, and by Join I assumed the join operator . What do you mean my Events (do you mean an event in a table or row)?
Which table are you referring to?
A single Table will have always have common fields. My example shows a join across two seperate tables, where their isn't a common field.
Are you asking about two Event IDs, in a table. For example. The Events table?
// count all events that are 7036 and show their description Event | where TimeGenerated > ago(1d) | where EventID == 7036 | summarize count() by EventID, RenderedDescription
Showing two events, would be
// count all events that are 7036 or 7040 and show their description Event | where TimeGenerated > ago(1d) | where EventID in ( 7036, 7040) | summarize count() by EventID
Or are you trying to join pieces of data in a single table?
Perf | where TimeGenerated > ago(10d) | where CounterName == "% Processor Time" | project CounterName, Computer, TimeGenerated // added this to increase performance as "Use where and project to reduce the numbers of rows and columns in the input tables, before the join. " | join kind= inner ( Perf | where CounterName == "Processor Queue Length" | project CounterName , TimeGenerated , Computer ) on TimeGenerated , ComputerPerhaps, you can send what you have so far, for us to look at? If we can see a screenshot or pseudo code, with expected output that would help.
e.g.
"I'm looking at the Events table, I want to show if there are entries from a user called "Local Service" or "System". I want to count how many times this happens." Where an activity is a value of "nnnnn"
Event | where TimeGenerated > ago(1d) | where UserName has "LOCAL SERVICE" or UserName has "SYSTEM" | summarize count() by UserName
The main docs page is here: https://docs.microsoft.com/en-us/azure/kusto/query/
Thanks