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.
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.
@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.
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.
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.
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)
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
If that's the case session_id=* AND log_id!=2
should work
is log_id2 a field?
Can you share a sanitized version of the events?
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.