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...
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 |
CliveWatson Thank you for the tip on External Data and I see how the External Data can be a Blob in Storage Account.
Aside to flat files, can we have external data as a table in Storage Account that can be accessed from Log Analytics ?
