Splunk Search

Splunk query not giving me results

Path Finder

HI All,
Could any one help me in this on urgent basis:
My query is malfunctioning :

index=auto_prod_okta eventType="user.session.start" outcome.result="SUCCESS" OR outcome.result="FAILURE" NOT "outcome.reason"=INVALID_CREDENTIALS NOT "outcome.reason"=LOCKED_OUT NOT "outcome.reason"=PASSWORD_BASED_LOGIN_DISALLOWED NOT "outcome.reason"="null" NOT outcome.reason=VERIFICATION_ERROR earliest=-90d
| eval Success=if(eventType="outcome.result="SUCCESS"", ‘actor.alternateId’, null())
| eval Failure=if('outcome.result'="FAILURE", ‘actor.alternateId’, null())
| timechart span=1d count(Success) as Success dc(Failure) as Failure
| eval time_day = strftime(_time, "%D")
| eval Total=(Success+Failure)
| eval Login_Failure_Percent=round((Failure/Total)*100,4)
| table time_day Failure, Total, Login_Failure_Percent
| sort -time_day

Here eval is giving error like" Error in 'eval' command: The expression is malformed. Expected )."
In above query if there is no failure it should get value 0

Tags (2)
0 Karma
1 Solution

Path Finder

Hello Everyone ,

I have done this using other (unnecessary 😉 ) but it is working for me.
Here is the query:
index=auto_prod_okta eventType="user.session.start" outcome.result="SUCCESS" earliest=-90d

| eval Success=if(eventType="user.session.start", 'actor.alternateId', null())
| timechart span=1d count(Success) as Success | eval time_day = strftime(_time, "%D")
| join type=left time_day [search index=auto_prod_okta eventType="user.session.start" outcome.result="FAILURE" NOT "outcome.reason"=INVALID_CREDENTIALS NOT "outcome.reason"=LOCKED_OUT NOT "outcome.reason"=PASSWORD_BASED_LOGIN_DISALLOWED NOT "outcome.reason"="null" NOT outcome.reason=VERIFICATION_ERROR earliest=-90d | eval Failure=if('outcome.result'="FAILURE",'actor.alternateId', null())
| timechart span=1d count(Failure) as Failure| eval time_day = strftime(_time, "%D") ] |eval TCI=(Success+Failure) | eval Interaction_Failure_Percent(FCI/TCI)100=round((Failure/TCI)*100,4)
|table time_day, Success, Failure ,TCI, Interaction_Failure_Percent(FCI/TCI)100 | rename Failure AS "Failed Logins" Success AS "Success Logins" TCI AS "Total Logins" |sort -time_day

View solution in original post

0 Karma

Path Finder

Hello Everyone ,

I have done this using other (unnecessary 😉 ) but it is working for me.
Here is the query:
index=auto_prod_okta eventType="user.session.start" outcome.result="SUCCESS" earliest=-90d

| eval Success=if(eventType="user.session.start", 'actor.alternateId', null())
| timechart span=1d count(Success) as Success | eval time_day = strftime(_time, "%D")
| join type=left time_day [search index=auto_prod_okta eventType="user.session.start" outcome.result="FAILURE" NOT "outcome.reason"=INVALID_CREDENTIALS NOT "outcome.reason"=LOCKED_OUT NOT "outcome.reason"=PASSWORD_BASED_LOGIN_DISALLOWED NOT "outcome.reason"="null" NOT outcome.reason=VERIFICATION_ERROR earliest=-90d | eval Failure=if('outcome.result'="FAILURE",'actor.alternateId', null())
| timechart span=1d count(Failure) as Failure| eval time_day = strftime(_time, "%D") ] |eval TCI=(Success+Failure) | eval Interaction_Failure_Percent(FCI/TCI)100=round((Failure/TCI)*100,4)
|table time_day, Success, Failure ,TCI, Interaction_Failure_Percent(FCI/TCI)100 | rename Failure AS "Failed Logins" Success AS "Success Logins" TCI AS "Total Logins" |sort -time_day

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

@punyanit If your problem is resolved, please accept an answer to help future readers.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

I think the problem lies in the first eval. The embedded quotes are confusing the parser.

Instead of | eval Success=if(eventType="outcome.result="SUCCESS"", ‘actor.alternateId’, null()) try | eval Success=if(eventType="outcome.result=\"SUCCESS\"", ‘actor.alternateId’, null()).

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

@punyanit ,

Test against your data and validate

Changes made :

  • Escaped " around SUCCESS
  • Removed quotes around actor.alternateId

    index=auto_prod_okta eventType="user.session.start" outcome.result="SUCCESS" OR outcome.result="FAILURE" NOT "outcome.reason"=INVALID_CREDENTIALS NOT "outcome.reason"=LOCKED_OUT NOT "outcome.reason"=PASSWORD_BASED_LOGIN_DISALLOWED NOT "outcome.reason"="null" NOT outcome.reason=VERIFICATION_ERROR earliest=-90d
    | eval Success=if(eventType="outcome.result=\"SUCCESS\"",actor.alternateId, null())
    | eval Failure=if(outcome.result="FAILURE", actor.alternateId, null())
    | timechart span=1d count(Success) as Success dc(Failure) as Failure
    | eval time_day = strftime(_time, "%D")
    | eval Total=(Success+Failure)
    | eval Login_Failure_Percent=round((Failure/Total)*100,4)
    | table time_day Failure, Total, Login_Failure_Percent
    | sort -time_day
    
0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!