Forum Discussion

Deleted's avatar
Deleted
Apr 25, 2019
Solved

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...
azure monitor
Query Language
  • CliveWatson's avatar
    CliveWatson
    Apr 29, 2019

    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's avatar
Deleted
Apr 29, 2019

CliveWatson 

First 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_time

The 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.

CliveWatson's avatar
CliveWatson
Former Employee
Apr 29, 2019

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

Resources