can we use ARSTask table in Modifyuserdatajson

[sppnra]

Hi Saviynt Team,

I have a use case where i need to check the arstask status and then populate one CP. So I'm just wondering if we can use the ARStask table and join with user table in the PreProcessor query. 

if yes, if you can give me a sample that would be really  helpful.

Thanks,

[rushikeshvartak]

You can use 

[sppnra]

is this look good to you @rushikeshvartak 

"USERS": "SELECT USERKEY,username,customproperty47,departmentname,termDate,customproperty45,customproperty48,firstname,lastname,customproperty2,customproperty3,customproperty27,customproperty21,customproperty30,customproperty32,country,customproperty52,enddate,employeeclass FROM USERS",
"ARSTASKS":"SELECT USERKEY,status FROM ARSTASKS where status=6"

I m getting this error

[dgandhi]

Can you paste the entire query that you are using like below?

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,location,EMPLOYEECLASS,CUSTOMPROPERTY6,MIDDLENAME,SYSTEMUSERNAME from USERS ",
"ENTITLEMENT_VALUES": "SELECT entitlementtypekey,customproperty1,customproperty2,customproperty3,customproperty4,customproperty5,customproperty6,customproperty7,entitlement_value FROM ENTITLEMENT_VALUES where entitlementtypekey in (224,240)"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY1",
"CITY",
"COUNTRY",
"CUSTOMPROPERTY2",
"CUSTOMPROPERTY3",
"STATE",
"STREET",
"CUSTOMPROPERTY4",
"CUSTOMPROPERTY6",
"SYSTEMUSERNAME"
],
"TABLEINDEXES": {
"currentusers": [
"username",
"location",
"EMPLOYEECLASS"
],
"CURRENTENTITLEMENT_VALUES": [
"entitlementtypekey",
"entitlement_value"
]
},
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY1=(select CURRENTENTITLEMENT_VALUES.customproperty1 from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=240 and CURRENTENTITLEMENT_VALUES.entitlement_value =NEWUSERDATA.LOCATION) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CITY=(select CURRENTENTITLEMENT_VALUES.customproperty2 from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=240 and CURRENTENTITLEMENT_VALUES.entitlement_value =NEWUSERDATA.LOCATION) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.COUNTRY=(select CURRENTENTITLEMENT_VALUES.customproperty3 from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=240 and CURRENTENTITLEMENT_VALUES.entitlement_value =NEWUSERDATA.LOCATION) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY2=(select CURRENTENTITLEMENT_VALUES.customproperty4 from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=240 and CURRENTENTITLEMENT_VALUES.entitlement_value =NEWUSERDATA.LOCATION) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY3=(select CURRENTENTITLEMENT_VALUES.customproperty5 from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=240 and CURRENTENTITLEMENT_VALUES.entitlement_value =NEWUSERDATA.LOCATION) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.STATE=(select CURRENTENTITLEMENT_VALUES.customproperty6 from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=240 and CURRENTENTITLEMENT_VALUES.entitlement_value =NEWUSERDATA.LOCATION) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.STREET=(select CURRENTENTITLEMENT_VALUES.customproperty7 from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=240 and CURRENTENTITLEMENT_VALUES.entitlement_value =NEWUSERDATA.LOCATION) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY4=(select CURRENTENTITLEMENT_VALUES.entitlement_value from CURRENTENTITLEMENT_VALUES where CURRENTENTITLEMENT_VALUES.entitlementtypekey=224 and CURRENTENTITLEMENT_VALUES.customproperty1 LIKE CONCAT('%', NEWUSERDATA.LOCATION, '%')and CURRENTENTITLEMENT_VALUES.customproperty2 LIKE CONCAT('%', NEWUSERDATA.EMPLOYEECLASS, '%')) ",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY6 = case when CURRENTUSERS.CUSTOMPROPERTY6 is not null and CURRENTUSERS.CUSTOMPROPERTY6 != '' then CURRENTUSERS.CUSTOMPROPERTY6 when (CURRENTUSERS.CUSTOMPROPERTY6 IS NULL or CURRENTUSERS.CUSTOMPROPERTY6 = '') AND (NEWUSERDATA.MIDDLENAME IS NOT NULL and NEWUSERDATA.MIDDLENAME != '') THEN CONCAT(CONCAT(UPPER(SUBSTRING(NEWUSERDATA.firstname,1,1)),LOWER(SUBSTRING(NEWUSERDATA.firstname,2))),' ',UPPER(SUBSTRING(NEWUSERDATA.middlename,1,1)),'.',' ',CONCAT(UPPER(SUBSTRING(NEWUSERDATA.lastname,1,1)),LOWER(SUBSTRING(NEWUSERDATA.lastname,2)))) WHEN (CURRENTUSERS.CUSTOMPROPERTY6 IS NULL or CURRENTUSERS.CUSTOMPROPERTY6='') AND (NEWUSERDATA.MIDDLENAME IS NULL or NEWUSERDATA.MIDDLENAME ='') THEN CONCAT(CONCAT(UPPER(SUBSTRING(NEWUSERDATA.firstname,1,1)),LOWER(SUBSTRING(NEWUSERDATA.firstname,2))),' ',CONCAT(UPPER(SUBSTRING(NEWUSERDATA.lastname,1,1)),LOWER(SUBSTRING(NEWUSERDATA.lastname,2)))) end",
"CUSTOMFUNCTION###FUNCTION1"
],
"CUSTOMFUNCTIONS": {
"FUNCTION1": {
"FULLCLASSNAME": "com.saviynt.utility.Transformation",
"METHODNAME": "doCustomPreprocess"
}
}
}

[sppnra]

Hi @dgandhi  @rushikeshvartak 

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,customproperty47,departmentname,termDate,customproperty45,customproperty48,firstname,lastname,customproperty2,customproperty3,customproperty27,customproperty21,customproperty30,customproperty32,country,customproperty52,enddate,employeeclass FROM USERS",
"ARSTASKS":"select userkey,status FROM ARSTASKS where status=6"
},
"COMPUTEDCOLUMNS": [
"customproperty47",
"departmentName",
"customproperty45",
"customproperty48",
"firstname",
"lastname",
"customproperty2",
"customproperty3",
"customproperty27",
"username",
"customproperty21",
"customproperty30",
"country",
"customproperty32",
"customproperty52",
"termDate",
"enddate",
"employeeclass",
"userkey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET customproperty47 = CASE WHEN departmentName='Applications' THEN 'EMEA Technology Solutions' WHEN departmentName='Architecture' THEN 'EMEA Technology Solutions' WHEN departmentName='Building Surveying' THEN 'Project & Building Consultancy' WHEN departmentName='Central London Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='Colliers Capital' THEN 'Colliers Global Investors' WHEN departmentName='Commercial Valuations 1' THEN 'Valuation & Advisory Services' WHEN departmentName='Commercial Valuations 2' THEN 'Valuation & Advisory Services' WHEN departmentName='Commercial Valuations 3' THEN 'Valuation & Advisory Services' WHEN departmentName='Commercial Valuations 4' THEN 'Valuation & Advisory Services' WHEN departmentName='Cost Management' THEN 'Project & Building Consultancy' WHEN departmentName='Digital Marketing' THEN 'Marketing & Communications' WHEN departmentName='EMEA Flex & Workplace' THEN 'Occupier Services EMEA' WHEN departmentName='Infrastructure' THEN 'EMEA Technology Solutions' WHEN departmentName='Marketing' THEN 'Marketing & Communications' WHEN departmentName='Midland Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='Nile Management' THEN 'Investment Property Management' WHEN departmentName='PMO' THEN 'EMEA Technology Solutions' WHEN departmentName='Portfolio Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='Professional' THEN 'Landlord & Occupier Advisory' WHEN departmentName='Project Management' THEN 'Project & Building Consultancy' WHEN departmentName='Residential International Properties' THEN 'Residential' WHEN departmentName='Scotland Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='Service Delivery' THEN 'EMEA Technology Solutions' WHEN departmentName='Strategic Advisory' THEN 'Retail Agency' WHEN departmentName='UK Treasury' THEN 'Occupier Services EMEA' WHEN departmentName='Valuation & Appeals' THEN 'Rating' WHEN departmentName='EMEA Corporate Capital Solutions' THEN 'Occupier Services EMEA' WHEN departmentName='EMEA Enterprise Clients' THEN 'Occupier Services EMEA' WHEN departmentName='EMEA PMO & Project Management' THEN 'Occupier Services EMEA' WHEN departmentName='Tax & Corporate Development' THEN 'EMEA Finance' WHEN departmentName='UK Enterprise Clients' THEN 'Occupier Services EMEA' WHEN departmentName='Colliers Asset Services' THEN 'Asset Services' WHEN departmentName='Energy & Sustainability' THEN 'Project & Building Consultancy' WHEN departmentName='Exec PA Group' THEN 'UK Executives' WHEN departmentName='UK' THEN '' WHEN departmentName='Data' THEN 'EMEA Technology Solutions' WHEN departmentName='Environmental' THEN 'Project & Building Consultancy' WHEN departmentName='Knowledge' THEN 'EMEA Technology Solutions' WHEN departmentName='Security' THEN 'EMEA Technology Solutions' WHEN departmentName='Salesforce COE' THEN 'EMEA Technology Solutions' WHEN departmentName='UK Applications' THEN 'EMEA Technology Solutions' WHEN departmentName='Digital' THEN 'EMEA Marketing & Communications' WHEN departmentName='Creative' THEN 'EMEA Marketing & Communications' WHEN departmentName='Content' THEN 'EMEA Marketing & Communications' WHEN departmentName='Rate Account Management' THEN 'Rating' WHEN departmentName='PBC Leadership' THEN 'Project & Building Consultancy' WHEN departmentName='Project Monitoring' THEN 'Project & Building Consultancy' WHEN departmentName='Compliance, Risk & Data' THEN 'Valuation & Advisory Services' WHEN departmentName='South West Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='North West Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='North East Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='Residential Valuations' THEN 'Valuation & Advisory Services' WHEN departmentName='Design & Build' THEN 'Define' WHEN departmentName='Architecture & Planning' THEN 'Define' WHEN departmentName='People & Places Advisory' THEN 'Define' WHEN departmentName='Legal and Compliance' THEN 'Legal & Compliance' WHEN departmentName='Business Development Unit' THEN 'Rating' WHEN departmentName='Marcomms' THEN 'Marketing & Communications' WHEN departmentName='PR' THEN 'Marketing & Communications' WHEN departmentName='Bid Team' THEN 'Marketing & Communications' WHEN departmentName='IT' THEN 'EMEA Technology Solutions' WHEN departmentName='Legal' THEN 'Finance, Legal & Compliance' WHEN departmentName='Valuation and Advisory Services' THEN 'Valuation & Advisory Services' WHEN departmentName='Communication and Marketing' THEN 'Marketing & Communications' WHEN departmentName='FP&A and M&A' THEN 'EMEA Finance' WHEN departmentName='Tax & Corporate Development​' THEN 'EMEA Finance' WHEN departmentName='Internal Audit​' THEN 'EMEA Finance' WHEN departmentName='Key Account Services & ​Business Line Marketing' THEN 'EMEA Marketing & Communications' WHEN departmentName='Business Development Centre' THEN 'EMEA Marketing & Communications' WHEN departmentName='Research' THEN 'EMEA Marketing & Communications' WHEN departmentName='EMEA Occupier Services' THEN 'Occupier Services EMEA' WHEN departmentName='Retail Agency North' THEN 'Retail Agency' WHEN departmentName='Retail Agency South' THEN 'Retail Agency' WHEN departmentName='Retail Agency Scotland' THEN 'Retail Agency' ELSE departmentname END",
"UPDATE NEWUSERDATA SET customproperty45 = REPLACE(REPLACE(customproperty45, '[', ''), ']', '')",
"UPDATE NEWUSERDATA SET customproperty48 = REPLACE(REPLACE(customproperty48, '[', ''), ']', '')",
"UPDATE NEWUSERDATA SET firstname =CASE WHEN firstname is NULL THEN customproperty2 ELSE firstname END",
"UPDATE NEWUSERDATA SET lastname =CASE WHEN lastname is NULL THEN customproperty3 ELSE lastname END",
"UPDATE NEWUSERDATA SET customproperty27 = SUBSTRING(username, 2)",
"UPDATE NEWUSERDATA SET customproperty30 = CASE WHEN country = 'Poland' AND customproperty21 IS NOT NULL THEN SUBSTRING(customproperty21, 3) ELSE customproperty30 END",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME LEFT JOIN CURRENTARSTASKS ON NEWUSERDATA.USERKEY = CURRENTARSTASKS.userkey SET NEWUSERDATA.customproperty52 =CASE WHEN CURRENTUSERS.termDate IS NOT NULL AND FROM_UNIXTIME(SUBSTRING_INDEX(SUBSTRING_INDEX(NEWUSERDATA.termDate, '(', -1), ')', 1) / 1000, '%Y-%m-%d %H:%i:%s') != CURRENTUSERS.termDate AND CURRENTARSTASKS.status = 6 THEN 'Date Changed' WHEN CURRENTUSERS.employeeclass = 'Contingent Worker' AND FROM_UNIXTIME(SUBSTRING_INDEX(SUBSTRING_INDEX(NEWUSERDATA.enddate, '(', -1), ')', 1) / 1000, '%Y-%m-%d %H:%i:%s') != CURRENTUSERS.enddate AND CURRENTARSTASKS.status = 6 THEN 'contingent extend enddate' END",
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME LEFT JOIN CURRENTARSTASKS ON NEWUSERDATA.USERKEY = CURRENTARSTASKS.userkey SET NEWUSERDATA.customproperty52 =CASE WHEN NEWUSERDATA.termDate IS NULL AND CURRENTUSERS.termDate IS NOT NULL AND CURRENTARSTASKS.status = 6 THEN 'Leaver Cancelled' END"
]
}