Technical rule query

[ewalton]

Hello, 

I would like to create a technical rule based on a users attribute in a particular endpoint. Is it possible to query objects on a table other than users when setting up a technical rule? Below is the query I am using. The query works using the Data Analyzer and an SQL query in analytics but it does not work with configuring a technical rule. I get a message that says "Invalid Condition".

Thank you in advance!

SELECT
u.username,
u.firstname,
u.lastname,
u.employeetype,
u.createdate,
u.startdate,
u.enddate,
u.statuskey AS SAVIYNTSTATUS,
accounts.name AS ADUSERNAME,
endpoints.ENDPOINTNAME,
accounts.STATUS AS ADSTATUS,
accounts.LASTLOGONDATE AS LASTADLOGON,
accounts.accountkey as acctKey
FROM
accounts
LEFT JOIN user_accounts ON accounts.ACCOUNTKEY = user_accounts.ACCOUNTKEY
LEFT JOIN endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN users u ON u.userkey = user_accounts.userkey
WHERE
endpoints.ENDPOINTNAME IN ('Active Directory')
AND accounts.STATUS LIKE '1'
AND u.employeetype = 'EpicCare Link'
AND u.statuskey = 1;

[dgandhi]

Please provide screenshot of the technical rule that you are trying.

Thanks

[ewalton]

Thank you! I have attached the files to this thread.

[ewalton]

Thank you. Screenshots attached.

[dgandhi]

Attached format is wrong and wont work. Please write advance query in below format as mentioned in the document.

https://docs.saviyntcloud.com/bundle/SSM-Admin-v55x/page/Content/Chapter05-Policies/Creating-Technic...

Advanced Config ON - If you need to write complex queries that have multiple AND and OR conditions, it is recommended to set Advanced Config ON and specify conditions using the query as the UI does not support multiple conditions.

Advanced Query

Select Advanced Config as ON to write complex multiple queries as the condition for triggering the Technical Rule.  The user attributes specified in the Advanced Config query are case-sensitive. The supported user attributes to be used in the Advanced Config are as follows: username,firstname,preferedFirstName,lastname,middlename,street,city,comments,statuskey,startdate,enddate,manager,password,location,jobCode,jobDescription,employeeType, systemUserName,departmentNumber,title,state,companyname,costcenter,departmentname,employeeclass,entity,jobcodedesc,locationdesc,locationnumber,siteid,orgunitid,region,regioncode, owner,employeeid,lastsyncdate,createdate,email,phonenumber,job_function,country,displayname,locale,customproperty1-65 Example to filter users who are 'Employee' AND active in IGA AND created date same as system date AND systemusernmae not null AND customproperty12 value null  Example query: a.employeeclass = 'Employee' AND a.statuskey=1 AND date(a.createdate) = date(sysdate()) AND a.systemUserName is not null and a.customproperty12 is null Example query: employeeclass='Contractor' and date(FROM_UNIXTIME(substr(a.customproperty6,7,10))) <= date(sysdate()) and (a.customproperty11 is null OR upper(a.customproperty11) != 'YES') users who are 'Contractor' with additional conditions

[ewalton]

Thank you for your response. How do I include an attribute from a specific endpoint in your example query? I am using the advaced query feature when creating the technical rule. Can you reference tables other than "users" in the technical rule query?

[dgandhi]

I believe only user as in the document or sample advance query no other objects (like accounts , endpoints ) are referred 

Thanks

[rushikeshvartak]

You can write query and try preview. Ideally it won’t work but you can give try

[ewalton]

Thank you for your response. The query I am using is listed below. Is this an issue with the syntax I am using?
SELECT
u.username,
u.firstname,
u.lastname,
u.employeetype,
u.createdate,
u.startdate,
u.enddate,
u.statuskey AS SAVIYNTSTATUS,
accounts.name AS ADUSERNAME,
endpoints.ENDPOINTNAME,
accounts.STATUS AS ADSTATUS,
accounts.LASTLOGONDATE AS LASTADLOGON,
accounts.accountkey as acctKey
FROM
accounts
LEFT JOIN user_accounts ON accounts.ACCOUNTKEY = user_accounts.ACCOUNTKEY
LEFT JOIN endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN users u ON u.userkey = user_accounts.userkey
WHERE
endpoints.ENDPOINTNAME IN ('Active Directory')
AND accounts.STATUS LIKE '1'
AND u.employeetype = 'New'
AND u.statuskey = 1;