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 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
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 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?
- Noa Kuperberg
Microsoft
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,NoaThanks 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.
