General MySQL useful Queries

 1. Delete duplicate rows in the table

a. Delete all the duplicate rows

DELETE t1 FROM newuserdata t1 INNER JOIN newuserdata t2 WHERE t1.customproperty1 != t2.customproperty1 AND t1.customproperty2 = t2.customproperty2 AND t1.customproperty3 = t2.customproperty3;

b. Delete duplicate row and keeps the highest customproperty1

DELETE t1 FROM newuserdata t1 INNER JOIN newuserdata t2 WHERE t1.customproperty1 < t2.customproperty1 AND t1.customproperty2 = t2.customproperty2 AND t1.customproperty3 = t2.customproperty3;


The above query also can be used in MODIFYUSERDATAJSON of the HR Connection to delete duplicate user records from the HR import

Comments

Post a Comment

Popular posts from this blog

How to update Endpoint(Account) Custom property labels in Saviynt

Image
Saviynt supports 45 Custom properties to use to store account information and comes with default labels. To make it user-friendly and readable format we are required to update the labels. This can be achieved in Saviynt by following the below steps. Step 1: Log in to Saviynt Instance And Navigate to Endpoint Navigate to Admin => Identity Repository => Security Systems => Select the Security System and Endpoint and then navigate to the Other Attributes page of Endpoint Scroll down until you see the option to update the labels for the attributes, and update the editable text boxes for all the required custom properties Note: This label change is applicable only for the selected endpoint, you need to update the same for all endpoints separately. I personally Suggest adding the cp# at the end of the label so that you can easily identify the backend name for Example for Custom Property 1 if the label is department then use department cp1 Note: this change takes time to reflect ...
Read more

Updating User custom property labels in Saviynt

Image
Saviynt supports 65 Custom properties to use to store user information and comes with default labels. To make it user-friendly and readable format we are required to update the labels. This can be achieved in Saviynt by following the below steps. Step 1: Assign the below SAV Roles to yourself Search for your user under Admin => Identity => Repository => enter your username and search Navigate to SAV Roles Tab in your user profile and add the below roles using the Action button  ROLE_UIADMIN  ROLE_ADMIN  The above roles are needed to update the labels in the Saviynt Application. Sometimes in Prod, you may not be supposed to do this directly as an Audit compliance issue, so you can use approval workflows if configured or seek approvals from IGA Administrators. Step 2: Logout and Login again Note: Sometimes you may also need to clear the browsing history or cache Step 3: Update the Custom Property Label Navigate to Admin => Identity Repository => Users => p...
Read more

Exchange Integration using REST Connector in Saviynt

Use-case requirement: Use Saviynt REST Connector to invoke/Execute PowerShell script which is placed in Windows Server machine. PowerShell Scripts connects to Exchange and enables or disables users Exchange account. REST Connection Details: ConnectionJSON: Replace the HOSTNAME and SaviyntWebApp name(based on the Saviynt App name deployed in IIS Server, I have deployed Saviynt app as SaviyntWebApp in the IIS server, you can find the details on how to deploy Saiviynt App in IIS server in the Saviynt Freshdesk documentation)  If you don't have a script for testConnection.ps1(Place your testConnection.ps1 script in C:\Saviynt\Scripts\ directory) then you can simply replace it with  "httpParams": "{\"Script\":\"Test-NetConnection HOSTNAME -Port 443\"}" If you are using hostname instead of IP then make sure the IP to Hostname(DNS) config is in Place on the Saviynt Infra side. {   "authentications": {     "auth": {       "au...
Read more