Splunk Search

## Different element between two stats values() lists

Path Finder
``````search made before ...| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers
``````

And it returns two lists

``````Usr1            Usr4
Usr3            Usr2
Usr2            Usr1
Usr4
``````

My purpose is to get the users that weren't modified i.e:

`````` Usr3
``````

Rsaude

Tags (2)
1 Solution
Ultra Champion

`````` | makeresults
| eval _raw="AllUsers,ModifiedUsers
| stats values(*) as *
| table AllUsers ModifiedUsers
`comment("this is your result sample. from here, the logic")`
| stats values(*) as * by AllUsers
| eval check = if(match(ModifiedUsers,"^".AllUsers."\$"),1,0)
| stats values(eval(if(check=0,AllUsers,NULL))) as AllUsers
``````

Recommend:

``````index=tenable (module=auth message="*login*") OR (module=user (message="*modefied*password*" OR message="*created user*))
| rex field=message "\[(?'usr'.*?)\].*\[(?'usr_mod'.*?)\]
| where eventcount=3 usr=usr_mod
| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers
| stats values(*) as * by AllUsers
| eval check = if(match(ModifiedUsers,"^".AllUsers."\$"),1,0)
| stats values(eval(if(check=0,AllUsers,NULL))) as AllUsers
``````

`>I couldn't get this code to work with only the logic or with the full code...`

@rsaude

OK, REGEX was wrong. I've fixed it.

Ultra Champion

`````` | makeresults
| eval _raw="AllUsers,ModifiedUsers
| stats values(*) as *
| table AllUsers ModifiedUsers
`comment("this is your result sample. from here, the logic")`
| stats values(*) as * by AllUsers
| eval check = if(match(ModifiedUsers,"^".AllUsers."\$"),1,0)
| stats values(eval(if(check=0,AllUsers,NULL))) as AllUsers
``````

Recommend:

``````index=tenable (module=auth message="*login*") OR (module=user (message="*modefied*password*" OR message="*created user*))
| rex field=message "\[(?'usr'.*?)\].*\[(?'usr_mod'.*?)\]
| where eventcount=3 usr=usr_mod
| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers
| stats values(*) as * by AllUsers
| eval check = if(match(ModifiedUsers,"^".AllUsers."\$"),1,0)
| stats values(eval(if(check=0,AllUsers,NULL))) as AllUsers
``````

`>I couldn't get this code to work with only the logic or with the full code...`

@rsaude

OK, REGEX was wrong. I've fixed it.

Path Finder

Corrected Code

`````` index=tenable (module=auth message="*login*") OR (module=user (message="*modified*password*" OR message="*created user*"))
| rex field=message "\[(?'usr'.*?)\].*\[(?'usr_mod'.*?)\]"
| where eventcount=3 AND usr=usr_mod
| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers | stats values(*) as * by AllUsers
| eval check = if(match(ModifiedUsers,"^".AllUsers."\$"),1,0)
| stats values(eval(if(check=0,AllUsers,NULL))) as AllUsers
``````
Path Finder

My bad, i got it to work with the full makeresult but i cant work it with my own, i'm gonna edit the original post and post the full query, there might be something wrong with the original query

Path Finder

i can't seem to change the question so i'll post it on the comments:

Here is the full query:
| rex field=message "[(?'usr'.?)].[(?'usr_mod'.?)]"
"
| where eventcount=3 AND usr=usr_mod
| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers

Ultra Champion
``````| where eventcount=3 AND usr=usr_mod
``````

This query selects user and user_mod both have same value.
Hence, `| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers` result is same values.

Path Finder

there are 3 fields, user, usr and usr_mod, they do not have the same values

Ultra Champion

I see , sorry. I have a mistake.

`| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers`
what's this real results?

I thought they had the same value, but they seemed different.

Path Finder
Path Finder

@to4kawa you had it praticly right, with a few erros,

``````index=tenable (module=auth message="*login*") OR (module=user (message="*modified*password*" OR message="*created user*"))
| rex field=message "\[(?'usr'.*?)\].*\[(?'usr_mod'.*?)\]"
| where eventcount=3 AND usr=usr_mod
| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers | stats values(*) as * by AllUsers
| eval check = if(match(ModifiedUsers,"^".AllUsers."\$"),1,0)
| stats values(eval(if(check=0,AllUsers,NULL))) as AllUsers
``````

modefied and it was modified
missing " at the end of the first and second line
where is missing an AND between the conditions

fix it so that i can call it an Correct awnser plss

Btw tyy

Ultra Champion

@rsaude

Path Finder

my final purpose is to get the diference between all users that logged in and the users that changed their passwords on first login, just to check who didnt changed it on the first login.

Ultra Champion
``````index=xxx (module=auth message="login") OR (module=user (message="modified*password" OR message="created user"))
``````

module: auth and module: user both have `user` field?

if the assumption is right, the query is simple.

``````index=xxx (module=auth message="login") OR (module=user (message="modified*password" OR message="created user"))
| stats dc(message) as flag by user
| where flag!=3
``````
Path Finder

that can't be the case, because i need to have exactly 3 events in this order, one that is the creation of the account, other that is login and the last is the password reset. so i used transaction

Path Finder

yes they both have it

Ultra Champion
``````| makeresults
| eval _raw="AllUsers ModifiedUsers
Usr1            Usr4
Usr3            Usr2
Usr2            Usr1
Usr4"
| stats values(*) as *
| table AllUsers ModifiedUsers
`comment("this is your result sample. from here, the logic")`
| stats values(*) as * by AllUsers
| eval check=if(match(ModifiedUsers,AllUsers),1,0)
| stats values(eval(if(check=0,AllUsers,NULL))) as AllUsers
``````

`stats` and `stats`

Path Finder

I couldn't get this code to work with only the logic or with the full code...

Communicator

Give this a try:

| where user!="" AND usr_mod==""
| stats list

Get Updates on the Splunk Community!