Backforward slash is not working in SQL Query

[Ashir]

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

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

I need to add 2 entitlements 

1.AG-Super 

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

[NM]

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]

Hi @NM 

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

[NM]

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

[NM]

@Ashir , in concat use single quotes.

[rushikeshvartak]

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');

[Ashir]

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.

[rushikeshvartak]

Issue with backslash or query logic

[Raghu]

@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');

[Ashir]

Hi @Raghu

It is not working, please suggest something else.