How to search for values not in a listing

[NanSplk01]

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

[bowesmana]

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

[richgalloway]

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

[NanSplk01]

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

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>[^.]+)"
| dedup username
| where username!="NAM\\OT00564"
| table username

[yuanliu]

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

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>[^.]+)"
| where username=="NAM\\OT00564"
| table username

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

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>[^.]+)"

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

[richgalloway]

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.

[gcusello]

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

[NanSplk01]

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

index IN ("pisupport", "pisupport-np") sourcetype=PIMessage procbook "Successful Login"
| rex field=_raw "Username\s*:\s*(?<username>\S+)"
| where NOT match(username, "(?i)(OT00564|CHawki5|DPerez3|AGross2)")
| dedup username
| table username

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

Thanks to all

[richgalloway]

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

[PickleRick]

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.

[bowesmana]

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.

[NanSplk01]

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.