Log analytics - Look up external source of data

Copper Contributor

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)

 

9 Replies

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:

Event | where TimeGenerated > now(-1h) | where Computer in (computers_in_my_custom_log)
 
Hope this helps,
Noa

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)

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.

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!!

@gagan2509rajpal 

 

You can use externaldata  operator to read files, like csv or tsvscsvsohsvpsvtxtraw.

 

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.RemoteIPCountry
 
Run 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 ?