topic Re: How to search for values not in a listing in Splunk Search

How to search for values not in a listing

NanSplk01 — Thu, 29 Jan 2026 15:52:37 GMT

I have a search started, but it's failing to run. What I want is to eliminate some ID's and only bring back ID's that are not in a listing:

index IN ("pisupport", "pisupport-np") sourcetype=PIMessage
("procbook" AND "Successful Login")
| rex field=_raw "Identity\sList:\s(?<identity>[^.]+)"
| rex field=_raw "Username\s:\s(?<username>[^.]+)"
| stats count AS Total_Connections, latest(_time) AS Latest_Timestamp, values(identity) AS Security_Mapping, values(host) AS Connected_Hosts, values(username) as LanID by username
| eval where LanID NOT IN("NAM\\OT00564",NAM\\CHawki5)
| eval Latest_Timestamp=strftime(Latest_Timestamp, "%Y-%m-%d %H:%M:%S")
| sort - Latest_Timestamp
| table Latest_Timestamp, Total_Connections, LanID, Connected_Hosts, Security_Mapping

Re: How to search for values not in a listing

gcusello — Thu, 29 Jan 2026 16:03:34 GMT

Hi @NanSplk01 ,

I suppose that you want to filter events where LanID hasn't values "NAM\\OT00564" or "NAM\\CHawki5",

is it correct?

anyway, the eval where row syntax is wrong, try this:

index IN ("pisupport", "pisupport-np") sourcetype=PIMessage ("procbook" AND "Successful Login") | rex field=_raw "Identity\sList:\s(?<identity>[^.]+)" | rex field=_raw "Username\s:\s(?<username>[^.]+)" | stats count AS Total_Connections, latest(_time) AS Latest_Timestamp, values(identity) AS Security_Mapping, values(host) AS Connected_Hosts, values(username) as LanID by username | where LanID NOT IN("NAM\\OT00564","NAM\\CHawki5") | sort -Latest_Timestamp | eval Latest_Timestamp=strftime(Latest_Timestamp, "%Y-%m-%d %H:%M:%S") | table Latest_Timestamp, Total_Connections, LanID, Connected_Hosts, Security_Mapping

then, it's always better to sort for a timestamp before changing the format.

Ciao.

Giuseppe

Re: How to search for values not in a listing

richgalloway — Thu, 29 Jan 2026 16:38:55 GMT

The in function used with the where command has a very different syntax from the IN operator used with the search command. An additional complication is neither in nor IN work well with the multi-value fields returned by stats. For that, you probably want mvfind or do the filtering before stats.

index IN ("pisupport", "pisupport-np") sourcetype=PIMessage ("procbook" AND "Successful Login") | rex field=_raw "Identity\sList:\s(?<identity>[^.]+)" | rex field=_raw "Username\s:\s(?<username>[^.]+)" | stats count AS Total_Connections, latest(_time) AS Latest_Timestamp, values(identity) AS Security_Mapping, values(host) AS Connected_Hosts, values(username) as LanID by username | eval discard=if(isnull(mvfind(LanID, "NAM\\OT00564|NAM\\CHawki5")),1,0) | where discard=1 | sort - Latest_Timestamp | eval Latest_Timestamp=strftime(Latest_Timestamp, "%Y-%m-%d %H:%M:%S") | table Latest_Timestamp, Total_Connections, LanID, Connected_Hosts, Security_Mapping

Re: How to search for values not in a listing

NanSplk01 — Thu, 29 Jan 2026 17:28:58 GMT

Giuseppe, unfortunately the search did not work for me. However, you are correct, I want to exclude those ID's from the results of my search and only list ID's that don't match them. I've tried a variety of ways, but I either get nothing, an error, or the ID's still show in the listing.

Re: How to search for values not in a listing

NanSplk01 — Thu, 29 Jan 2026 18:59:26 GMT

I even tried just the following, but it brought back the ID:

Re: How to search for values not in a listing

richgalloway — Thu, 29 Jan 2026 21:07:45 GMT

It's more efficient to filter before dedup. It means fewer results are returned to the search head for processing.

I believe you do not need to escape backslashes in strings. | where username!="NAM\OT00564" should be sufficient.

Re: How to search for values not in a listing

bowesmana — Fri, 30 Jan 2026 04:02:56 GMT

As @richgalloway says, "IN" functionality is different between the "search" command and the "where" command. The search is

| search username IN ("A","B","C")

The where command is

| where in(username, "A", "B", "C")

and these are both string matches. Note that the search command will interpret \ so you will need to escape \ as \\ but oddly enough, not in the where command.

I am not quite sure the purpose of this statement

| stats count AS Total_Connections, latest(_time) AS Latest_Timestamp, values(identity) AS Security_Mapping, values(host) AS Connected_Hosts, values(username) as LanID by username

because there is no point in doing values(username) as LanID because that will simply contain the same value as username because you are splitting by username.

So, you could do this before the stats

| where !in(username, "NAM\OT00564", "NAM\CHawki5") OR | search username NOT IN ("NAM\\OT00564", "NAM\\CHawki5")

and then you want username to be LanID, so just do

| rename username as LanID

Re: How to search for values not in a listing

bowesmana — Fri, 30 Jan 2026 04:05:56 GMT

NB: This is incorrect where in() syntax

| where LanID NOT IN("NAM\\OT00564","NAM\\CHawki5")

First parameter to where !in() is fieldname followed by string values.

Re: How to search for values not in a listing

PickleRick — Fri, 30 Jan 2026 16:23:33 GMT

Adding to what's already been said here, depending on your data and stats result, the "where" command logic might surprise you if you get multivalued fields.

I'm not sure what your initial intent was but remember than when dealing with multivalued fields Splunk generally applies conditions to each field value separately when deciding whether an event as a whole matches. So you have to use different searches for each of those cases:

- you want to exclude event for which _neither_ of values matches given value

- you want to exlude event which consists solely of the given value

- you want to remove a given value from multivalued field.

- possibly something else.

Also, I disagree here a bit with @gcusello - it's even better to use fieldformat than eval for formatting timestamps.

Re: How to search for values not in a listing

yuanliu — Sat, 31 Jan 2026 17:58:38 GMT

Maybe we should go back to basics: The data. Instead of trying to filter out, let's see if those username's are even in. How about testing

I have a suspicion that these user names you are trying to exclude do not match the expressions, therefore the above will not return anything.

In other words, the task is not to find or fine tune SPL, but to determine the exact users you are really trying to exclude. If you have difficulty with string for matching, please post exact username you get from raw data.

One more pointer about data and match string. Looking at the samples you give, "NAM\\OT00564" and "NAM\\CHawki5", they look too much like Windows domain users, but with double backslashes. Have you tried single backslash? Like this

Re: How to search for values not in a listing

NanSplk01 — Tue, 03 Feb 2026 12:15:20 GMT

I was finally able to get it to work with help from you guys:

Now I need to add in the other data so they get date and time.

Thanks to all

Re: How to search for values not in a listing

richgalloway — Tue, 03 Feb 2026 12:29:18 GMT

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.