@Sankhadeep as discussed in the SME call,
1. There are two ways to do this:
- You can run the same runtime analytic control some time after the job execution and filter the list by Total_No_DaysLastRotation. For the accounts that just got rotatated the value should be 0
- You can navigate to completed tasks page and check for the change password tasks
2. You can create a new runtime analytic control and customize it by adding additional conditions in the out of the box query. Here is a query which has additional conditions associated with the platform_type column in endpoints_properties table. I have split the query at joins and unions for easy understanding. But there are 3 additions right after the word rotateKey"%')) in the query (I have highlighted them). You can remove the platforms you dont need and keep only the required ones. So, this segregation is at platform level (ex: all windows servers or all unix or all db, or a combination of them, etc.)
Modified Query:
SELECT ac.ACCOUNTKEY AS ID, ac.accountid AS 'accountid', ac.NAME AS NAME,ac.accounttype AS accounttype,ep.ENDPOINTNAME AS 'EndpointName', ss.SYSTEMNAME AS 'SystemName', IF(ac.LASTPASSWORDCHANGE IS NULL , DATEDIFF( CURDATE(), DATE(ac.CREATED_ON)),DATEDIFF( CURDATE(), DATE(ac.LASTPASSWORDCHANGE))) AS 'Total_No_DaysLastRotation', IF(INSTR(ec.credentialchangeconfig,ac.ACCOUNTKEY) > 0 ,'Master','Shareable') AS 'CredentialType', pr.EXPIREAFTER, epp.PLATFORM_TYPE AS 'PLATFORM'
FROM accounts ac INNER JOIN
endpoints ep ON ac.ENDPOINTKEY=ep.ENDPOINTKEY AND ac.status IN (1,'Manually Provisioned') AND (ac.accountconfig NOT LIKE '%"justInTime":"true"%' OR ac.ACCOUNTCONFIG IS NULL) AND ((ac.accounttype IS NOT NULL AND ac.accounttype != '' AND ac.accounttype != 'Platform Service Account') AND ac.ACCOUNTCONFIG LIKE '%ENABLED%') INNER JOIN
endpoints_properties AS epp ON epp.ENDPOINTKEY=ep.ENDPOINTKEY AND epp.PAM_STATE = 'ENABLED' AND (epp.PAMCONFIG IS NOT NULL AND (epp.PAMCONFIG ->> '\$.rotateKey' != 'false' OR epp.PAMCONFIG NOT LIKE '%"rotateKey"%')) AND epp.PLATFORM_TYPE in('WINDOWS','UNIX','CONSOLE','AWSConsole','GCPConsole','DB','ADConsole','SAPConsole') INNER JOIN
securitysystems AS ss ON ss.SYSTEMKEY = ep.SECURITYSYSTEMKEY INNER JOIN
policyrule AS pr ON pr.POLICYRULEKEY=ss.POLICYRULESERVICEACCOUNT INNER JOIN
externalconnection AS ec ON ss.provisioningconnection = ec.externalconnectionkey
UNION
SELECT ac.ACCOUNTKEY AS ID, ac.accountid AS 'accountid',ac.NAME AS NAME,ac.accounttype AS accounttype, ep.ENDPOINTNAME AS 'EndpointName',ss.SYSTEMNAME AS 'SystemName',IF(ac.LASTPASSWORDCHANGE IS NULL, DATEDIFF( CURDATE(), DATE(ac.CREATED_ON)),DATEDIFF( CURDATE(), DATE(ac.LASTPASSWORDCHANGE))) AS 'Total_No_DaysLastRotation', IF(INSTR(ec.credentialchangeconfig,ac.ACCOUNTKEY) > 0 ,'Master','Shareable') AS 'CredentialType', pr.EXPIREAFTER, epp.PLATFORM_TYPE AS 'PLATFORM'
FROM accounts ac INNER JOIN
endpoints ep ON ac.ENDPOINTKEY=ep.ENDPOINTKEY inner join
account_attributes accatt on accatt.ACCOUNTKEY = ac.ACCOUNTKEY and accatt.ATTRIBUTE_NAME = 'PRINCIPALSOURCE' AND ac.status IN (1,2,'Manually Provisioned') AND ( ac.accountconfig NOT LIKE '%"justInTime":"true"%' OR ac.ACCOUNTCONFIG IS NULL ) AND ac.accounttype = 'Platform Service Account' AND (((ac.ACCOUNTCONFIG NOT LIKE '%ENABLED%' OR ac.ACCOUNTCONFIG IS NULL) AND accatt.ATTRIBUTE_VALUE = 'ActiveDirectory') OR (accatt.ATTRIBUTE_VALUE = 'Local')) INNER JOIN
endpoints_properties AS epp ON epp.ENDPOINTKEY=ep.ENDPOINTKEY AND epp.PAM_STATE = 'ENABLED' AND (epp.PAMCONFIG IS NOT NULL AND (epp.PAMCONFIG ->> '\$.rotateKey' != 'false' OR epp.PAMCONFIG NOT LIKE '%"rotateKey"%')) AND epp.PLATFORM_TYPE in('WINDOWS','UNIX','CONSOLE','AWSConsole','GCPConsole','DB','ADConsole','SAPConsole') INNER JOIN
securitysystems AS ss ON ss.SYSTEMKEY = ep.SECURITYSYSTEMKEY INNER JOIN
policyrule AS pr ON pr.POLICYRULEKEY=ss.POLICYRULESERVICEACCOUNT INNER JOIN
externalconnection AS ec ON ss.provisioningconnection = ec.externalconnectionkey
UNION
SELECT distinct ac.ACCOUNTKEY AS ID, ac.accountid AS 'accountid',ac.NAME AS NAME,ac.accounttype AS accounttype, ep.ENDPOINTNAME AS 'EndpointName',ss.SYSTEMNAME AS 'SystemName', IF(ac.LASTPASSWORDCHANGE IS NULL , DATEDIFF( CURDATE(), DATE(ac.CREATED_ON)),DATEDIFF( CURDATE(), DATE(ac.LASTPASSWORDCHANGE))) AS 'Total_No_DaysLastRotation', IF(INSTR(ec.credentialchangeconfig,ac.ACCOUNTKEY) > 0 ,'Master','Shareable') AS 'CredentialType', pr.EXPIREAFTER,epp.PLATFORM_TYPE AS 'PLATFORM'
FROM accounts ac INNER JOIN
account_attributes acc_attr ON ac.accountkey = acc_attr.accountkey AND acc_attr.attribute_name = 'MEMBER_ENDPOINTKEY' INNER JOIN
endpoints ep ON ac.ENDPOINTKEY = ep.endpointkey INNER JOIN
endpoints_properties AS epp ON epp.ENDPOINTKEY=ep.ENDPOINTKEY AND (epp.PAMCONFIG IS NOT NULL AND (epp.PAMCONFIG ->> '\$.rotateKey' != 'false' OR epp.PAMCONFIG NOT LIKE '%"rotateKey"%')) AND epp.PLATFORM_TYPE in('WINDOWS','UNIX','CONSOLE','AWSConsole','GCPConsole','DB','ADConsole','SAPConsole') INNER JOIN
securitysystems AS ss ON ss.SYSTEMKEY = ep.SECURITYSYSTEMKEY INNER JOIN
policyrule AS pr ON pr.POLICYRULEKEY=ss.POLICYRULESERVICEACCOUNT INNER JOIN
externalconnection AS ec ON ss.provisioningconnection = ec.externalconnectionkey WHERE ac.status IN (1,'Manually Provisioned') AND ( ac.accountconfig NOT LIKE '%"justInTime":"true"%' OR ac.ACCOUNTCONFIG is NULL ) AND ( ac.accounttype = 'Platform Service Account' OR ac.ACCOUNTCONFIG LIKE '%ENABLED%' );
Thanks,
Nagesh K
