How to produce a field value from a subsearch?

[dkingsland967]

Hi all, I'm currently working on creating an alert for any time a user mounts an ISO. My core search works exactly as intended, but I'm having trouble creating a desired subsearch.

Both searches run from the same index, but the core search will not produce the name of the workstation as it is not present in the data returned by the sourcetype in use. There is another sourcetype (same index) that does include this as a field titled "ComputerName", and there is an "ID" field that correlates between both sourcetypes.

So here is my core search:

Spoiler

index=[indexname] sourcetype=[sourcetype] [search parameters]
| table EventType FileName ID IndexTime

How can I build a subsearch that queries the second sourcetype by the corresponding ID value and produces the ComputerName value to add to the table?

Thanks!

[yuanliu]

A subsearch limits the main search returns only those that any and all fields returned by the subsearch is a match.  If the subsearch returns a field that the main search doesn't contain, as you described about ComputerName, a match is not found.  Therefore this method is unsuitable for your use case.

You can do this with join.  But many experienced users will tell you not to because any set operation is expensive. (There are many posts, even .conf talks about this subject.)  A better method is to search both sourcetypes and run stats on their differences.  Because you didn't give specifics, (it's better to be specific with complex conditions) here is a hypothetical example. (By the way, use the "</>" icon to insert code illustrations, not "Spoiler" which make it more difficult for others.)

index=indexname (sourcetype=sourcetype1 OR (sourcetype=sourcetype2 additional search parameters))
| stats values(sourcetype) as sourcetypes values(EventType) as EventType values(FileName) as FileName values(ComputerName) as ComputerName values(IndexTime) as IndexTime by ID
| where sourcetype == "sourcetype1" AND sourcetype == "sourcetype2"
| fields - sourcetypes

Here, I assume that IndexTime is a field that comes with sourcetype1 independent of _time. (Again, such details are very important when asking questions.)  If this is not the case, you can bucket _time if the match between the two can be expected within each bucket.  Additionally, you mentioned the desire to use ComputerName but your code did not consider that; I inserted it in the above as it makes logical sense. (Details, details🙂)

If, on the other hand, IndexTime is just _time, and sourcetype2 is to be used like a lookup in the entire search period and cannot be expected to match sourcetype1 within each time bucket, you can be creative and produce the field for sourcetype1 only, like this:

index=indexname (sourcetype=sourcetype1 OR (sourcetype=sourcetype2 additional search parameters))
| eval IndexTime = if(sourcetype == "sourcetype1", _time, null())
| stats values(sourcetype) as sourcetypes values(EventType) as EventType values(FileName) as FileName values(ComputerName) as ComputerName values(IndexTime) as IndexTime by ID
| where sourcetype == "sourcetype1" AND sourcetype == "sourcetype2"
| fields - sourcetypes