Web Traffic data hunt

[exigentcircumstance]

Trying to write a script that will show me all internet/web traffic data for a specific person/machine for a specific amount of a time range.

Example: I want to see what Joe Smith did for the past 30 days with web searching.

Can't seem to find anything that will fit this nor can I seem to write a usable script in Defender.

Can anyone help? Please :)

[cheapbyte]

Try this.

// This query finds all network communication for a specific userID and Date Range and displays the Process communicating
// Modify Line 8 with the UserID you want a report on or user Partial if you are not sure
//
let TimeRangeStart = datetime(2020-04-29 20:50:01); //Fill in the start date and time
let TimeRangeEnd = datetime(2020-05-01 20:50:00); // Fill in the end date and time
// If running interactive, set the date range to 30 days or custom to cover the date range you have in the script, otherwise if running in a process, the timerange will work regardless
let partialRemoteUrlToDetect = ""; // Change this to a URL you'd like to find machines connecting to or leave blank for everything
//
DeviceNetworkEvents
| where InitiatingProcessAccountName contains "UserID" //enter the full or partial userID
| where Timestamp between ((TimeRangeStart) ..TimeRangeEnd)
and RemoteUrl has partialRemoteUrlToDetect // Can be changed to "contains" operator as explained above
| project Timestamp, InitiatingProcessAccountName, RemoteUrl, InitiatingProcessParentFileName, DeviceName, DeviceId, ReportId
// | top 1000 by RemoteUrl desc // Remove the first two slashes if you want just the top 1000

[JakKAaj]

I am struggling to create a query to match https://urlhaus.abuse.ch/downloads/text/ or https://urlhaus.abuse.ch/downloads/text_recent/
with DeviceNetworkEvents RemoteUrl

Can you help ?

[tali-ash]

Hi JakKAaj,

You can use this example:

EmailAttachmentInfo
| where SHA256 in (externaldata(TimeGenerated:datetime, SHA256:string)
[@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Microsoft.Covid19.Indicators.csv"]
with (format="csv"))

Using externaldata operator you can use list from external source in your query.
You need to save the url lists in csv/txt format(or any format supported by externaldata).

https://docs.microsoft.com/en-us/microsoft-365/security/mtp/advanced-hunting-best-practices?view=o365-worldwide#ingest-data-from-external-sources

[JakKAaj]

Hi, Thank you, last bit for me is to compare the url from web to format found in MDATP: $left.payload_url==$right.RemoteUrl How we can use contain or has to compare ? let urlhaus_recent = (externaldata(payload_url: string ) [@"https://urlhaus.abuse.ch/downloads/text_recent/"] with (format="txt")) | project payload_url; urlhaus_recent | join (DeviceEvents | where Timestamp > ago(24h) ) on $left.payload_url==$right.RemoteUrl |project Timestamp, payload_url, RemoteIP, DeviceName, ActionType, RemoteUrl, InitiatingProcessFileName From: tali-ash <[email protected]> Sent: Tuesday, November 3, 2020 1:40 PM To: microsoft/Microsoft-365-Defender-Hunting-Queries <[email protected]> Cc: Michal Kalisz - QIAGEN <[email protected]>; Comment <[email protected]> Subject: Re: [microsoft/Microsoft-365-Defender-Hunting-Queries] Web Traffic data hunt (#111) Caution: This email originated from outside QIAGEN. Hi JakKAaj, You can use this example: EmailAttachmentInfo | where SHA256 in (externaldata(TimeGenerated:datetime, SHA256:string) [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Microsoft.Covid19.Indicators.csv"] with (format="csv")) Using externaldata operator you can use list from external source in your query. You need to save the url lists in csv format(or any format supported by externaldata). https://docs.microsoft.com/en-us/microsoft-365/security/mtp/advanced-hunting-best-practices?view=o365-worldwide#ingest-data-from-external-sources<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fmicrosoft-365%2Fsecurity%2Fmtp%2Fadvanced-hunting-best-practices%3Fview%3Do365-worldwide%23ingest-data-from-external-sources&data=04%7C01%7Cmichal.kalisz%40qiagen.com%7Ca3d57e70813a4f2467dd08d87ff59c05%7Cdc81d03c239c4fd5a96f18a58773c86c%7C0%7C0%7C637400040166822796%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=PfISEPAMMAW9RotM2mP%2Fv%2BOyrq54ZsNDvhkWaWB%2BtBc%3D&reserved=0> — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fmicrosoft%2FMicrosoft-365-Defender-Hunting-Queries%2Fissues%2F111%23issuecomment-721091876&data=04%7C01%7Cmichal.kalisz%40qiagen.com%7Ca3d57e70813a4f2467dd08d87ff59c05%7Cdc81d03c239c4fd5a96f18a58773c86c%7C0%7C0%7C637400040166822796%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=MLS1MQvHsmVJMK0N9iuWCYq06UUhgu0z7qIJwrPGPMs%3D&reserved=0>, or unsubscribe<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FANCNGCOYSAE3EV7KDZRXBETSN722PANCNFSM4MIROLTQ&data=04%7C01%7Cmichal.kalisz%40qiagen.com%7Ca3d57e70813a4f2467dd08d87ff59c05%7Cdc81d03c239c4fd5a96f18a58773c86c%7C0%7C0%7C637400040166822796%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=y%2Fg1tmzG0kPZo8GaWQcf4oH8u%2FebSSsVmW%2F7QclCANc%3D&reserved=0>.

[mjmelone]

Good morning all,
Here's how I would likely approach the issue. First, parse the URLs from urlhaus into their individual parts using the parse_url() function:

(externaldata(payload_url: string ) [@"https://urlhaus.abuse.ch/downloads/text_recent/"]
with (format="txt"))
| extend ParsedUrl = parse_url(payload_url)
| evaluate bag_unpack(ParsedUrl)
| extend Port = case(isnotempty(Port), Port, Scheme == 'http', "80", Scheme == 'https', "443", Port)

...once you have that, we should probably try to parse out the DeviceNetworkEvents in the same manner for matching:

DeviceNetworkEvents
| extend ParsedUrl = iff(RemoteUrl matches regex @'^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}$', todynamic(strcat('{"Host":"', tolower(RemoteUrl), '"}')), parse_url(RemoteUrl))
| evaluate bag_unpack(ParsedUrl)

...and now put them together with some joins to perform comparisons. To achieve this, you'll need to use a function such as mvexpand followed by a comparison operator.
Also, as a former incident responder, I would probably be a bit more paranoid than most and look for simple IP matches as well as FQDN matches. Here we go:

let urlhausurls = toscalar((externaldata(payload_url: string ) [@"https://urlhaus.abuse.ch/downloads/text_recent/"]
with (format="txt"))
| extend ParsedUrl = parse_url(payload_url)
| evaluate bag_unpack(ParsedUrl)
| extend Port = case(isnotempty(Port), Port, Scheme == 'http', "80", Scheme == 'https', "443", Port)
| extend Packed = pack_all()
| summarize makelist(Packed));
DeviceNetworkEvents
| extend ParsedUrl = iff(RemoteUrl matches regex @'^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}$', todynamic(strcat('{"Host":"', tolower(RemoteUrl), '"}')), parse_url(RemoteUrl))
| evaluate bag_unpack(ParsedUrl)
| extend UrlHaus = urlhausurls
| mvexpand UrlHaus 
| evaluate bag_unpack(UrlHaus, 'UrlHaus_')
| extend HostAndPortMatch = ((RemoteIP == UrlHaus_Host or Host == UrlHaus_Host) and RemotePort == UrlHaus_Port), UriStemMatch = (isnotempty(UrlHaus_Path) and Path == UrlHaus_Path)
| where HostAndPortMatch == true

Let me know if that works for you

[JakKAaj]

Hi, Thanks, I can see the gap now. I have run a test on one of the URLs from urlhaus list but there was no match. I can’t find where is the problem. From: Michael Melone <[email protected]> Sent: Monday, November 16, 2020 5:31 PM To: microsoft/Microsoft-365-Defender-Hunting-Queries <[email protected]> Cc: Michal Kalisz - QIAGEN <[email protected]>; Comment <[email protected]> Subject: Re: [microsoft/Microsoft-365-Defender-Hunting-Queries] Web Traffic data hunt (#111) Caution: This email originated from outside QIAGEN. Good morning all, Here's how I would likely approach the issue. First, parse the URLs from urlhaus into their individual parts using the parse_url() function: (externaldata(payload_url: string ) [@"https://urlhaus.abuse.ch/downloads/text_recent/"] with (format="txt")) | extend ParsedUrl = parse_url(payload_url) | evaluate bag_unpack(ParsedUrl) | extend Port = case(isnotempty(Port), Port, Scheme == 'http', "80", Scheme == 'https', "443", Port) ...once you have that, we should probably try to parse out the DeviceNetworkEvents in the same manner for matching: DeviceNetworkEvents | take 100 | extend ParsedUrl = iff(RemoteUrl matches regex @'^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}$', todynamic(strcat('{"Host":"', tolower(RemoteUrl), '"}')), parse_url(RemoteUrl)) | evaluate bag_unpack(ParsedUrl) ...and now put them together with some joins to perform comparisons. To achieve this, you'll need to use a function such as mvexpand followed by a comparison operator. Also, as a former incident responder, I would probably be a bit more paranoid than most and look for simple IP matches as well as FQDN matches. Here we go: let urlhausurls = toscalar((externaldata(payload_url: string ) [@"https://urlhaus.abuse.ch/downloads/text_recent/"] with (format="txt")) | extend ParsedUrl = parse_url(payload_url) | evaluate bag_unpack(ParsedUrl) | extend Port = case(isnotempty(Port), Port, Scheme == 'http', "80", Scheme == 'https', "443", Port) | extend Packed = pack_all() | summarize makelist(Packed)); DeviceNetworkEvents | take 100 | extend ParsedUrl = iff(RemoteUrl matches regex @'^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}$', todynamic(strcat('{"Host":"', tolower(RemoteUrl), '"}')), parse_url(RemoteUrl)) | evaluate bag_unpack(ParsedUrl) | extend UrlHaus = urlhausurls | mvexpand UrlHaus | evaluate bag_unpack(UrlHaus, 'UrlHaus_') | extend HostAndPortMatch = ((RemoteIP == UrlHaus_Host or Host == UrlHaus_Host) and RemotePort == UrlHaus_Port), UriStemMatch = (isnotempty(UrlHaus_Path) and Path == UrlHaus_Path) | where HostAndPortMatch == true Let me know if that works for you — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fmicrosoft%2FMicrosoft-365-Defender-Hunting-Queries%2Fissues%2F111%23issuecomment-728173650&data=04%7C01%7Cmichal.kalisz%40qiagen.com%7C4cd79a789e6345f7ee5008d88a4d061f%7Cdc81d03c239c4fd5a96f18a58773c86c%7C0%7C0%7C637411410714879099%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=YTZY4NjtrlcG8I0HFEh6OG6hkL8fG0Q7MxXeRP%2FFfSw%3D&reserved=0>, or unsubscribe<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FANCNGCKTAP7FXNXJ6GJEVATSQFHUXANCNFSM4MIROLTQ&data=04%7C01%7Cmichal.kalisz%40qiagen.com%7C4cd79a789e6345f7ee5008d88a4d061f%7Cdc81d03c239c4fd5a96f18a58773c86c%7C0%7C0%7C637411410714879099%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=TK%2Fg6rGbwY7ZjfUVZXWs1rrcrqMxNL1Y7T3ATkyKm0I%3D&reserved=0>.

[mjmelone]

One slight modification - using case insensitive equals

let urlhausurls = toscalar((externaldata(payload_url: string ) [@"https://urlhaus.abuse.ch/downloads/text_recent/"]
with (format="txt"))
| extend ParsedUrl = parse_url(payload_url)
| evaluate bag_unpack(ParsedUrl)
| extend Port = case(isnotempty(Port), Port, Scheme == 'http', "80", Scheme == 'https', "443", Port)
| extend Packed = pack_all()
| summarize makelist(Packed));
DeviceNetworkEvents
| extend ParsedUrl = iff(RemoteUrl matches regex @'^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}$', todynamic(strcat('{"Host":"', tolower(RemoteUrl), '"}')), parse_url(RemoteUrl))
| evaluate bag_unpack(ParsedUrl)
| extend UrlHaus = urlhausurls
| mvexpand UrlHaus 
| evaluate bag_unpack(UrlHaus, 'UrlHaus_')
| extend HostAndPortMatch = ((RemoteIP == UrlHaus_Host or Host =~ UrlHaus_Host) and RemotePort == UrlHaus_Port), UriStemMatch = (isnotempty(UrlHaus_Path) and Path =~ UrlHaus_Path)
| where HostAndPortMatch == true

[JakKAaj]

Not sure why but the query time out in WDATP.