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
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 , Computer
Perhaps, 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?
Deleted
Not a problem, I think I was close with my initial answer but used some example tables (not the ones you wanted).
Example 1
Here, in example code 1, we look at the last 24hrs, and select "cscript.exe" as an example process name (change to match yours). I also look for a specific activity - again change this to suit. We then use a project operator to only keep three columns - you may need more. The last column is round_time, which takes a data/time which we use to join on later (rounded to 1m intervals - as we are unlikely to get a match on less than that)!
I then join the SecurityEvents table using that round_time, to WindowsFirewall table. In addition we filter on a specific RemoteIP address (please change for one that matches yours). You didn't mention which Firewall, so you might have that data in a table called CommonSecurityLog - in that case change WindowsFirewall to CommonSecurityLog.
SecurityEvent | where TimeGenerated > ago(1d) // filter in last 24hrs | where Process =="cscript.exe" // find a process | where Activity == "4688 - A new process has been created." // look for "a new process has been created" eventid | project Computer, Process, round_time=bin(TimeGenerated, 1m) // set a value for time to enable a JOIN | join (WindowsFirewall | where RemoteIP == "192.168.137.1" // find a specifc IP in the Windows Firewall logs | project Computer, RemoteIP , // only project two columns round_time=bin(TimeGenerated, 1m) // set time to match join ) on round_timeYou can run the above from here in the demo Log Analytics portal.
Output
Example 2:
SecurityEvent, WindowsFirewall and CommonSecurityLog - do all have common fields, so we could have just joined on Computer. I don't know which firewall table you are looking at, you are using so this second example may not work in your case.
SecurityEvent | where TimeGenerated > ago(1d) // filter in last 24hrs | where Process =="cscript.exe" // find a process | where Activity == "4688 - A new process has been created." // look for "a new process has been created" eventid | project Computer, Process, Activity // set a value for time to enable a JOIN | join (WindowsFirewall | where RemoteIP == "192.168.137.1" // find a specifc IP in the Windows Firewall logs | project Computer, RemoteIP ) on ComputerFirst of all Thank You, I really appreciate your time and effort in helping me.
I think My problem is that unlike the cscript.exe file (which generates an Event almost every minute) the file I'm looking for may only be generated once in 24h, And will most likely be completely out of sync with the Firewall event (So the round_time will almost never be the same in both events).
As I understand, this is why when I run your query it works, but when I run mine I get nothing,
AzureDiagnostics
|where TimeGenerated > ago(1d)
|where Category == "AzureFirewallNetworkRule"
|where OperationName == "AzureFirewallNetworkRuleLog"
|where msg_s contains "to *******. Action: Allow"
|project Category , OperationName , msg_s , round_time=bin(TimeGenerated, 1m)
|join
(SecurityEvent
| where Activity == "4688 - A new process has been created."
| where Process == "******.exe"
| project Activity ,Computername ,Process , round_time=bin(TimeGenerated, 1m)
) on round_timeThe source computer\IP in the Firewall event is irrelevant to the SecurityEvent as the SecurityEvent can occur on any computer in the network at any given time after the initial outbound connection.
Thank you.