Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Backforward slash is not working in SQL Query

Ashir
New Contributor
New Contributor

Hi Team,

I need one help from you.

Am working on Actionable Analytic report for adding the access for active account.

This below SQL query is working in Data Analyzer and we are getting the result but if am trying to create the actionable report am getting the error.

To add the single \ i have added 2 \\. But it is not wokring in Analytics. Any idea how we use \ inside (datacenter.xxxgroupinc.net\tholt-s)

select
ev.entitlement_valuekey as entvaluekey,
ev.entitlement_value,
a.accountkey as acctkey,
a.name as accname,
u.username,
a.endpointkey,
u.userkey as userkey,
'provision access' as default_action_for_analytics
from
entitlement_values ev,
entitlement_types et,
accounts a,
user_accounts ua,
users u
where ev.entitlementtypekey=et.entitlementtypekey
and a.accountkey in (select accountkey from accounts where endpointkey=(select endpointkey from endpoints where endpointname='Secret Server')) and ua.accountkey=a.accountkey
and u.username in ( select u.username from users u, user_accounts ua, accounts a, account_entitlements1 ae1, entitlement_values ev, endpoints e
where a.status = 1
and a.accountkey = ua.accountkey
and u.userkey = ua.userkey
and e.endpointname='Secret Server'
and a.endpointkey = e.endpointkey
and u.statuskey = 1
and u.username='411043'
and ae1.accountkey = a.accountkey
and ae1.entitlement_valuekey = ev.entitlement_valuekey
and ev.entitlement_value not in ('AG-Super',concat("datacenter.xxxgroupinc.net\\",systemUserName,"-s")))
and u.userkey =ua.userkey
and ev.entitlement_value in ('AG-Super',concat("datacenter.xxxgroupinc.net\\",systemUserName,"-s"))
and a.status in ('1','Manually Provisioned')

Result from Data Analyzer

Ashir_0-1721988857806.png

 

If am trying to create the Actionable analytic report using the same query in Analytics am getting this below error.

Ashir_1-1721988973966.png

 

I need to add 2 entitlements 

1.AG-Super 

2.datacenter.xxxgroupinc.net\tholt-s
The syntax has to be same as above.

 

9 REPLIES 9

NM
Honored Contributor II
Honored Contributor II

Hi @Ashir try below

select
ev.entitlement_valuekey as entvaluekey,
ev.entitlement_value,
a.accountkey as acctkey,
a.name as accname,
u.username,
a.endpointkey,
u.userkey as userkey,
'provision access' as default_action_for_analytics
from
entitlement_values ev,
entitlement_types et,
accounts a,
user_accounts ua,
users u
where ev.entitlementtypekey=et.entitlementtypekey
and a.accountkey in (select accountkey from accounts where endpointkey=(select endpointkey from endpoints where endpointname='Secret Server')) and ua.accountkey=a.accountkey
and u.username in ( select u.username from users u, user_accounts ua, accounts a, account_entitlements1 ae1, entitlement_values ev, endpoints e
where a.status = 1
and a.accountkey = ua.accountkey
and u.userkey = ua.userkey
and e.endpointname='Secret Server'
and a.endpointkey = e.endpointkey
and u.statuskey = 1
and u.username='411043'
and ae1.accountkey = a.accountkey
and ae1.entitlement_valuekey = ev.entitlement_valuekey
and ev.entitlement_value not in ('AG-Super',concat("datacenter.xxxgroupinc.net\\\",systemUserName,"-s")))
and u.userkey =ua.userkey
and ev.entitlement_value in ('AG-Super',concat("datacenter.xxxgroupinc.net\\\",systemUserName,"-s"))
and a.status in ('1','Manually Provisioned')

Ashir
New Contributor
New Contributor

Hi @NM 

I have tried the query which you provided in analytic am getting no result.

Ashir_0-1721992925014.png

 

NM
Honored Contributor II
Honored Contributor II

Hi @Ashir , do you see records in data anaylzer?

NM
Honored Contributor II
Honored Contributor II

@Ashir , in concat use single quotes.

rushikeshvartak
All-Star
All-Star
SELECT
    ev.entitlement_valuekey AS entvaluekey,
    ev.entitlement_value,
    a.accountkey AS acctkey,
    a.name AS accname,
    u.username,
    a.endpointkey,
    u.userkey AS userkey,
    'provision access' AS default_action_for_analytics
FROM
    entitlement_values ev,
    entitlement_types et,
    accounts a,
    user_accounts ua,
    users u
WHERE
    ev.entitlementtypekey = et.entitlementtypekey
    AND a.accountkey IN (
        SELECT accountkey
        FROM accounts
        WHERE endpointkey = (
            SELECT endpointkey
            FROM endpoints
            WHERE endpointname = 'Secret Server'
        )
    )
    AND ua.accountkey = a.accountkey
    AND u.username IN (
        SELECT u.username
        FROM users u
        JOIN user_accounts ua ON u.userkey = ua.userkey
        JOIN accounts a ON a.accountkey = ua.accountkey
        JOIN account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
        JOIN entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
        JOIN endpoints e ON e.endpointkey = a.endpointkey
        WHERE a.status = 1
        AND e.endpointname = 'Secret Server'
        AND u.statuskey = 1
        AND u.username = '411043'
        AND ev.entitlement_value NOT IN (
            'AG-Super',
            CONCAT('datacenter.xxxgroupinc.net\\', systemUserName, '-s')
        )
        AND ev.entitlement_value IN (
            'AG-Super',
            CONCAT('datacenter.xxxgroupinc.net\\', systemUserName, '-s')
        )
    )
    AND a.status IN ('1', 'Manually Provisioned');

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Thanks for your reply

@NM  I have tested with single quotes also it is not working.('AG-Super',CONCAT('datacenter.radiangroupinc.net\\',systemUserName,'-s'))



@rushikeshvartak  Query is not working to me. Am creating this query for add access using Actionable report.

If secret server account don't have these AG-Super and datacenter.xxxgroupinc.net\abc-s groups then we need to add these 2 entitlements.

Issue with backslash or query logic


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Raghu
All-Star
All-Star

@Ashir  try bewlo

 

SELECT
    ev.entitlement_valuekey AS entvaluekey,
    ev.entitlement_value,
    a.accountkey AS acctkey,
    a.name AS accname,
    u.username,
    a.endpointkey,
    u.userkey AS userkey,
    'provision access' AS default_action_for_analytics
FROM
    entitlement_values ev
JOIN
    entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN
    accounts a ON a.accountkey IN (
        SELECT accountkey
        FROM accounts
        WHERE endpointkey = (
            SELECT endpointkey
            FROM endpoints
            WHERE endpointname = 'Secret Server'
        )
    )
JOIN
    user_accounts ua ON ua.accountkey = a.accountkey
JOIN
    users u ON u.userkey = ua.userkey
WHERE
    u.username IN (
        SELECT u.username
        FROM
            users u
        JOIN
            user_accounts ua ON u.userkey = ua.userkey
        JOIN
            accounts a ON ua.accountkey = a.accountkey
        JOIN
            account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
        JOIN
            entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
        JOIN
            endpoints e ON a.endpointkey = e.endpointkey
        WHERE
            a.status = 1
            AND e.endpointname = 'Secret Server'
            AND u.statuskey = 1
            AND u.username = '411043'
            AND ev.entitlement_value NOT IN ('AG-Super', CONCAT('datacenter.xxxgroupinc.net\\\\', systemUserName, '-s'))
    )
    AND ev.entitlement_value IN ('AG-Super', CONCAT('datacenter.xxxgroupinc.net\\\\', systemUserName, '-s'))
    AND a.status IN ('1', 'Manually Provisioned');


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Ashir
New Contributor
New Contributor

Hi @Raghu

It is not working, please suggest something else.