SOLVED

Historical IOC searches

%3CLINGO-SUB%20id%3D%22lingo-sub-2555535%22%20slang%3D%22en-US%22%3EHistorical%20IOC%20searches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2555535%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everybody%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20interested%20to%20understand%20how%20others%20approach%20this%20and%20what%20is%20perhaps%20considered%20the%20best%20practice%20for%20performing%20historical%20searches%20for%20IOC%20hits%20against%20the%20log%20data%20within%20Sentinel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3B%20if%20a%20request%20is%20received%20to%20interrogate%20the%20previous%206%20months%20worth%20of%20retained%20log%20data%20against%20a%20large%20list%20of%20IOC%20IP%20addresses%20what%20method%20is%20best%20suited%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20am%20creating%20KQL%20queries%20and%20running%20these%20against%20the%20appropriate%20tables%2C%20or%20all%20tables%20if%20this%20is%20required.%20However%20these%20queries%20time%20out%20and%20end%20after%20circa%2010%20minutes%20so%20this%20is%20not%20always%20practical%20for%20large%20investigations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdditionally%20construction%20of%20the%20KQL%20queries%20for%20multiple%20IOC%20values%20is%20time%20consuming%20as%20you%20have%20to%20manually%20populate%20the%20query%20string%20with%20the%20relevant%20IOC%20and%20Sentinel%20KQL%20operator%2C%20using%20find%20and%20replace%20for%20example%20then%20pasting%20this%20back.%20Is%20there%20not%20a%20way%20like%20other%20SIEMs%20where%20you%20can%20create%20a%20list%20of%20IOCs%20(IP%20addresses%20or%20domains%20etc)%20and%20then%20reference%20that%20list%20within%20the%20KQL%20as%20not%20to%20have%20to%20manually%20construct%20the%20query%20on%20each%20occasion%20you%20perform%20your%20retrospective%20searches%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20help%20and%20comments.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2555707%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20IOC%20searches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2555707%22%20slang%3D%22en-US%22%3EGenerally%20if%20you%20need%20a%20list%20you%20have%20two%20main%20choices%2C%20creating%20the%20IP%20Addresses%20or%20IOC%20in%20a%20Watchlist%20(or%20on%20Azure%20Storage)%20or%20creating%20a%20dynamic%20list%20as%20part%20of%20the%20query%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fwatchlists%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsentinel%2Fwatchlists%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EKQL%20example%20of%20a%20dynamic%20list%3CBR%20%2F%3E%3CBR%20%2F%3Elet%20IPList%20%3D%20dynamic(%5B%22216.24.185.74%22%2C%20%22107.175.189.159%22%2C%22194.88.106.146%22%5D)%3B%3CBR%20%2F%3EThreatIntelligenceIndicator%3CBR%20%2F%3E%7C%20where%20NetworkSourceIP%20in%20(IPList)%20%3CBR%20%2F%3E%7C%20summarize%20count()%20by%20NetworkSourceIP%3CBR%20%2F%3E%3CBR%20%2F%3EHaving%20a%20timeout%20'suggests'%20there%20is%20some%20optimisation%20we%20can%20do%2C%20do%20you%20use%20the%20summarize%20command%3F%20Also%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fbest-practices%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fbest-practices%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everybody, 

 

I'm interested to understand how others approach this and what is perhaps considered the best practice for performing historical searches for IOC hits against the log data within Sentinel. 

 

For example; if a request is received to interrogate the previous 6 months worth of retained log data against a large list of IOC IP addresses what method is best suited for this?

 

Currently I am creating KQL queries and running these against the appropriate tables, or all tables if this is required. However these queries time out and end after circa 10 minutes so this is not always practical for large investigations.

 

Additionally construction of the KQL queries for multiple IOC values is time consuming as you have to manually populate the query string with the relevant IOC and Sentinel KQL operator, using find and replace for example then pasting this back. Is there not a way like other SIEMs where you can create a list of IOCs (IP addresses or domains etc) and then reference that list within the KQL as not to have to manually construct the query on each occasion you perform your retrospective searches? 

 

Thanks in advance for your help and comments.

3 Replies
best response confirmed by ts1120 (New Contributor)
Solution
Generally if you need a list you have two main choices, creating the IP Addresses or IOC in a Watchlist (or on Azure Storage) or creating a dynamic list as part of the query

https://docs.microsoft.com/en-us/azure/sentinel/watchlists

KQL example of a dynamic list

let IPList = dynamic(["216.24.185.74", "107.175.189.159","194.88.106.146"]);
ThreatIntelligenceIndicator
| where NetworkSourceIP in (IPList)
| summarize count() by NetworkSourceIP

Having a timeout 'suggests' there is some optimisation we can do, do you use the summarize command? Also see https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices



Thanks Clive, your help was appreciated. I have managed to utilise Watchlists and referenced this within the query as you said.
You can also look at integrating your IOC list with the Microsoft Security Graph - https://docs.microsoft.com/en-us/graph/api/resources/tiindicator?view=graph-rest-beta

Then they will show in the ThreatIntelligenceIndicator table.

You could also look at ingesting them to a custom table - https://docs.microsoft.com/en-us/rest/api/loganalytics/

Guess it depends how dynamic that list is, if it is a once off investigation then a watchlist is probably the easiest/most effective, if that list updates more often then I would go one of the other two options.
We support Ukraine and condemn war. Push Russian government to act against war. Be brave, vocal and show your support to Ukraine. Follow the latest news HERE