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 !
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 !
