Forum Discussion
How to query for two events with no common fields?
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
Deleted
Something like:
Alert
| project AlertName, round_time=bin(TimeGenerated, 1m)
| join (Perf
| project Computer,
round_time=bin(TimeGenerated, 1m))
on round_time
I 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
Thank you for the reply and I apologize for my lack of clarity.
I need to query if two events accrued within 24hrs.
One is a FireWall event of a connection being made to a certain external IP.
The second event is a SecurityEvent of a certain process being executed on any machine in the network (so I don't have common fields like machine name or IPs).
I can query them each individually, but how can I check if they both accrued in the last 24hrs in one query?