Forum Discussion
Log analytics - Look up external source of data
We have a requirement where we should be able to lookup data from an external text file and use it in our filter conditions in the queries. Since we did not see an option to do a lookup, we decid...
Thanks Ketan, i was able to make it work, my query was slightly different.
let arr = (search "whitelistusers_CL" | project Name_s );
let foo = (tableName:string) { table(tableName) | project Name_s };
let b = foo('whitelistusers_CL');
SecurityEvent
| where SubjectUserName in (b)
Ok.
Q: What is the first let statement for ? let arr = (search "whitelistusers_CL" | project Name_s)
Doesn't seem to be used anywhere?
That first line was a mistake.
Vineet Bhatia I also have the same requirement. We have to lookup the data into a .csv file and use the corresponding data in a query. Instead of using Virtual machine, I want to use storage account. Any suggestions would be appreciated.
Thanks!!
You can use externaldata operator to read files, like csv or
tsv,scsv,sohsv,psv,txt,raw.This example files happens to be publicly accessible on a website, but you could use one in Azure Blob instead?
externaldata(Name:string, Code:string) [@"https://datahub.io/core/country-list/r/data.csv"]Go to Log Analytics and Run Query
Name Code Name Code Afghanistan AF Åland Islands AX Albania AL Algeria DZ You can then JOIN those datasets together - I've used the WireData table, but you may need to use one you have or want to use - the link below uses the Microsoft demo dataset?externaldata(Name:string, Code:string) [@"https://datahub.io/core/country-list/r/data.csv"] | join kind= inner ( WireData | where TimeGenerated > startofday(ago(1d)) ) on $left.Name == $right.RemoteIPCountryRun the above in the demo workspace, which adds the CODE column from the csv file to the WireData - use this link:Example results:
Name Code Computer SessionStartTime SessionEndTime LocalIP LocalSubnet LocalMAC LocalPortNumber RemoteIP RemoteMAC RemotePortNumber SessionID SequenceNumber SessionState SentBytes ReceivedBytes TotalBytes ProtocolName IPVersion SentPackets ReceivedPackets Direction ApplicationProtocol ProcessID ProcessName ApplicationServiceName LatencyMilliseconds LatencySamplingTimeStamp LatencySamplingFailureRate MaliciousIP IndicatorThreatType Description TLPLevel Confidence Severity FirstReportedDateTime LastReportedDateTime IsActive ReportReferenceLink AdditionalInformation RemoteIPLongitude RemoteIPLatitude RemoteIPCountry SourceSystem MG ManagementGroupName TenantId TimeGenerated Type _ResourceId Netherlands NL ContosoAppSrv1 2019-08-13T07:52:02Z 2019-08-13T07:52:17Z 10.6.0.28 10.6.0.0/24 3389 194.61.24.9 0 10.6.0.28_3389_194.61.24.9_0_1104 null Connected 6789 4272 11061 TCP IPv4 null null Inbound RDP 1104 C:\Windows\System32\svchost.exe null null null 5.71 52.51 Netherlands OpsManager 00000000-0000-0000-0000-000000000001 AOI-b438b4f6-912a-46d5-9cb1-b44069212abc b438b4f6-912a-46d5-9cb1-b44069212abc 2019-08-13T07:53:09Z WireData United States US ContosoAppSrv1 2019-08-13T07:52:02Z 2019-08-13T07:52:17Z 10.6.0.28 10.6.0.0/24 3389 50.243.35.24 0 10.6.0.28_3389_50.243.35.24_0_1104 null Disconnected 1563 1148 2711 TCP IPv4 null null Inbound RDP 1104 C:\Windows\System32\svchost.exe null null null -75.14 39.99 United States OpsManager 00000000-0000-0000-0000-000000000001 AOI-b438b4f6-912a-46d5-9cb1-b44069212abc b438b4f6-912a-46d5-9cb1-b44069212abc 2019-08-13T07:53:09Z WireData Canada CA ContosoAppSrv1 2019-08-13T07:52:02Z 2019-08-13T07:52:17Z 10.6.0.28 10.6.0.0/24 3389 96.125.135.204 0 10.6.0.28_3389_96.125.135.204_0_1104 null Disconnected 1563 1144 2707 TCP IPv4 null null Inbound RDP 1104 C:\Windows\System32\svchost.exe null null null -79.52 43.61 Canada OpsManager 00000000-0000-0000-0000-000000000001 AOI-b438b4f6-912a-46d5-9cb1-b44069212abc b438b4f6-912a-46d5-9cb1-b44069212abc 2019-08-13T07:53:09Z WireData United States US ContosoAppSrv1 2019-08-13T07:52:02Z 2019-08-13T07:52:17Z 10.6.0.28 10.6.0.0/24 3389 193.188.22.243 0 10.6.0.28_3389_193.188.22.243_0_1104 null Disconnected 1563 1122 2685 TCP IPv4 null null Inbound RDP 1104 C:\Windows\System32\svchost.exe null null null -122.28 37.77 United States OpsManager 00000000-0000-0000-0000-000000000001 AOI-b438b4f6-912a-46d5-9cb1-b44069212abc b438b4f6-912a-46d5-9cb1-b44069212abc 2019-08-13T07:53:09Z WireData Netherlands NL ContosoAppSrv1 2019-08-13T07:52:17Z 2019-08-13T07:52:32Z 10.6.0.28 10.6.0.0/24 3389 194.61.24.9 0 10.6.0.28_3389_194.61.24.9_0_1104 null Connected 4888 4032 8920 TCP IPv4 null null Inbound RDP 1104 C:\Windows\System32\svchost.exe null null null 5.71 52.51 Netherlands OpsManager 00000000-0000-0000-0000-000000000001 AOI-b438b4f6-912a-46d5-9cb1-b44069212abc b438b4f6-912a-46d5-9cb1-b44069212abc 2019-08-13T07:53:09Z WireData
