let say here is my log:
id 123456789 appear here
id 123456789 something bad want to exclude
id 111111111 appear here
how to build a search so that it will only pickup id with "appear here" but not "something bad want to exclude"? in this case the desired result is the 111111111
I presume you have the "id" field available for searching. If not, create a field extraction for it.
Use a subsearch to exclude specific events from a base search:
index=... NOT [search index=... "something bad want to exclude" | table id]
Following your example, the result should look like:
123456789
111111111
Because both events have appear here
.
If the "appear here" pattern doesn't change you can add it to your search, E.g.:
index=test "appear here" | rex "id\s+(?<id>[0-9]{9})" |table id
Otherwise, you can exclude what you don't want:
index=test NOT "something bad want to exclude" | rex "id\s+(?<id>[0-9]{9})" |table id
Hope it helps.