How do you use a AND statement in a IF statement?

[agreer]

I am looking through log files and building a report that will give a list of usage based off those logs. Currently I am only showing one of the values, Portallogins, but additional data will be pulled in a similar fashion where Action will be one value and another column will be another value. I am using the following and the results of 'Portal Logins' is always 0 when it should not be. Any suggestions on what is wrong in this query would be greatly appreciated.

sourcetype = drupal_app_logs domain_type = "clientportal" email != "surfspamfree.com" email ="@littler.com"
|dedup _time,message
|eval mytime=strftime(_time, "%Y-%m-%d-%T")
|eval Portallogins=if((action="User Login" AND domain="Login Portal"),1,0)
|stats sum(Portallogins) as "Portal Logins" by email
|join type=left email
[|search index=onelogin_roll role_id{} != null]
|table email,firstname, lastname,last_login,"Portal Logins",company
|sort company

[niketn · Accepted solution]

@agreer, ideally there is nothing wrong with your query if the field names action and domain exist and have the same casing as per your query. Also the casing in the field values i.e. User Login and Login Portal should have same casing without leading or trailing spaces. You can try with the following just to be sure

| eval Portallogins=if((trim(upper(action))=trim(upper("User Login")) AND trim(upper(domain))=trim(upper("Login Portal"))),1,0)

If still it does not work may be evaluate which condition is going wrong by writing a test case:

|eval userLogin=if(action="User Login","matched",action)
|eval loginPortal=if(domain="Login Portal","matched",domain)

Following is a run anywhere search with the condition provided in your sample search which is working fine:

| makeresults 
| eval action="User Login",domain="Login Portal"
| append 
    [| makeresults 
| eval action="User Login",domain="Logout Portal"]
| fields - _time
| eval Portallogins=if((action="User Login" AND domain="Login Portal"),1,0)

[agreer]

It was the casing! Your first suggestion was what was needed. Thank you.

[niketn]

@agreer, I have converted to answer. Please accept to mark this as answered.

[agreer]

@niketn I have accepted this as the answer. To add on is it not possible to run multiple if statements. When I run the following, it changes the results you just helped fixed back to all 0's and shows nothing for "Portal Logins." When I only run the new if statement I do get data back but not when both are present:

sourcetype = drupal_app_logs domain_type = "clientportal" email != "surfspamfree.com" email ="@littler.com"
|dedup _time,message
|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)
|stats sum(Portallogins) as "Portal Logins" by email
|eval Globallogins=if(like (message,"%portal.littler.com/apps/global-guide"),1,0)
|stats sum(Globallogins) as "Global Logins" by email
|join type=left email
[|search index=onelogin_roll role_id{} != null]

|table email,firstname, lastname,company,last_login,"Portal Logins","Global Logins"
|sort company

[jplumsdaine22]

Be sure to accept the answer if correct!

[woodcock]

Try this:

(sourcetype = drupal_app_logs domain_type = "clientportal" email != "surfspamfree.com" email ="@littler.com")
OR ( index=onelogin_roll role_id{} != null)
| stats first(firstname) AS firstname first(lastname) AS lastname first(last_login) AS last_login first(company) AS company count(eval(action="User Login" AND domain="Login Portal")) as "Portal Logins" BY email 
| table email firstname lastname last_login "Portal Logins" company
| sort company

[agreer]

This still produces all 0's. I have also never been able to get the search between two indexes to work in the fashion you are using. It never brings int he data from the second index.

[woodcock]

Then your data must be disjointed. This type of search is bulletproof, if there is overlapping email field values in both datasets and all fields and values are named correctly.

[jplumsdaine22]

Without seeing an example event, this will be difficult, but two things come to mind. email!="surfspamfree.com" will filter out all events that do not contain an email field. So If some of your events do not have a value for email the will not be in the result set.

If you want to simply exclude events with email="surfspamfree.com" but want to include events with email=NULL, then put NOT email="surfspamfree.com in your search instead.

Also |eval mytime=strftime(_time, "%Y-%m-%d-%T") is redundant, as that field is never referenced. Again if you post some sample data it will be much easier to assist.

[agreer]

The email !="*surfspamfree.com" is so test data isn't returned in the results (forgot the * in the original query) and mytime will be referenced later. The problem needing assistance here is the if statement. How is an AND statement included in and if statement? Data would be:
action=
domain=
domain_type=
email=

[jplumsdaine22]

It sounds from your statement that you don't currently have an action field? If that's the case then (action="User Login" AND domain="Login Portal") will always evaluate to false, thus Portallogins will always eval to 0.

Is that correct? Perhaps you can share some event data? It may make what you're trying to do clearer.

[agreer]

All fields referenced do exist.