Sentinel Analytical Rule Issue: No Results for 24-hour Time Range with Cisco Firewall Events Join

Question

I have an issue with an analytical rule configured in Sentinel. This rule targets events from a Cisco firewall sourced from the CommonSecurityLog table. Using an inner join function, it searches for the source IP addresses logged in the firewall events and compares them with the ThreatIntelligenceIndicator table to identify connections originating from malicious IPs.

    CommonSecurityLog
| join kind=inner ThreatIntelligenceIndicator on $left.SourceIP == $right.NetworkIP
| where SourceIP == NetworkIP
| where DeviceVendor == 'Cisco' and DeviceProduct == 'Firepower'
| where DeviceAction == 'Allow'
| where ipv4_is_private(SourceIP) == false
| where ipv4_is_private(DestinationIP) == true
| project TimeGenerated, DeviceAction, SourceIP, DestinationIP, DestinationPort

The problem is that no records are displayed even though related events have been generated. Today is October 15th, and when I run the query with a time range set to 24 hours, no results are shown. However, when the range is set to 7 days, records do appear, including events from within the past 24 hours. I suspect the issue might lie within the inner join, as when I comment out the join and display only the firewall events, they are displayed without any issues.

Answer 1

I'm not sure if you ever figured this out. But, have you compared receipttime vs timegenerated? We're seeing that Sentinel/Log analytics is seemingly doing a 12 hour conversion (am/pm) on timegenerated. But, your logs in the other table are in UTC format (24 hr).