Aug 28 2017
02:51 AM
- last edited on
Apr 07 2022
04:42 PM
by
TechCommunityAP
Aug 28 2017
02:51 AM
- last edited on
Apr 07 2022
04:42 PM
by
TechCommunityAP
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 decided to attach a text file to one of the VMs and create a custom log. Now the other problem that we have is to parse the RawData from these text files.
The example query is, lets say we are trying to find out the security events fired by users which belong to the text file in custom logs. Can anyone help in providing some reference in building such queries ?
SecurityEvent
| where SubjectUserName in ( table("USERS4_CL") | project RawData)
Aug 28 2017 08:29 AM - edited Aug 28 2017 08:56 AM
Hi,
you can do it quite like you show above. For example, here's how you can query Event logs of computers that also have security events from the last hour:
let computers_with_security_events =
SecurityEvent | where TimeGenerated > now(-1h) | project Computer;
Event | where TimeGenerated > now(-1h) | where Computer in (computers_with_security_events)
If querying the custom log is very common, you might want to save this part as a function. If you name the function for example 'computers_in_my_custom_log' you can refer to it from any other query like this:
Aug 28 2017 06:21 PM - edited Aug 28 2017 06:23 PM
For your query specifically it seems like this should work, assuming you have ingested using Custom Logs functioanlity data from Custom Logs into the table User4_CL.
//Assuming your table User4_CL has username as a column - or substitute with appropriate colum.
let users=Users4_CL | summarize makeset(username);
SecurityEvent| where SubjectUserName in (users) | project RawData)
Aug 29 2017 11:19 PM
Thanks Noa, was able to slove the problem. It seems when I insert the custom logs using the API,its much better in terms of identifying sptring fields.
Aug 29 2017 11:20 PM
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)
Aug 30 2017 06:15 AM
Ok.
Q: What is the first let statement for ? let arr = (search "whitelistusers_CL" | project Name_s)
Doesn't seem to be used anywhere?
Aug 31 2017 10:18 AM
That first line was a mistake.
Aug 12 2019 02:02 AM
@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!!
Aug 13 2019 01:02 AM
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"]
Name | Code |
---|---|
Name | Code |
Afghanistan | AF |
Åland Islands | AX |
Albania | AL |
Algeria | DZ |
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.RemoteIPCountry
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 |
Nov 04 2019 08:27 PM
@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 ?