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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...