Forum Discussion

abk92394's avatar
abk92394
Copper Contributor
Apr 06, 2022

Searching Historical Logs for Threat Intelligence Matches.

Hello all,

 

I was just wondering what the best or most efficient way to search logs for threat intelligence IOCs was, I saw a previous post explaining how to do it if you would like to search a large amount of values via watchlist but I would like to do it only for threat intelligence IOCs, I have a search below that works for IP addresses and can also be applied to file hashes. 

 

ThreatIntelligenceIndicator
| where isnotempty(NetworkIP)
| summarize by ThreatIntelIP=NetworkIP
| join
(
Network_MetaParser
| where isnotempty(SrcIpAddr)
| summarize by SrcIpAddr, DstIpAddr, EventProduct, DvcAction, DstPortNumber, NetworkProtocol, TimeGenerated
) on $left.ThreatIntelIP == $right.DstIpAddr

 

My question is regarding URL/Domain names. How do I search my logs for any URLs/domains that match or contain the URL/Domain values from threat intelligence. I've tried doing something like the below but it doesn't seem to work. Any suggestions would be greatly appreciated!

 

| summarize by URL
| where isnotempty(URL)

| where URL has_any (ThreatIntelligenceIndicator)

KQL
kusto
Log Data
siem
threat hunting
  • JBUB_Accelerynt's avatar
    JBUB_Accelerynt
    Brass Contributor
    Apr 07, 2022

    Here is something we made a long time ago but should still work as a template.

    let dt_lookBack = 7d;
    let ioc_lookBack = 14d;
    ThreatIntelligenceIndicator
    | where TimeGenerated > ago(ioc_lookBack)
    | where isnotempty(DomainName)
    | join (
    CommonSecurityLog
    | where TimeGenerated > ago(dt_lookBack)
    | where isnotempty(DestinationHostName)
    | extend CommonSecurityLog_TimeGenerated = TimeGenerated
    )
    on $left.DomainName == $right.DestinationHostName
    | project SourceUserName, DomainName, CommonSecurityLog_TimeGenerated, Description, Activity
    | extend AccountCustomEntity = SourceUserName
    | extend HostCustomEntity = DomainName
    | extend URLCustomEntity = DomainName

  • Jonhed's avatar
    Jonhed
    Steel Contributor
    Apr 07, 2022

    abk92394 

    See if this works.

     

    First, define a variable that will contain a list of all the URLs in the threat indicator table.

     

     

    let TIUrls = toscalar(ThreatIntelligenceIndicator
| summarize make_set(Url));

     

     

     

    Then, you use mv-apply to compare the URL value against each indicator in the variable above.

     

     

    Table //Table that contains the traffic logs
| summarize by URL
| where isnotempty(URL)
| mv-apply l=TIUrls to typeof(string) on
(where URL contains l)

     

     

     

    • abk92394's avatar
      abk92394
      Copper Contributor
      Apr 07, 2022

      Jonhed 

       

      Thanks for you suggestion. I tried it and it works in a sense. It doesn't throw any errors, but I need to know which URLs from the Threat Intelligence list was matched, is there a way to output them side by side, kind of like the Join operator.

      • Jonhed's avatar
        Jonhed
        Steel Contributor
        Apr 08, 2022

        abk92394 

        If you want to compare identical columns, the template JBUB_Accelerynt posted looks fine.

         

        If you want to check your logs against a list of URLs/DomainNames, then you would probably need to use the solution I listed.

        When using mv-apply, the matched indicator URLs will be stored in the l column, when there are matches.

        If you want to show all the info of the entry in the Threat indicator table, you can add the join bit below.

        let TIUrls = toscalar(ThreatIntelligenceIndicator
| summarize make_set(Url));
| mv-apply l=TIUrls to typeof(string) on
(where URL contains l)
| join ThreatIntelligenceIndicator on $left.l=$right.Url

         

        The query I created is comparing with the Url field of the threat intelligence indicators, but if you want to compare with DomainNames instead, you can edit row 2 and 5, and change the "Url" for "DomainName".

        let TIUrls = toscalar(ThreatIntelligenceIndicator
| summarize make_set(DomainName));
| mv-apply l=TIUrls to typeof(string) on
(where URL contains l)
| join ThreatIntelligenceIndicator on $left.l=$right.DomainName

         

Resources