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.
... View more