Update account status through enhance query for a specific endpoint

[biswajitoram]

Hi Team,

I need help in developing an enhanced query for the below scenario,

I want to update all the accounts' (having Status = Manually Suspended) present for an endpoint XYZ (ENDPOINTKEY = 100) to update the status to Suspended From Import Service. I can update the status for a specific account with the below query: 

SELECT
"XXXXXXX-Delted on-08-01-2023 15:42" AS accounts__name,
"SUSPENDED FROM IMPORT SERVICE" AS accounts__status,
accountkey AS accounts__PRIMARYKEY
FROM
accounts
WHERE
accountkey = '111111';

But how can I update the status of all the accounts?

[NM]

Hi @biswajitoram 

Try this 

SELECT

"XXXXXXX-Delted on-08-01-2023 15:42" AS accounts__name,

"SUSPENDED FROM IMPORT SERVICE" AS accounts__status,

accountkey AS accounts__PRIMARYKEY

FROM

accounts

WHERE

endpointkey=100 and status in ('Manually Suspended')

[biswajitoram]

Hi @NM 

Thanks for your quick support.

I need some more help on this. I want to pass the AccountName instead of the Dummy value.

I tried this query but no help.

SELECT

"(select name from accounts) -Delted on-08-01-2023 15:42" AS accounts__name,
"SUSPENDED FROM IMPORT SERVICE" AS accounts__status,
accountkey AS accounts__PRIMARYKEY

FROM accounts

WHERE
endpointkey=100 and status in ('Manually Suspended')

[NM · Accepted solution]

@biswajitoram try this 

SELECT

Concat(name,'-Delted on-08-01-2023 15:42') AS accounts__name,

"SUSPENDED FROM IMPORT SERVICE" AS accounts__status,

accountkey AS accounts__PRIMARYKEY

FROM

accounts

WHERE

endpointkey=100 and status in ('Manually Suspended')

If you find the solution helpful please accept the solution and hit kudos button.

[biswajitoram]

@NM Thanks for your quick support. It worked.

[biswajitoram]

Hi @NM 

I have one more requirement, can we add the current date and timestamp instead of static value?

Concat(name,'-Delted on-08-01-2023 15:42') AS accounts__name,

[rushikeshvartak]

• SELECT
  CONCAT(ac.name, '-Deleted on-', SYSDATE()) AS accounts__name,
  'SUSPENDED FROM IMPORT SERVICE' AS accounts__status,
  ac.accountkey AS accounts__primarykey FROM
  accounts ac
  WHERE
  accountkey=100 and endpointkey=1

Refer https://forums.saviynt.com/t5/identity-governance/change-the-account-name-and-status-using-analytics... 

[NM]

Hi @biswajitoram just update concat part

SELECT

CONCAT(name, '-Deleted on-', SYSDATE())

[biswajitoram]

Thanks @NM & @rushikeshvartak  for your support