Forum Discussion
Solved
KQL to match URL FW LOGS and Threatfox URL feeds
Hi all, I try to match RequestURL field (in CommonSecurityLog) from a Fortigate FW with URL Haus live feeds. The query does not produce any errors but it doesn't match anything. let ThreatFox = ex...
Hello,
Finally got it !
let ThreatFox = externaldata(URL: string, Data:string ) ["https://threatfox.abuse.ch/export/csv/recent/"] with (format="txt", ignoreFirstRecord=True);
let ThreatFoxUrl = ThreatFox
| where URL contains "url"
| extend URL = replace_string(URL, "\"", "")
| extend URL = replace_string(URL, "\x20", "")
| extend parse_csv(URL)
| extend URL = URL[2];
CommonSecurityLog
| where isnotempty(RequestURL)
| where RequestURL has_any (ThreatFoxUrl)Many thanks for your help !
Hi HA13029,
The issue likely comes from how you're parsing the URL from the ThreatFox feed. In your query, after parsing the CSV data, you're using extend URL = URL[2], which might not be extracting the correct URL format. You might want to adjust how the CSV is parsed and ensure that the URL field you're matching in the CommonSecurityLog is formatted correctly for comparison.
Also, using has_any with a list of URLs might not be working as expected if the URLs in ThreatFoxUrl are not being processed correctly.
Try simplifying or adjusting your query for better matching, especially how you handle the URL extraction and make sure the field types align between the two datasets.
Hello,
I think you're right.
After saving the result of the query in a text file and opened it, I get the following content.
URL
" https://check.gesom.icu/gkcxv.google"
But I don'k known how I can remove the quote from the KQL query...
Regards,
HA
To remove the quotes from the URL in your KQL query, you can modify your query to replace the quote characters using replace_string(). You can use this function to remove both the opening and closing quotes from the URL string.
You can try something like this one:
let ThreatFoxUrl = ThreatFox | where URL contains "url" | extend URL = replace_string(replace_string(URL, "\"", ""), "\"", "") | extend parse_csv(URL) | extend URL = URL[2];This way, the replace_string() function will remove both instances of the quote characters.
Let me know how it goes
Regards!
Hello,
Finally got it !
let ThreatFox = externaldata(URL: string, Data:string ) ["https://threatfox.abuse.ch/export/csv/recent/"] with (format="txt", ignoreFirstRecord=True);
let ThreatFoxUrl = ThreatFox
| where URL contains "url"
| extend URL = replace_string(URL, "\"", "")
| extend URL = replace_string(URL, "\x20", "")
| extend parse_csv(URL)
| extend URL = URL[2];
CommonSecurityLog
| where isnotempty(RequestURL)
| where RequestURL has_any (ThreatFoxUrl)Many thanks for your help !
Hello,
First, many tanks for your help.
I think I found the issue (not the solution..).
When exporting the result to a file, we can see space character is the beginning of the string just after the quote.
This black character needs to be removed
URL
" https://check.qejym.xxxx/test"My query is
let ThreatFox = externaldata(URL: string ) ["https://threatfox.abuse.ch/export/csv/recent/"] with (format="txt", ignoreFirstRecord=True);
let ThreatFoxUrl = ThreatFox
| where URL contains "url"
| extend URL = replace_string(replace_string(URL, "\"", ""), "\"", "")
| extend parse_csv(URL)
| extend URL = URL[2];
ThreatFoxUrl
| take 20You can use trim() in your query to remove any leading or trailing whitespace. This will clean up any stray space characters, including the one you're encountering at the beginning of the URL.
You can modify your query to something like this:
let ThreatFox = externaldata(URL: string) ["https://threatfox.abuse.ch/export/csv/recent/"] with (format="txt", ignoreFirstRecord=True); let ThreatFoxUrl = ThreatFox | where URL contains "url" | extend URL = replace_string(replace_string(URL, "\"", ""), "\"", "") | extend URL = trim(" ", URL) // This removes any leading or trailing spaces | extend parse_csv(URL) | extend URL = URL[2]; ThreatFoxUrl | take 20in this case the trim(" ", URL) function will ensure that any spaces before or after the URL are removed.
