how to exclude a certain field value from results

[ayenumula]

Search query: list the last known user (userid) on each host.

sourcetype=syslog source=/var/log/secure "pam_unix(sshd:session)" | rex "(?i) user (?P<userid>[^ ]+)" | stats latest(userid) as userid by host, _time | dedup host

I then want to exclude a particular user "system" from the result list.

I tried the NOT and "!=" in every part of the query but not working, can you help me with the search query

[Damien_Dallimor · Accepted solution]

.. | where userid != "system" | ...

or maybe tweak your regex

(?i)\suser\s(?!system)(?P<userid>\w+)

[ayenumula]

Is there a certain way to exclude multiple users using | where userid != "system"

[ayenumula]

Damien's answer: | where userid != "system". This worked as it included the host (row) which has "system" user but excluded "system" from the result set, it still displayed the host with other users.

I tried Alterdego's answer: "| search userid!=system". This did not work for me because it completely excluded the host (row) which contains the word "system" even thou it contains other users.

[alterdego]

Does the value "system" appear in the filed you create? If so then you should be able to pipe your original search to the search command and exclude the "system" value with "| search userid!=system".

sourcetype=syslog source=/var/log/secure "pam_unix(sshd:session)" | rex "(?i) user (?P[^ ]+)" | stats latest(userid) as userid by host, _time | dedup host | search userid!=system