Splunk query not giving me results

[punyanit]

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

[punyanit · Accepted solution]

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

[richgalloway]

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

[richgalloway]

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

[renjith_nair]

@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