topic Re: Different element between two stats values() lists in Splunk Search

Different element between two stats values() lists

rsaude — Thu, 06 Feb 2020 17:51:58 GMT

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

Thanks in advanced,
Rsaude

Re: Different element between two stats values() lists

13tsavage — Thu, 06 Feb 2020 18:44:40 GMT

Give this a try:

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

Re: Different element between two stats values() lists

to4kawa — Fri, 07 Feb 2020 03:33:27 GMT

| makeresults 
| eval _raw="AllUsers ModifiedUsers
Usr1            Usr4
Usr3            Usr2
Usr2            Usr1
Usr4"
| multikv forceheader=1
| 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

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 10:00:36 GMT

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

Re: Different element between two stats values() lists

to4kawa — Fri, 07 Feb 2020 10:35:32 GMT

 | makeresults 
 | eval _raw="AllUsers,ModifiedUsers
 admin,splunk
 splunk,tenable.admin
 tenable.admin,"
 | multikv forceheader=1
 | 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'.*?)\]
| transaction startswith="created user" endswith="*modified*password*"
| 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.

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 10:50:36 GMT

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

Re: Different element between two stats values() lists

rsaude — Wed, 30 Sep 2020 04:08:15 GMT

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

Here is the full query:
index=xxx (module=auth message="login") OR (module=user (message="modified*password" OR message="created user"))
| rex field=message "[(?'usr'.?)].[(?'usr_mod'.?)]"
| transaction startswith="created user" endswith="*modified*password"
| where eventcount=3 AND usr=usr_mod
| stats values(user) as AllUsers, values(usr_mod) as ModifiedUsers

Re: Different element between two stats values() lists

to4kawa — Wed, 30 Sep 2020 04:08:19 GMT

| 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.

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 11:16:31 GMT

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

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 11:19:51 GMT

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.

Re: Different element between two stats values() lists

to4kawa — Fri, 07 Feb 2020 11:24:44 GMT

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.

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 11:28:16 GMT

https://imgur.com/lv3cTnu

Re: Different element between two stats values() lists

to4kawa — Fri, 07 Feb 2020 11:29:51 GMT

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

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 11:32:30 GMT

yes they both have it

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 11:42:05 GMT

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

Re: Different element between two stats values() lists

to4kawa — Fri, 07 Feb 2020 12:02:26 GMT

@rsaude
I find the problem and fixed it. please confirm my answer.

Re: Different element between two stats values() lists

rsaude — Fri, 07 Feb 2020 13:33:27 GMT

@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'.*?)\]"
| transaction startswith="created user" endswith="*modified*password*"
| 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

You had
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

Re: Different element between two stats values() lists

rsaude — Mon, 10 Feb 2020 10:03:22 GMT

Corrected Code

 index=tenable (module=auth message="*login*") OR (module=user (message="*modified*password*" OR message="*created user*"))
 | rex field=message "\[(?'usr'.*?)\].*\[(?'usr_mod'.*?)\]"
 | transaction startswith="created user" endswith="*modified*password*"
 | 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