Conversation Re: Managing lists in Azure Sentinelhttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1193892#M1094
<P><LI-USER uid="239477"></LI-USER></P><P>i got it using&nbsp;&nbsp;</P><DIV><DIV><SPAN>externaldata (Type</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN>, Indicator</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN> , Campaign</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN> ) [</SPAN></DIV><DIV><SPAN>@</SPAN><SPAN>"<A href="https://xxxxxxxx.csv" target="_blank">https://xxxxxxxx.csv</A>"</SPAN><SPAN>]</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>1. how to i search for a hit of the IOC's on all of the tables on sentinel.</SPAN></DIV><DIV><SPAN>2. how do i do that on specific tables</SPAN></DIV></DIV>Tue, 25 Feb 2020 18:05:23 GMTomrip2020-02-25T18:05:23ZManaging listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/906879#M546
<P>how can i manage a list on Sentinel</P><P>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</P><P>obviously using similar solution&nbsp; such as above is not possible:</P><PRE>let List = datatable(Account:string, Domain:string)
["john", "johnsdomain.com", "greg", "gregsdomain.net", "larry", "Domain"];</PRE><P>&nbsp;the same goes for IOC's i have found in my enviroment and would like to search for a hit.</P>Thu, 10 Oct 2019 18:54:42 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/906879#M546omrip2019-10-10T18:54:42ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/908045#M552
<P><LI-USER uid="423940"></LI-USER>&nbsp;</P>
<P>&nbsp;</P>
<P>Option 1 - you can use IN or !IN to include or exclude</P>
<LI-CODE lang="markup">let List = datatable(Account:string, Domain:string)
["john", "johnsdomain.com",
"Demo", "gregsdomain.net",
"larry", "Domain"];
SigninLogs
| where Identity in (List)
</LI-CODE>
<P>Option 2 - you can use a JOIN as well&nbsp;</P>
<LI-CODE lang="markup">let masterList = dynamic (['GB', 'US']); // setup a master list of country codes
SigninLogs
| where TimeGenerated &gt;= 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&lt;10, identityList, "multiple (&gt;10)")
| join kind= anti (
SigninLogs
| where TimeGenerated &lt; 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 &gt;= 1 // select threshold above which #new accounts from a new location is deemed suspicious</LI-CODE>
<P>&nbsp;</P>
<P>Option 3 - create a group/list with a query and compare it to another table&nbsp;</P>
<P>&nbsp;</P>
<LI-CODE lang="markup">// 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 &gt; ago(60m)
| where myLinuxGrp contains Computer
| project myLinuxGrp, Computer , SyslogMessage </LI-CODE>
<P>&nbsp;&nbsp;</P>Fri, 11 Oct 2019 08:49:35 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/908045#M552Clive Watson2019-10-11T08:49:35ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/910468#M557
<P><LI-USER uid="239477"></LI-USER>&nbsp;</P><P>thx</P><P>1. i do not think you understood my intention.</P><P>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.</P><P>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?</P><P>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.</P><P>again, for neither of the cases i do not wish to mange them locally but in the Azure.</P><P>&nbsp;</P>Sat, 12 Oct 2019 09:04:38 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/910468#M557omrip2019-10-12T09:04:38ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/911256#M558
<P>Hi&nbsp;<LI-USER uid="423940"></LI-USER>&nbsp;</P>
<P>&nbsp;</P>
<P>I struggling to understand what you are asking here, so sorry to ask again?&nbsp;</P>
<P>&nbsp;</P>
<P>Are you trying to read from a file, if so see&nbsp;<A href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcloudblogs.microsoft.com%2Findustry-blog%2Fen-gb%2Fcross-industry%2F2019%2F08%2F13%2Fazure-log-analytics-how-to-read-a-file%2F&amp;data=02%7C01%7CClive.Watson%40microsoft.com%7C5cd8ccc5120c47098d8808d74e6107ea%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637064050994834286&amp;sdata=A9aNQT5Zb24WvND8ZN4YKxL5cmsPzWaSyiNK8XUJUOs%3D&amp;reserved=0" target="_blank">https://cloudblogs.microsoft.com/industry-blog/en-gb/cross-industry/2019/08/13/azure-log-analytics-how-to-read-a-file/</A>&nbsp; &nbsp;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.&nbsp; You can build lists on the fly / at run time with a data table as shown.&nbsp;&nbsp;<BR /><BR />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.&nbsp; Then read from it with extrernaldata and parse the JSON (if it's JSON )</P>Sun, 13 Oct 2019 17:43:11 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/911256#M558Clive Watson2019-10-13T17:43:11ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147495#M1017
Hi, Did you get answer to your query? I also have 1000's of IOCs to be used against rules to check for a match. And if using BLOB storage isn't an option (want to read data from a file stored locally in the system) then what should we do?<BR /><BR />Regards,<BR />Mitesh AgrawalMon, 03 Feb 2020 10:10:20 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147495#M1017MiteshAgrawal2020-02-03T10:10:20ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147496#M1018
<P>Hi&nbsp;<LI-USER uid="239477"></LI-USER>,</P><P>&nbsp;</P><P><SPAN>I&nbsp; have 1000's of IOCs to be used against rules to check for a match. And if using BLOB storage isn't an option (want to read data from a file stored locally in the system) then what should we do?</SPAN><BR /><BR /><SPAN>Regards,</SPAN><BR /><SPAN>Mitesh Agrawal</SPAN></P>Mon, 03 Feb 2020 10:11:10 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147496#M1018MiteshAgrawal2020-02-03T10:11:10ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147555#M1019
<P><LI-USER uid="539205"></LI-USER>&nbsp;</P>
<P>&nbsp;</P>
<P>There are more guidance articles&nbsp;<A href="https://techcommunity.microsoft.com/t5/azure-sentinel/implementing-lookups-in-azure-sentinel-part-1-reference-files/ba-p/1091306&nbsp;" target="_blank">https://techcommunity.microsoft.com/t5/azure-sentinel/implementing-lookups-in-azure-sentinel-part-1-reference-files/ba-p/1091306&nbsp;</A> and more to follow. Also have you considered a custom log?&nbsp;&nbsp;</P>
<P><A href="https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources-custom-logs" target="_blank">https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources-custom-logs</A></P>
<P>or reading data from a file using a Logic App?&nbsp;&nbsp;</P>
<P>&nbsp;</P>Mon, 03 Feb 2020 10:58:09 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147555#M1019Clive Watson2020-02-03T10:58:09ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147631#M1022
Hi Clive,<BR /><BR />Thanks for the links. The first one is related to BLOB storage which we aren't using as of now.<BR /><BR />I found 2nd one interesting and will definitely try creating a Custom log source to read files.<BR /><BR />Regards,<BR />Mitesh AgrawalMon, 03 Feb 2020 12:21:26 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1147631#M1022MiteshAgrawal2020-02-03T12:21:26ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1182297#M1079
<P>HI <LI-USER uid="239477"></LI-USER>&nbsp;</P><P>can you please elaborate on the process which you have mentioned :</P><P>1. "<SPAN>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.&nbsp; Then read from it with external&nbsp;data and parse the JSON (if it's JSON )</SPAN>"</P><P>2. also what is the process of using a blob storage?</P><P>3. am i bind of using only a Blob storage?</P><P>4. the external file must be json format?&nbsp;</P>Wed, 19 Feb 2020 15:05:43 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1182297#M1079omrip2020-02-19T15:05:43ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1193892#M1094
<P><LI-USER uid="239477"></LI-USER></P><P>i got it using&nbsp;&nbsp;</P><DIV><DIV><SPAN>externaldata (Type</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN>, Indicator</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN> , Campaign</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN> ) [</SPAN></DIV><DIV><SPAN>@</SPAN><SPAN>"<A href="https://xxxxxxxx.csv" target="_blank">https://xxxxxxxx.csv</A>"</SPAN><SPAN>]</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>1. how to i search for a hit of the IOC's on all of the tables on sentinel.</SPAN></DIV><DIV><SPAN>2. how do i do that on specific tables</SPAN></DIV></DIV>Tue, 25 Feb 2020 18:05:23 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1193892#M1094omrip2020-02-25T18:05:23ZRe: Managing listshttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1193935#M1095
<P><LI-USER uid="423940"></LI-USER>&nbsp;</P>
<P><A href="https://techcommunity.microsoft.com/t5/azure-sentinel/implementing-lookups-in-azure-sentinel-part-1-reference-files/ba-p/1091306" target="_blank">https://techcommunity.microsoft.com/t5/azure-sentinel/implementing-lookups-in-azure-sentinel-part-1-reference-files/ba-p/1091306</A></P>
<P>The above has examples like this (adapt the <STRONG>whitelist</STRONG> line to your own file)</P>
<LI-CODE lang="markup">let timeRange = 1d;
let whitelist = externaldata (UserPrincipalName: string) [h"https://..."] with (ignoreFirstRecord=true);
SigninLogs
| where TimeGenerated &gt;= ago(timeRange)
| where UserPrincipalName !in~ (whitelist)</LI-CODE>
<P>&nbsp;</P>
<P>Using your data across all tables, would need a union or join e.g. (jusr replace the fake whitelist with your one).&nbsp;</P>
<DIV><SPAN>externaldata (Type</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN>, Indicator</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN>&nbsp;, Campaign</SPAN><SPAN>:</SPAN><SPAN>string</SPAN><SPAN>&nbsp;) [</SPAN></DIV>
<DIV><SPAN>@</SPAN><SPAN>"<A href="https://xxxxxxxx.csv/" target="_blank" rel="nofollow noopener noreferrer">https://xxxxxxxx.csv</A>"</SPAN><SPAN>]</SPAN></DIV>
<P>&nbsp;</P>
<LI-CODE lang="markup">let whitelist = dynamic(["fake IOC","another fakeIOC"]);
union withsource=TableName *
| where Indicator in (whitelist)</LI-CODE>
<P>&nbsp;</P>Tue, 25 Feb 2020 18:26:29 GMThttps://techcommunity.microsoft.com/t5/azure-sentinel/managing-lists/m-p/1193935#M1095Clive Watson2020-02-25T18:26:29Z