How to exclude user name that start with the number "0" on a correlation search on ES?

[ajromero]

how to exclude user name that start with the number "0" on a correlation search on ES

This is the query:

| from inputlookup:access_tracker | stats min(firstTime) as firstTime,max(lastTime) as lastTime by user | where ((now()-'lastTime')/86400)>90

and I want to remove all user that start with "0"

Thank you

[tshah-splunk]

Hey @ajromero,

You can append  | search user != 0* to the query and exclude users starting with 0. You search query should look something like below:

| from inputlookup:access_tracker | stats min(firstTime) as firstTime,max(lastTime) as lastTime by user | where ((now()-'lastTime')/86400)>90
| search user != 0*

[ajromero]

Hi tshah-splunk

I tried that and it worked, but just realized that there more users with different names like these

How can I exclude that kind of users?

Thank you,

Araceli

[tofa · Accepted solution]

Hi Araceli,

Possibly a regex exclusion:

| from inputlookup:access_tracker | stats min(firstTime) as firstTime,max(lastTime) as lastTime by user | where ((now()-'lastTime')/86400)>90
| regex user!="^0|^[0-9a-zA-Z\-]+$|<YOURPATTERNHERE>"

 Or just the opposite: if you, for example, got user with firstname.lastname format in your company:

| from inputlookup:access_tracker | stats min(firstTime) as firstTime,max(lastTime) as lastTime by user | where ((now()-'lastTime')/86400)>90
| regex user="^[A-Za-z]+\.[A-Za-z]+"​

[ajromero]

Hi,

that actually worked pretty good for me. 

thank you