Splunk Search

removing zero values or using where clause with multiple eval statements

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

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

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

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

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

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

Revered Legend

Here you go.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!