Forum Discussion
KQL to match URL FW LOGS and Threatfox URL feeds
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.icu/gkcxv.google"
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 20
You 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.