Forum Discussion

HA13029's avatar
HA13029
Brass Contributor
Feb 14, 2025
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...
analytics
KQL
  • HA13029's avatar
    HA13029
    Feb 17, 2025

    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 !

luchete's avatar
luchete
Steel Contributor
Feb 14, 2025

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.

  • HA13029's avatar
    HA13029
    Brass Contributor
    Feb 14, 2025

    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

     

    • luchete's avatar
      luchete
      Steel Contributor
      Feb 14, 2025

      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!

      • HA13029's avatar
        HA13029
        Brass Contributor
        Feb 17, 2025

        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 !

Resources