Query Details
Risky Sign In After Tweetfeed URL IP Click
Query
//This Query hunts for Risky Sign in after the Click to a URL or IP listed on Tweetfeed IOC
let TweetFeedM = externaldata(Datetime: string, Src: string, Type: string, Indicator: string, Desc: string)["https://raw.githubusercontent.com/0xDanielLopez/TweetFeed/master/month.csv"];
// Extract the URL from IOC Feed
let TweetFeedURL = TweetFeedM
| where Type == "url";
// Extract IPs from IOC Feed
let TweetFeedIP = TweetFeedM
| where Type == "ip";
// Test for Hits on URL
let HitsURL = UrlClickEvents
| join kind = inner TweetFeedURL on $left.Url == $right.Indicator
| project Timestamp, AccountUpn, Url, IPAddress;
// Test for Hits on IPs
let HitsIP = UrlClickEvents
| join kind = inner TweetFeedIP on $left.IPAddress == $right.Indicator
| project Timestamp, AccountUpn, Url, IPAddress;
// join Sign in Events with RiskLevel not empty
HitsURL
| union HitsIP
| join kind = inner AADSignInEventsBeta on $left.AccountUpn == $right.AccountUpn
| where Timestamp > Timestamp1
| where isnotempty(RiskLevelDuringSignIn) Explanation
This query is designed to identify potentially risky sign-in activities that occur after a user clicks on a URL or IP address listed in a threat intelligence feed (Tweetfeed IOC). Here's a simplified breakdown of what the query does:
-
Import Threat Intelligence Data: It imports data from an external source (a CSV file hosted on GitHub) that contains indicators of compromise (IOCs), such as URLs and IP addresses associated with potential threats.
-
Separate URLs and IPs: The data is filtered into two separate sets: one containing URLs and the other containing IP addresses.
-
Identify Click Events on Threat URLs: It checks for any events where users clicked on URLs that match those in the threat feed. This is done by joining the URL click events with the threat URL list.
-
Identify Click Events on Threat IPs: Similarly, it checks for events where users clicked on IP addresses that match those in the threat feed by joining the IP click events with the threat IP list.
-
Combine and Analyze Risky Sign-Ins: The results from both URL and IP click events are combined. These combined results are then joined with Azure Active Directory (AAD) sign-in events to find instances where the same user account had a sign-in event with a non-empty risk level after clicking on a threat-listed URL or IP.
In summary, the query is hunting for user sign-ins that are flagged as risky and occur after the user has interacted with potentially malicious URLs or IPs from a threat intelligence feed.
Details
Benjamin Zulliger
Released: November 10, 2024
Tables
Keywords
Operators