Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Account status update by customquery

sabnam_panda
New Contributor
New Contributor

Hi All,

we are using below custumquery  job to update disconnected application account status manually suspended to suspended from Import in 5.5 SP5.x but now our upgrade happen to 23.1 and we are unable to use this. Can any body help us.

Query

update accounts set status='SUSPENDED FROM IMPORT SERVICE' where accountkey in(select * from(
select accountkey from
accounts a,endpoints ep
where a.endpointkey=ep.endpointkey and ep.customproperty10='Disconnected' and a.status in('Manually Suspended'))tb1Tmp);

6 REPLIES 6

rushikeshvartak
All-Star
All-Star

Try below query 

SELECT Accounts__status,a.accountkey as accounts__PRIMARYKEY from accounts a where a.accountkey IN (
SELECT a.accountkey
FROM accounts a
JOIN endpoints ep ON a.endpointkey = ep.endpointkey
WHERE ep.customproperty10 = 'Disconnected'
AND a.status IN ('Manually Suspended')
);


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Hi,

We tried below query and it gives output.

SELECT status as ACCOUNTS__STATUS,a.accountkey as ACCOUNTS__PRIMARYKEY from accounts a where a.accountkey IN (
SELECT a.accountkey
FROM accounts a
JOIN endpoints ep ON a.endpointkey = ep.endpointkey
WHERE ep.customproperty10 = 'Disconnected'
AND a.status IN ('Manually Suspended'));

But we want to change the Status to 'Suspended from Import Service' if it 'Manually Suspended'.

So how to print hardcode value as here in select statement.

SELECT 'Suspended from Import Service'as ACCOUNTS__STATUS,a.accountkey as ACCOUNTS__PRIMARYKEY from accounts a where a.accountkey IN (
SELECT a.accountkey
FROM accounts a
JOIN endpoints ep ON a.endpointkey = ep.endpointkey
WHERE ep.customproperty10 = 'Disconnected'
AND a.status IN ('Manually Suspended'));


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

sabnam_panda
New Contributor
New Contributor

Thanks rushikesh, Its working

alvian
New Contributor III
New Contributor III

I'm wondering if I change or update the account status using a custom query job, does the change will be picking up to pending-task ? 

 

Can you elaborate?


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.