topic Re: removing zero values or using where clause with multiple eval statements in Splunk Search

removing zero values or using where clause with multiple eval statements

agreer — Fri, 29 Dec 2017 17:40:37 GMT

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.

Re: removing zero values or using where clause with multiple eval statements

somesoni2 — Fri, 29 Dec 2017 19:10:46 GMT

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)

Re: removing zero values or using where clause with multiple eval statements

agreer — Fri, 29 Dec 2017 19:20:41 GMT

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 (")?

Re: removing zero values or using where clause with multiple eval statements

somesoni2 — Fri, 29 Dec 2017 19:35:30 GMT

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)

Re: removing zero values or using where clause with multiple eval statements

agreer — Fri, 29 Dec 2017 19:47:20 GMT

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'.

Re: removing zero values or using where clause with multiple eval statements

somesoni2 — Fri, 29 Dec 2017 19:49:23 GMT

Here you go.