Splunk Search

Different element between two stats values() lists

rsaude
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

Thanks in advanced,
Rsaude

Tags (2)
0 Karma
1 Solution

to4kawa
Ultra Champion

my answer

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

View solution in original post

to4kawa
Ultra Champion

my answer

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

rsaude
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'.*?)\]"
 | 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
0 Karma

rsaude
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

0 Karma

rsaude
Path Finder

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

0 Karma

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

0 Karma

rsaude
Path Finder

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

0 Karma

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

0 Karma

rsaude
Path Finder
0 Karma

rsaude
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'.*?)\]"
| 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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

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.

0 Karma

to4kawa
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
0 Karma

rsaude
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

0 Karma

rsaude
Path Finder

yes they both have it

0 Karma

to4kawa
Ultra Champion
| 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

0 Karma

rsaude
Path Finder

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

0 Karma

13tsavage
Communicator

Give this a try:

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

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...