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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...