SPL help - enterprise security

[iherb_0718]

Trying to modify this default correlation search:

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

I want to exclude from this search if the field "user" includes a value that begins with "bob"

Thanks in advance

[scelikok · Accepted solution]

Hi @iherb_0718,

I didn't notice pipe , normally there is no need from command also but below should work based on your correlation search;

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

or

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

 Both should be ok;

If this reply helps you an upvote is appreciated.

[scelikok]

Hi @iherb_0718,

You can use below;

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

If this reply helps you an upvote is appreciated.

[iherb_0718]

Sceikok,

that's exactly what I tried but it doesn't work.  By the way, there's a PIPE before the WHERE so i'm sure you meant it like this

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

The problem is it doesn't like the asterisk after bob.  If I type in an exact user, I can see it gets excluded. If I include the asterik, I see all the "BOBs"

[iherb_0718]

anyone please?

[iherb_0718]

Sceikok thanks for the quick response. I want to EXCLUDE bob.   Therefore what booleon would that be? It won't be "LIKE"

[scelikok]

Hi @iherb_0718,

Please try this;

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

If this reply helps you an upvote is appreciated.