SOLVED
Home

Managing lists

%3CLINGO-SUB%20id%3D%22lingo-sub-906879%22%20slang%3D%22en-US%22%3EManaging%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-906879%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20can%20i%20manage%20a%20list%20on%20Sentinel%3C%2FP%3E%3CP%3Efor%20instance-%20i%20have%20a%20list%20of%20known%20assets%20that%20hold%20hundreds%2B%20assets%20and%20when%20the%20search%20runs%20i%20would%20like%20to%20search%20and%20check%20if%20there%20is%20a%20hit%20in%20the%20list%3C%2FP%3E%3CP%3Eobviously%20using%20similar%20solution%26nbsp%3B%20such%20as%20above%20is%20not%20possible%3A%3C%2FP%3E%3CPRE%3Elet%20List%20%3D%20datatable(Account%3Astring%2C%20Domain%3Astring)%0A%5B%22john%22%2C%20%22johnsdomain.com%22%2C%20%22greg%22%2C%20%22gregsdomain.net%22%2C%20%22larry%22%2C%20%22Domain%22%5D%3B%3C%2FPRE%3E%3CP%3E%26nbsp%3Bthe%20same%20goes%20for%20IOC's%20i%20have%20found%20in%20my%20enviroment%20and%20would%20like%20to%20search%20for%20a%20hit.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-906879%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESentinel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908045%22%20slang%3D%22en-US%22%3ERe%3A%20Managing%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423940%22%20target%3D%22_blank%22%3E%40omrip%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOption%201%20-%20you%20can%20use%20IN%20or%20!IN%20to%20include%20or%20exclude%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20List%20%3D%20datatable(Account%3Astring%2C%20Domain%3Astring)%0A%5B%22john%22%2C%20%22johnsdomain.com%22%2C%0A%20%22Demo%22%2C%20%22gregsdomain.net%22%2C%0A%20%22larry%22%2C%20%22Domain%22%5D%3B%0ASigninLogs%0A%7C%20where%20Identity%20in%20(List)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EOption%202%20-%20you%20can%20use%20a%20JOIN%20as%20well%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20masterList%20%3D%20dynamic%20(%5B'GB'%2C%20'US'%5D)%3B%20%20%2F%2F%20setup%20a%20master%20list%20of%20country%20codes%0ASigninLogs%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(1d)%0A%7C%20summarize%20perIdentityAuthCount%3Dcount()%20by%20Identity%2C%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20locationString%3D%20strcat(tostring(LocationDetails%5B%22countryOrRegion%22%5D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%22%2F%22%2C%20tostring(LocationDetails%5B%22state%22%5D)%2C%20%22%2F%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20tostring(LocationDetails%5B%22city%22%5D)%2C%20%22%3B%22%20%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20tostring(LocationDetails%5B%22geoCoordinates%22%5D))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20countryString%3D%20strcat(tostring(LocationDetails%5B%22countryOrRegion%22%5D))%0A%2F%2F%20filter%20on%20masterList%20of%20country%20codes%2C%20exclude%20those%20on%20the%20list%0A%7C%20where%20countryString%20!in%20(masterList)%0A%7C%20summarize%20distinctAccountCount%20%3D%20count()%2C%20identityList%3Dmakeset(Identity)%2C%20t%20%3D%20tostring(masterList)%20%20by%20locationString%0A%7C%20extend%20identityList%20%3D%20iff(distinctAccountCount%26lt%3B10%2C%20identityList%2C%20%22multiple%20(%26gt%3B10)%22)%0A%7C%20join%20kind%3D%20anti%20(%0A%20%20%20%20SigninLogs%0A%20%20%20%20%7C%20where%20TimeGenerated%20%26lt%3B%20ago(1d)%0A%20%20%20%20%7C%20project%20%20%20locationString%3D%20strcat(tostring(LocationDetails%5B%22countryOrRegion%22%5D)%2C%20%22%2F%22%2C%20tostring(LocationDetails%5B%22state%22%5D)%2C%20%22%2F%22%2C%20%0A%20%20%20%20%20%20%20%20tostring(LocationDetails%5B%22city%22%5D)%2C%20%22%3B%22%20%2C%20tostring(LocationDetails%5B%22geoCoordinates%22%5D))%0A%20%20%20%20%7C%20summarize%20priorCount%20%3D%20count()%20by%20locationString)%20on%20locationString%0A%7C%20where%20distinctAccountCount%20%26gt%3B%3D%201%20%2F%2F%20select%20threshold%20above%20which%20%23new%20accounts%20from%20a%20new%20location%20is%20deemed%20suspicious%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOption%203%20-%20create%20a%20group%2Flist%20with%20a%20query%20and%20compare%20it%20to%20another%20table%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%20First%20create%20a%20list%20of%20Linux%20machines%20that%20startwith%20%22aksnnnnnnn%22%20%0Alet%20myLinuxGrp%20%3D%20toscalar(Heartbeat%20%0A%7C%20where%20OSType%20%3D%3D%20%22Linux%22%20and%20Computer%20startswith%20%22aks%22%20%0A%7C%20summarize%20make_set(Computer))%3B%20%20%20%0ASyslog%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(60m)%20%0A%7C%20where%20myLinuxGrp%20contains%20Computer%20%0A%7C%20project%20myLinuxGrp%2C%20Computer%20%2C%20SyslogMessage%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-910468%22%20slang%3D%22en-US%22%3ERe%3A%20Managing%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethx%3C%2FP%3E%3CP%3E1.%20i%20do%20not%20think%20you%20understood%20my%20intention.%3C%2FP%3E%3CP%3Ei%20have%20hundreds%20of%20endpoint%20and%20would%20like%20to%20create%20a%20large%20table%2Ffile%20from%20my%20known%20assets%20and%20to%20check%20on%20top%20of%20that.%3C%2FP%3E%3CP%3Ebeside%20managing%20it%20locally%20and%20and%20using%20the%20mantioned%20_json%2C%20is%20there%20a%20way%20to%20upload%20the%20file%20to%20the%20Azure%20and%20run%20on%20top%20of%20that%3F%3C%2FP%3E%3CP%3E2.%20what%20do%20i%20do%20in%20case%20i%20am%20managing%20a%20large%20amount%20of%20IOC's%20list%20%2C%20if%20i%20run%20a%20search%20on%20that%20list%20and%20i%20do%20not%20have%20it%2C%20i%20would%20like%20to%20ingest%20the%20new%20IOC%20i%20found%20to%20the%20list.%3C%2FP%3E%3CP%3Eagain%2C%20for%20neither%20of%20the%20cases%20i%20do%20not%20wish%20to%20mange%20them%20locally%20but%20in%20the%20Azure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-911256%22%20slang%3D%22en-US%22%3ERe%3A%20Managing%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-911256%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423940%22%20target%3D%22_blank%22%3E%40omrip%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20struggling%20to%20understand%20what%20you%20are%20asking%20here%2C%20so%20sorry%20to%20ask%20again%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAre%20you%20trying%20to%20read%20from%20a%20file%2C%20if%20so%20see%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fnam06.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fcloudblogs.microsoft.com%252Findustry-blog%252Fen-gb%252Fcross-industry%252F2019%252F08%252F13%252Fazure-log-analytics-how-to-read-a-file%252F%26amp%3Bdata%3D02%257C01%257CClive.Watson%2540microsoft.com%257C5cd8ccc5120c47098d8808d74e6107ea%257C72f988bf86f141af91ab2d7cd011db47%257C1%257C0%257C637064050994834286%26amp%3Bsdata%3DA9aNQT5Zb24WvND8ZN4YKxL5cmsPzWaSyiNK8XUJUOs%253D%26amp%3Breserved%3D0%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcloudblogs.microsoft.com%2Findustry-blog%2Fen-gb%2Fcross-industry%2F2019%2F08%2F13%2Fazure-log-analytics-how-to-read-a-file%2F%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BIf%20you%20are%20trying%20to%20create%20a%20file%20from%20Log%20Analytics%2C%20you%20can't%20do%20that%2C%20only%20read%20from%20a%20file%20is%20possible%20using%20externaldata%20operator%20as%20per%20my%20example.%26nbsp%3B%20You%20can%20build%20lists%20on%20the%20fly%20%2F%20at%20run%20time%20with%20a%20data%20table%20as%20shown.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20it's%20a%20file%20you%20need%20to%20upload%2C%20perhaps%20on%20a%20schedule%2C%20you%20might%20need%20to%20use%20Logic%20Apps%20to%20control%20that%20workflow%2Fprocess.%26nbsp%3B%20Then%20read%20from%20it%20with%20extrernaldata%20and%20parse%20the%20JSON%20(if%20it's%20JSON%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E
omrip
Occasional Contributor

how can i manage a list on Sentinel

for instance- i have a list of known assets that hold hundreds+ assets and when the search runs i would like to search and check if there is a hit in the list

obviously using similar solution  such as above is not possible:

let List = datatable(Account:string, Domain:string)
["john", "johnsdomain.com", "greg", "gregsdomain.net", "larry", "Domain"];

 the same goes for IOC's i have found in my enviroment and would like to search for a hit.

3 Replies

@omrip 

 

Option 1 - you can use IN or !IN to include or exclude

let List = datatable(Account:string, Domain:string)
["john", "johnsdomain.com",
 "Demo", "gregsdomain.net",
 "larry", "Domain"];
SigninLogs
| where Identity in (List)

Option 2 - you can use a JOIN as well 

let masterList = dynamic (['GB', 'US']);  // setup a master list of country codes
SigninLogs
| where TimeGenerated >= ago(1d)
| summarize perIdentityAuthCount=count() by Identity,  
            locationString= strcat(tostring(LocationDetails["countryOrRegion"]),
             "/", tostring(LocationDetails["state"]), "/",
             tostring(LocationDetails["city"]), ";" ,
             tostring(LocationDetails["geoCoordinates"])),
             countryString= strcat(tostring(LocationDetails["countryOrRegion"]))
// filter on masterList of country codes, exclude those on the list
| where countryString !in (masterList)
| summarize distinctAccountCount = count(), identityList=makeset(Identity), t = tostring(masterList)  by locationString
| extend identityList = iff(distinctAccountCount<10, identityList, "multiple (>10)")
| join kind= anti (
    SigninLogs
    | where TimeGenerated < ago(1d)
    | project   locationString= strcat(tostring(LocationDetails["countryOrRegion"]), "/", tostring(LocationDetails["state"]), "/", 
        tostring(LocationDetails["city"]), ";" , tostring(LocationDetails["geoCoordinates"]))
    | summarize priorCount = count() by locationString) on locationString
| where distinctAccountCount >= 1 // select threshold above which #new accounts from a new location is deemed suspicious

 

Option 3 - create a group/list with a query and compare it to another table 

 

// First create a list of Linux machines that startwith "aksnnnnnnn" 
let myLinuxGrp = toscalar(Heartbeat 
| where OSType == "Linux" and Computer startswith "aks" 
| summarize make_set(Computer));   
Syslog
| where TimeGenerated > ago(60m) 
| where myLinuxGrp contains Computer 
| project myLinuxGrp, Computer , SyslogMessage 

  

@Clive Watson 

thx

1. i do not think you understood my intention.

i have hundreds of endpoint and would like to create a large table/file from my known assets and to check on top of that.

beside managing it locally and and using the mantioned _json, is there a way to upload the file to the Azure and run on top of that?

2. what do i do in case i am managing a large amount of IOC's list , if i run a search on that list and i do not have it, i would like to ingest the new IOC i found to the list.

again, for neither of the cases i do not wish to mange them locally but in the Azure.

 

Solution

Hi @omrip 

 

I struggling to understand what you are asking here, so sorry to ask again? 

 

Are you trying to read from a file, if so see https://cloudblogs.microsoft.com/industry-blog/en-gb/cross-industry/2019/08/13/azure-log-analytics-h...   If you are trying to create a file from Log Analytics, you can't do that, only read from a file is possible using externaldata operator as per my example.  You can build lists on the fly / at run time with a data table as shown.  

If it's a file you need to upload, perhaps on a schedule, you might need to use Logic Apps to control that workflow/process.  Then read from it with extrernaldata and parse the JSON (if it's JSON )

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies