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
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
    
Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...