Forum Discussion
Can you help me in this query
let minTimeRange = ago(7d);
let outlookLinks =
DeviceEvents
| where Timestamp > minTimeRange and ActionType == "BrowserLaunchedToOpenUrl" and
isnotempty(RemoteUrl)
| where
InitiatingProcessFileName =~ "outlook.exe"
or InitiatingProcessFileName =~ "runtimebroker.exe"
| project Timestamp , DeviceId , DeviceName , RemoteUrl, InitiatingProcessFileName,
ParsedUrl=parse_url(RemoteUrl)
| extend WasOutlookSafeLink=(tostring(http://ParsedUrl.Host) endswith "http://safelinks.protection.outlook.com")
| project Timestamp , DeviceId, DeviceName , WasOutlookSafeLink,
InitiatingProcessFileName,
OpenedLink=iff(WasOutlookSafeLink, url_decode(tostring(ParsedUrl["QueryParameters"]["url"])), RemoteUrl);
let alerts =
AlertInfo
| summarize (FirstDetectedActivity, Title)=argmin(Timestamp,Title) by AlertId,
| where FirstDetectedActivity > minTimeRange;
alerts
| join kind=inner (outlookLinks) on DeviceId
| where FirstDetectedActivity -
Timestamp between (0min..3min)
| summarize FirstDetectedActivity=min(FirstDetectedActivity),
AlertTitles=makeset(Title) by OpenedLink, InitiatingProcessFileName,
EventTime=bin(Timestamp, 1tick), DeviceName, DeviceId , WasOutlookSafeLink
links opened from outlook.exe, followed by warning that was ignored by the user.
let outlookLinks =
DeviceEvents
| where Timestamp > minTimeRange and ActionType == "BrowserLaunchedToOpenUrl" and
isnotempty(RemoteUrl)
| where
InitiatingProcessFileName =~ "outlook.exe"
or InitiatingProcessFileName =~ "runtimebroker.exe"
| project Timestamp , DeviceId , DeviceName , RemoteUrl, InitiatingProcessFileName,
ParsedUrl=parse_url(RemoteUrl)
| extend WasOutlookSafeLink=(tostring(http://ParsedUrl.Host) endswith "http://safelinks.protection.outlook.com")
| project Timestamp , DeviceId, DeviceName , WasOutlookSafeLink,
InitiatingProcessFileName,
OpenedLink=iff(WasOutlookSafeLink, url_decode(tostring(ParsedUrl["QueryParameters"]["url"])), RemoteUrl);
let alerts =
AlertInfo
| summarize (FirstDetectedActivity, Title)=argmin(Timestamp,Title) by AlertId,
| where FirstDetectedActivity > minTimeRange;
alerts
| join kind=inner (outlookLinks) on DeviceId
| where FirstDetectedActivity -
Timestamp between (0min..3min)
| summarize FirstDetectedActivity=min(FirstDetectedActivity),
AlertTitles=makeset(Title) by OpenedLink, InitiatingProcessFileName,
EventTime=bin(Timestamp, 1tick), DeviceName, DeviceId , WasOutlookSafeLink
links opened from outlook.exe, followed by warning that was ignored by the user.
- Tali Ash
Microsoft
Hello Shviam ,
We are parsing the Safe Links urls for you
Therefore in the first part of the query, you don't need to parse the url:
- In the column RemoteUrl we are giving you the actual target url, that in case of Safe Links is "behind" the Safe Links url.
- In AdditionalFields you will have the Safe link complete url, in case this link was a Safe Links url. Using: | extend SafeLinksUrl = tostring(parse_json(AdditionalFields)["SafeLinksUrl"]), you can extract it and get the complete url. In case the url was not a Safe Links, SafeLinksUrl will be null.
An example :
I adjusted this part of the query a bit and it is working now:let outlookLinks =DeviceEvents| where Timestamp > minTimeRange and ActionType == "BrowserLaunchedToOpenUrl" and isnotempty(RemoteUrl)| where InitiatingProcessFileName =~ "outlook.exe" or InitiatingProcessFileName =~ "runtimebroker.exe"| extend SafeLinksUrl = tostring(parse_json(AdditionalFields)["SafeLinksUrl"])| project Timestamp , DeviceId , DeviceName , OpenedLink = RemoteUrl, InitiatingProcessFileName, SafeLinksUrl;I was not sure what you were trying to do with alerts? If you can please clarify I can help with the rest of the query.Thanks,TaliTali AshThanks a lot
I'm trying to create an analytic rule for which user clicked on the malicious link and they got the warning sign" The URL is in an email message that seems similar to other email messages that are considered suspicious. We recommend that you double-check the email message before proceeding to the site."
- Tali AshWe are working to onboard url clicks data from Office365 into advanced hunting, so you will be able to see if a url was clicked and the verdict of the url at the time of the click. Thanks, Tali
