Why am I receiving wrong number of events when running multiple lookups?

[finnpalm]

Hello. I'm having some problem and I can't for the life of me figure out what goes wrong. I am running a search like this against two lookups (both lookup files has multiple columns):

index=gateway EventIDValue=gateway-check EventStatus=success
| lookup assets_and_users.csv USER AS SourceUserName, ASSET AS EndpointDeviceName OUTPUTNEW USER, ASSET
| lookup computer_objects.csv own_asset AS EndpointDeviceName OUTPUTNEW own_asset
| where isnotnull(USER) OR isnotnull(ASSET) OR isnotnull(own_asset) AND own_asset!=EndpointDeviceName

The idea is to check for a certain number of assets and users previously seen in our environment with the assets_and_users.csv lookup, and filter out assets that are currently managed by us with the computer_objects.csv lookup, so that I can see activity from the previously seen assets and users as well as assets not previously seen and that are not managed by us. However the first iteration of the search looked like this:

index=vpn EventIDValue=gateway-check EventStatus=success
| lookup assets_and_users.csv USER AS SourceUserName OUTPUTNEW USER
| lookup computer_objects.csv own_asset AS EndpointDeviceName OUTPUTNEW own_asset
| where isnotnull(USER) OR isnotnull(own_asset) AND own_asset!=EndpointDeviceName

and that version gave me a couple thousand events. However, once I added the asset part as seen in the top query I got three events which doesn't make sense. I should if anything get more events than the first iteration (bottom query).

Can someone spot where it goes wrong?

[yuanliu]

The first thing I notice is that the two servers not only differ in ASSET AS EndpointDeviceName, but also in index.  The first iteration uses index=vpn, the new one uses index=gateway.  Why should the newer search return more than three?

Secondly, even if the two indices contain similar data, why would adding a second match requirement ("ASSET AS EndpointDeviceName") in lookup assets_and_users.csv NOT significantly reduce the number of outputs?  I see no reason for adding a match requirement to "get more events than the first iteration."

This is a long way to say: You need to illustrate your data, explain any characteristics that affect results, and explain why you are expecting the results to be different from what you get.

[finnpalm · Accepted solution]

I also need to write the queries correctly in the post it would seem. Both queries were run against the same index. I messed up in my original post.

After some experimenting back and forth it seems that the issue is some sort of hidden order of operations as making changes to the order in the line with the where clause give different results. I still have no idea exactly how that needs to be formatted but it works now coincidentally.

I also split up the first lookup into two, because it seems that if you list several lookup fields comma separated that assumes an "AND", but I don't know if that is indeed the case.

Finally I changed the "isnotnull(own_asset) AND own_asset!=EndpointDeviceName" to simply "isnull(own_asset)" just to tidy it up as I realised that is the same thing.

So in conclusion, it works, but I don't know why those changes made the difference.

[finnpalm]

Case sensitivity does not seem to be the issue. We have already tried setting the definition to case insensitive and it gave the same result.

[scelikok]

Hİ @finnpalm,

Lookups work case-sensitive as default. That is why your "ASSET AS EndpointDeviceName"  may not match. If this is the case, you can change your lookup definition to case-insensitive.