Modules

Browse queries by module.

All Modules

ARS Requests ARS Tasks Accounts Accounts, Endpoint Accounts, Endpoints Analytics Audit Logs Audit Trail Campaign Certification Connections Connections, Job Control Panel Customers Email History Endpoints Entitlement Values External Connections Job Control Panel Request Access Role Assignments Roles Rules Rulesets Security Systems User Accounts User Group User Groups Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLES

Rules by Event Source

Module: Rules
Description

Displays rules grouped by event source.

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT eventsource, COUNT(*) AS Total_Rules FROM hanarule GROUP BY eventsource ORDER BY Total_Rules DESC;
Comments

nan

Rules by Type

Module: Rules
Description

Displays rules grouped by rule type.

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT CASE type WHEN 0 THEN 'Business' WHEN 1 THEN 'Technical' WHEN 2 THEN 'User Update' WHEN 3 THEN 'New User' WHEN 4 THEN 'Remove User' WHEN 5 THEN 'Update Entitlement' WHEN 6 THEN 'New Entitlement' WHEN 7 THEN 'Remove Entitlement' WHEN 8 THEN 'Request' ELSE CONCAT('Unknown (',type,')') END AS Rule_Type, COUNT(*) AS Total_Rules FROM hanarule GROUP BY type ORDER BY Total_Rules DESC;
Comments

nan

Rules by Status

Module: Rules
Description

Displays rules grouped by status.

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT CASE status WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' ELSE CONCAT('Unknown (',status,')') END AS Rule_Status, COUNT(*) AS Total_Rules FROM hanarule GROUP BY status ORDER BY Total_Rules DESC;
Comments

nan

User Update Rule- ISUPDATED keyword check (Basic and Advanced)

Module: Rules
Description

Get all User Update rules with its basic or advanced query having “ISUPDATED” check in it

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT hr.hanarulekey,
hr.name,
hr.advancedconfig,
hr.advancedquery,
hr.birthright,
hr.detective,
hr.evaluatehierarchy,
hr.ruledescription,
ha.conditionopertor,
ha.conditionoraction,
ha.nextrulecondition,
ha.objectattribute,
ha.objectname,
CASE
WHEN ha.objectname=1 THEN 'Launch Certification'
WHEN ha.objectname=3 THEN 'Re-Run Provisioning Rules'
WHEN ha.objectname=7 THEN 'Enable Account'
WHEN ha.objectname=-999 THEN 'Create Account'
WHEN ha.objectname=19 THEN 'Update Account'
WHEN ha.objectname=12 THEN 'Disable Account'
WHEN ha.objectname=9 THEN 'Update User'
WHEN ha.objectname = '10' THEN 'Deprovision Endpoint(s)'
WHEN ha.objectname = '11' THEN 'Trasnsfer Ownership'
WHEN ha.objectname=21 THEN 'Re-Run Selected Technical Rule'
WHEN ha.objectname=20 THEN 'Generate User Email'
ELSE 'Unknown'
END AS Rule_Action_Type,
ha.objectvalue,
ha.objectvaluetype,
ha.position,
ha.openparanthesis,
ha.closeparanthesis
FROM hanarule hr
JOIN hanaruleattribute ha
ON hr.hanarulekey = ha.hanarule
WHERE hr.type=2 AND (hr.advancedquery LIKE '%isupdated%'
OR ha.conditionopertor = 'is updated');
Comments

nan

Technical Rule- Status keyword check (Advanced)

Module: Rules
Description

Get all Technical rules with its advanced query, and check whether advanced query has ‘statuskey’ keyword

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT hanarulekey,
name,
advancedquery,
CASE
WHEN advancedquery LIKE '%statuskey%' THEN 'TRUE'
ELSE 'FALSE'
END AS contains_statuskey
FROM hanarule
WHERE type = 1
AND advancedquery IS NOT NULL;
Comments

nan

Technical Rule- Status keyword check (Basic)

Module: Rules
Description

Get all Technical rules with its basic query, and check whether basic query has ‘statuskey’ keyword

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT
hr.hanarulekey,
hr.name,
hr.birthright,
hr.detective,
hr.evaluatehierarchy,
hr.ruledescription,
ha.conditionopertor,
ha.conditionoraction,
ha.nextrulecondition,
ha.objectattribute,
ha.objectname,
CASE
WHEN ha.objectname=1 THEN 'Launch Certification'
WHEN ha.objectname=3 THEN 'Re-Run Provisioning Rules'
WHEN ha.objectname=7 THEN 'Enable Account'
WHEN ha.objectname=-999 THEN 'Create Account'
WHEN ha.objectname=19 THEN 'Update Account'
WHEN ha.objectname=12 THEN 'Disable Account'
WHEN ha.objectname=9 THEN 'Update User'
WHEN ha.objectname = '10' THEN 'Deprovision Endpoint(s)'
WHEN ha.objectname = '11' THEN 'Trasnsfer Ownership'
WHEN ha.objectname=21 THEN 'Re-Run Selected Technical Rule'
WHEN ha.objectname=20 THEN 'Generate User Email'
ELSE 'Unknown'
END AS Rule_Action_Type,
ha.objectvalue,
ha.objectvaluetype,
ha.position,
ha.openparanthesis,
ha.closeparanthesis,
CASE
WHEN ha.objectattribute = 'statuskey' THEN 'TRUE'
ELSE 'FALSE'
END AS contains_statuskey
FROM hanarule hr
JOIN hanaruleattribute ha ON hr.hanarulekey = ha.hanarule
WHERE hr.type = '1';
Comments

nan

User Update Rule- Status keyword check (Advanced)

Module: Rules
Description

Get all User Update rules with its advanced query, and check whether advanced query has ‘statuskey’ keyword

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT hanarulekey,
name,
advancedquery,
CASE
WHEN advancedquery LIKE '%statuskey%' THEN 'TRUE'
ELSE 'FALSE'
END AS contains_statuskey
FROM hanarule
WHERE type = 2
AND advancedquery IS NOT NULL;
Comments

nan

User Update Rule- Status keyword check (Basic)

Module: Rules
Description

Get all User Update rules with its basic query, and checks whether basic query has ‘statuskey’ keyword

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query 
SELECT
hr.hanarulekey,
hr.name,
hr.birthright,
hr.detective,
hr.evaluatehierarchy,
hr.ruledescription,
ha.attributeconfig,
ha.conditionopertor,
ha.conditionoraction,
ha.nextrulecondition,
ha.objectattribute,
ha.objectname,
CASE
WHEN ha.objectname=1 THEN 'Launch Certification'
WHEN ha.objectname=3 THEN 'Re-Run Provisioning Rules'
WHEN ha.objectname=7 THEN 'Enable Account'
WHEN ha.objectname=-999 THEN 'Create Account'
WHEN ha.objectname=19 THEN 'Update Account'
WHEN ha.objectname=12 THEN 'Disable Account'
WHEN ha.objectname=9 THEN 'Update User'
WHEN ha.objectname = '10' THEN 'Deprovision Endpoint(s)'
WHEN ha.objectname = '11' THEN 'Trasnsfer Ownership'
WHEN ha.objectname=21 THEN 'Re-Run Selected Technical Rule'
WHEN ha.objectname=20 THEN 'Generate User Email'
ELSE 'Unknown'
END AS Rule_Action_Type,
ha.objectvalue,
ha.objectvaluetype,
ha.position,
ha.openparanthesis,
ha.closeparanthesis,
CASE
WHEN ha.objectattribute = 'statuskey' THEN 'TRUE'
ELSE 'FALSE'
END AS contains_statuskey
FROM hanarule hr
JOIN hanaruleattribute ha ON hr.hanarulekey = ha.hanarule
WHERE hr.type = '2';
Comments

nan

Query copied!