How to retrieve a field value for which no event exists containing another field value?

[laconix]

Hello,

I would like to perform a search that return only a particular field value for which i don't find in any other events containing a specific value for another field.

For example, if my sourcetype contains below events and field values (fields name are "session_id" and "log_id"):
Event1: session_id=1, log_id=1
Event2: session_id=1, log_id=2
Event3: session_id=1, log_id1, log_id=2
Event4: session_id=2, log_id=1

With this dataset, the search must only show session_id=2 because there isn't any event containing fields value session_id=2 + log_id=2.

Thanks for your help.

[laconix]

I found the solution for my problem, that is:

 sourcetype="test" NOT [search sourcetype="test" log_id=2  | dedup session_id  | fields session_id] | table session_id'

Thanks all for your help.

[niketn]

@laconix, try the following run anywhere example based on sample data provided.
The commands from | makeresults till | rename data as _raw are used to generate sample events as per the question. I have used rex to extract the fields session_is and log_id. You would not need this step if fields are already being extracted.

| makeresults
| eval data="session_id=1, log_id=1;session_id=1, log_id=2;session_id=1, log_id=1, log_id=2;session_id=2, log_id=1"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| rex "session_id=(?<session_id>[^,]+),"
| rex "log_id=(?<log_id>\d+)" max_match=0
| eventstats dc(log_id) as overall_unique_log_ids
| stats dc(log_id) as session_unique_log_ids max(overall_unique_log_ids) as overall_unique_log_ids values(log_id) as log_ids by session_id
| where session_unique_log_ids<overall_unique_log_ids

Please try out and confirm. You can change sample data as per your use cases to test out various scenarios.

[laconix]

Thanks for this example, the result is not as expected unfortunately.

If I add a new event in my dataset with content "session_id=3, log_id=2", it return me session_id=3.

[laconix]

Thanks for your reply. I see you count the number of unique log_id to perform the search, but it can't work for my need.

With the below dataset:

Event1: session_id=1, log_id=1
Event2: session_id=1, log_id=2
Event3: session_id=1, log_id1, log_id=2
Event4: session_id=2, log_id=1
Event5: session_id=3, log_id=1
Event5: session_id=4, log_id=2

Using you search, I have three results for session_id: 2, 3 and 4

But I expected only 2 results, session_id=2 and session_id=3. Session_id=4 must not match the Search as it is contains in an event with log_id=2.

[renjith_nair]

Hi @laconix,

It shouldn't be this easier but are you looking for something like

"your search" AND (session_id=2 AND log_id!=2)

[laconix]

Yes it's more complicated than that. If i have another event like below

Event5: session_id=3, log_id=1

Then, the search must return two results, session_id=2 and session_id=3

[renjith_nair]

If that's the case session_id=* AND log_id!=2 should work

[diogofgm]

is log_id2 a field?
Can you share a sanitized version of the events?

[laconix]

No, it is a value. The dataset can be presented like this:

Event1: session_id=1, log_id=1
Event2: session_id=1, log_id=2
Event3: session_id=1, log_id=1, log_id=2
Event4: session_id=2, log_id=1

Hope it's more clear like that.