Splunk Search

Splunk query not giving me results

punyanit
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

punyanit
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

punyanit
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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

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

richgalloway
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, Karma would be appreciated.
0 Karma

renjith_nair
Legend

@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
    
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...