SQL to HQL Conversion for Report

IdentityIQ (IIQ) IIQ Discussion and Questions
,
1

Hello all,

I want create a custom report with HQL but I couldn’t handle it. Could you please help me to convert below SQL query to HQL?

select i.name,i.status, i.display_name,p.name policy_name,b1.name as leftRole,(TO_DATE(‘1970-01-01’,‘YYYY-MM-DD’) + ie.created/86400000)
as LEFTROLE_CREATED,b2.name as rightRole,(TO_DATE(‘1970-01-01’,‘YYYY-MM-DD’) + ie2.created/86400000) as RIGHTROLE_CREATED from spt_policy p inner join SPT_SODCONSTRAINT cons on cons.POLICY = p.id
inner join SPT_SODCONSTRAINT_LEFT left on left.SODCONSTRAINT = cons.id
inner join SPT_SODCONSTRAINT_RIGHT right on right.SODCONSTRAINT = cons.id
inner join spt_bundle b1 on b1.id = left.BUSINESSROLE
inner join spt_bundle b2 on b2.id = right.BUSINESSROLE
inner join spt_identity_entitlement ie on ie.value = b1.name
inner join spt_identity_entitlement ie2 on ie2.value = b2.name
inner join spt_identity i on ie.identity_id = i.id
where ie2.identity_id = ie.identity_id
order by i.name desc

2

3_LiveReports_FINAL.pptx (7.4 MB)

3

@burakhalitoglu - Please see this document might help you.

4

Hey @burakhalitoglu please take a look at this plugin; it helps convert SQL queries to HQL for custom reports. SQL-conversion-to-HQL-for-Custom-Report

5

Use this Query plugin that I have used before.

pub / sailpoint-plugins / Query Plugin · GitLab

6

I figured out! Thanks guys!

        from Policy p
        join p.SODConstraints cons
        join cons.leftBundles b1
        join cons.rightBundles b2
        join IdentityEntitlement ie on ie.value = b1.name
        join IdentityEntitlement ie2 on ie2.value = b2.name
        join ie.identity i
        where ie2.identity = ie.identity