Sample Reports for data verification in EIC

[nimitdave]

Use Case

This document contains some useful report that can be used to verify data ,job status, task provisioning status etc in EIC. These can be leveraged to be used for monitoring.

Pre-requisites

Create Analytics Role Access

Data Analyzer Role Access

Applicable Version(s)

ALL

 

Solution

Report to give details of the  User Manager campaigns data at the time of campaign creation.

SELECT DISTINCT certification_user.USERNAME, certification_user.FIRSTNAME, certification_user.LASTNAME, certification_user.EMAIL, certification_user.STATUSKEY, certification_account.NAME, certification_account.STATUS, certification_entitlement_value.ENTITLEMENT_VALUE, ENTITLEMENTNAME, endpoints.ENDPOINTNAME FROM campaign INNER JOIN certification ON campaign.id = certification.CAMPAIGNKEY INNER JOIN certification_account_entitlement1_status ON certification.CERTKEY = certification_account_entitlement1_status.CERTKEY INNER JOIN certification_account ON certification_account_entitlement1_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY INNER JOIN certification_entitlement_value ON certification_account_entitlement1_status.CERT_ENTITLEMENT_VALUEKEY = certification_entitlement_value.CERT_ENTITLEMENT_VALUEKEY INNER JOIN entitlement_types ON certification_entitlement_value.ENTITLEMENTTYPEKEY = entitlement_types.ENTITLEMENTTYPEKEY INNER JOIN endpoints ON entitlement_types.ENDPOINTKEY = endpoints.ENDPOINTKEY LEFT JOIN certification_user_account_status ON certification_user_account_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY LEFT JOIN certification_user ON certification_user.CERT_USERKEY = certification_user_account_status.CERT_USERKEY WHERE campaign.campaign_name = 'INSERT CAMPAIGN NAME HERE' -- Modifiable Parameter ORDER BY certification_user.CREATEDBY DESC;

Query to get the campaign base data at any instance , this is the real time data for accounts and associated access:

SELECT DISTINCT users.USERNAME, users.FIRSTNAME, users.LASTNAME, users.EMAIL, users.CREATEDATE, users.STATUSKEY, accounts.NAME, accounts.STATUS, ENTITLEMENT_VALUE, ENTITLEMENTNAME, endpoints.ENDPOINTNAME FROM users, user_accounts, accounts, account_entitlements1, endpoints, entitlement_types, entitlement_values WHERE users.USERKEY = user_accounts.userkey AND user_accounts.ACCOUNTKEY = accounts.ACCOUNTKEY AND accounts.ACCOUNTKEY = account_entitlements1.ACCOUNTKEY AND entitlement_values.ENTITLEMENT_VALUEKEY = account_entitlements1.ENTITLEMENT_VALUEKEY AND entitlement_values.ENTITLEMENTTYPEKEY = entitlement_types.ENTITLEMENTTYPEKEY AND entitlement_types.ENDPOINTKEY = endpoints.ENDPOINTKEY AND endpoints.endpointkey IN (25,10,15) -- Replace comma-seperated keys as per requirement AND accounts.STATUS != 'SUSPENDED FROM IMPORT SERVICE' ORDER BY users.CREATEDATE DESC;

This report provides a snapshot of the Running Scheduled Jobs Performance in Saviynt. This will pick all the jobs which ran in the past 7 days into scope.

select EC2.Type,SY.Systemname,EC1.ConnectionStatus,EC2.JOBSTARTDATE,EC2.JOBENDDATE,EC2.JOBNAME,EC2.SAVRESPONSE
from
(select Systemname,systemkey from securitysystems ss where systemname not like 'AWS%' ) as SY
left join
(select ss.systemname,ss.systemkey,ec.connectionname,case when ec.STATUS = 1 then 'ConnectionSuccess' else 'ConnectionFailure' end as ConnectionStatus
from EXTERNALCONNECTION ec, securitysystems ss
where ss.EXTERNALCONNECTION = ec.EXTERNALCONNECTIONkey and systemname not like 'AWS%') as EC1 on SY.systemkey = EC1.systemkey
(select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname, SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'SOD Evaluation Jobs' as Type
from ecmimportjob
where jobname = 'RiskSODEvaluationJob'
and date(jobstartdate) > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
UNION
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname, SAVRESPONSE, JOBSTARTDATE,JOBENDDATE, 'SAP TWO Table Jobs' as Type
where jobname = 'SAPImportJob'
and externalconnection like '%CLNT%'
and TRIGGERNAME like '%TWO_1%'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname, savresponse, JOBSTARTDATE,JOBENDDATE, 'SAP Full Import Jobs' as Type
and TRIGGERNAME like '%FULL_1%'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname, SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'AD Incremental Jobs' as Type
and externalconnection like 'BP1%'
and coments like '%fullorincremental:incremental%'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname,SAVRESPONSE, JOBSTARTDATE,JOBENDDATE, 'AD Full Import Jobs' as Type
and coments like '%fullorincremental:full%'
select systemname,replace(JOBNAME,"Sap","System") as Jobname,SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'Saviynt for Saviynt Jobs' as Type
where externalconnection = 'Saviynt'
and jobname in ('AccountsImportFullJob','EntitlementValueImportJob')
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname,SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'Workday Accounts' as Type
where externalconnection in ('Workday')
and jobname in ('SapImportJob')
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname,SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'EC Registers Job' as Type
where externalconnection = 'EC Registers'
and jobname = 'AccountsImportFullJob'
select case when coments like '%connectionname:SALESFORCE_COMPANY_ORG%' then 'SALESFORCE_COMPANY_ORG'
when coments like '%connectionname:SALESFORCE_COMMUNITY_ORG%' then 'SALESFORCE_COMMUNITY_ORG'
when coments like '%connectionname:SALESFORCE_CUSTOMER_ORG%' then 'SALESFORCE_CUSTOMER_ORG'
when coments like '%connectionname:SALESFORCE_CONTACTS_COMMUNITY_ORG%' then 'SALESFORCE_CONTACTS_COMMUNITY_ORG'
when coments like '%connectionname:SALESFORCE_COLLEAGUES_ORG%' then 'SALESFORCE_COLLEAGUES_ORG'
when coments like '%connectionname:SALESFORCE_CONSUMER_ORG_PREPROD%' then 'SALESFORCE_CONSUMER_ORG_PREPROD'
when coments like '%connectionname:SALESFORCE_COLLEAGUES_ORG_PREPROD%' then 'SALESFORCE_COLLEAGUES_ORG_PREPROD'
when coments like '%connectionname:SALESFORCE_COMMUNITY_ORG_PREPROD%' then 'SALESFORCE_COMMUNITY_ORG_PREPROD'
when coments like '%connectionname:SALESFORCE_COMPANY_ORG_PREPROD%' then 'SALESFORCE_COMPANY_ORG_PREPROD'
when coments like '%connectionname:SALESFORCE_CONTACTS_COMPANY_ORG%' then 'SALESFORCE_CONTACTS_COMPANY_ORG'
when coments like '%connectionname:SALESFORCE_CONTACTS_COLLEAGUES_ORG%' then 'SALESFORCE_CONTACTS_COLLEAGUES_ORG'
else COMENTS end as 'System',replace(JOBNAME,"Sap","System") as Jobname,savresponse, JOBSTARTDATE, jobenddate, 'Salesforce Jobs' as Type
where (coments like '%connectionname:SALESFORCE_COMPANY_ORG%'
or coments like '%connectionname:SALESFORCE_COMMUNITY_ORG%'
or coments like '%connectionname:SALESFORCE_CUSTOMER_ORG%'
or coments like '%connectionname:SALESFORCE_CONTACTS_COMMUNITY_ORG%'
or coments like '%connectionname:SALESFORCE_COLLEAGUES_ORG%'
or coments like '%connectionname:SALESFORCE_CONSUMER_ORG_PREPROD%'
or coments like '%connectionname:SALESFORCE_COLLEAGUES_ORG_PREPROD%'
or coments like '%connectionname:SALESFORCE_COMMUNITY_ORG_PREPROD%'
or coments like '%connectionname:SALESFORCE_COMPANY_ORG_PREPROD%'
or coments like '%connectionname:SALESFORCE_CONTACTS_COMPANY_ORG%'
or coments like '%connectionname:SALESFORCE_CONTACTS_COLLEAGUES_ORG%')
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname,SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'ISIM Incremental Jobs' as Type
where jobname in ('UserImportJob')
and externalconnection like 'ISIM%Incremental%'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname, SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'ISIM Full Jobs' as Type
and externalconnection like 'ISIM%Rules%'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname,savresponse,jobstartdate, jobenddate, 'ExternalJar Jobs' as Type
where jobname = 'ExternalJarJob'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname, SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'WSRetry Jobs' as Type
where jobname = 'WSRetryJob'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname, SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'Email Jobs' as Type
where jobname = 'EmailHistoryJob'
select externalconnection as System,replace(JOBNAME,"Sap","System") as Jobname,SAVRESPONSE, JOBSTARTDATE, JOBENDDATE, 'Azure AD Accounts' as Type
where systemname = 'Azure Active Directory'
select eij.jobname as 'System Name','Null' as ConnectionStatus,eij.JOBSTARTDATE,eij.JOBENDDATE,eij.JOBNAME,eij.SAVRESPONSE
from ecmimportjob eij where jobname in ('WSRetryJob','EnterpriseRoleManagementJob')
) as EC2 on EC2.system = EC1.connectionname
order by EC2.TYPE desc

This report provides a snapshot of the Provisioning Performance in Saviynt. This will pick all the tasks which is created on the current date into the scope.

select e.endpointname as Endpoint, count(a.taskkey) as Total_Tasks,
sum(case when a.status = 1 then 1 else 0 end) as Pending,
sum(case when a.status = 4 then 1 else 0 end) as Discontinued,
sum(case when a.status in (3,9) then 1 else 0 end) as Success,
sum(case when a.status in (7,8) then 1 else 0 end) as Error,
sum(case when a.status in (3,9) then 1 else 0 end)*100/count(taskkey) as Success_Percentage,
sum(case when a.status in (7,8) then 1 else 0 end)*100/count(taskkey) as Failure_Percentage,
case when ec.STATUS = 1 then 'Connection Successful' else 'Connection Failure' end as 'Connection_Checks'
from arstasks a, endpoints e, securitysystems ss, externalconnection ec
where date(a.taskdate) = curdate()
and a.endpoint = e.endpointkey
and e.SECURITYSYSTEMKEY = ss.SYSTEMKEY
and ss.EXTERNALCONNECTION = ec.EXTERNALCONNECTIONKEY
group by a.endpoint
order by Failure_Percentage desc

Note - Please run these reports when the system is not in heavy use so that it does not impact ongoing processes.

[PragyaPurwar]

Can we have more rows in data analyzer , it is showing only top 100 .