Splunk Search

removing zero values or using where clause with multiple eval statements

agreer
New Member

I am running the query below:

index=onelogin_roll role_id{} != null email!="*surfspamfree.com" email!="*littler.com" email != "unknown" company!=Fastcase
|dedup email
|join type email
     [|search sourcetype = drupal_app_logs domain_type = "clientportal" email != "*surfspamfree.com" email !="*@littler.com" email != "unknown"
      |eval mytime=strftime(_time, "%Y-%m-%d-%T") 
      |eval Portallogins=if((trim(upper(action))=trim(upper("User Login")) AND trim(upper(domain))!=trim(upper("Login Portal"))),1,0)
      |eval Globallogins=if(like (message,"%Global Guide%"),1,0)
      |eval GPSlogins=if(like (message,"%Littler GPS%"),1,0)
      |eval LCSlogins=if(like (message,"%CaseSmart&%") AND action="Main Navigation Page Visit",1,0)
      |stats max(mytime) as "Last Login" sum(Globallogins) as "Global Visits" sum(Portallogins) as "Portal Logins" sum(GPSlogins) as "GPS Visits" sum(LCSlogins) as "LCS Visits" by email]

|table email,firstname, lastname,company,title,"Last Login",password_changed_at,"Portal Logins","Global Visits","GPS Visits","LCS Visits"
|sort -"Portal Logins"

I do not want to return any 0 values for Portallogins or "Portal Logins." My research indicates the following would need to be added:

 |where Portallogins>0

Depending where this is placed the 0 values are not removed or it will sum the Portallogins and report 0 for all the other eval statements. Any advise wold be greatly appreciated.

0 Karma
1 Solution

somesoni2
Revered Legend

Since you're doing inner join, I don't think it'll make a difference. You can put you where clause inside your subsearch (probably best so that you can reduce the number of rows to be joined, which is very expensive BTW) or towards end of the search (of course, field is renamed so you'd use | where 'Portal Logins'>0)

View solution in original post

0 Karma

somesoni2
Revered Legend

Since you're doing inner join, I don't think it'll make a difference. You can put you where clause inside your subsearch (probably best so that you can reduce the number of rows to be joined, which is very expensive BTW) or towards end of the search (of course, field is renamed so you'd use | where 'Portal Logins'>0)

0 Karma

agreer
New Member

SMH, this was tried with:

| where "Portal Logins">"0"

with the results I identified in the original summary.

Your advice below works:

|where 'Portal Logins'>0

When should (') be used over (")?

0 Karma

somesoni2
Revered Legend

You'd use single quotes (for field whose name contains special characters including spaces) in eval and where. If you're using eval within a stats or timechart (e.g. | stats count(eval(status="Success")) as Success, then also you'd use single quotes. For all other commands or variation of commands, you'll use double quotes (rename, stats, timechart, bucket etc)

0 Karma

agreer
New Member

Thank you for clearing this up. If you change your comment to an answer I will gladly mark this correct. Didn't know the difference between "Portal Logins" and 'Portal Logins'.

0 Karma

somesoni2
Revered Legend

Here you go.

0 Karma
Get Updates on the Splunk Community!

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...

What's New in Splunk Observability - July 2025

What’s New?  We are excited to announce the latest enhancements to Splunk Observability Cloud as well as what ...