We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Actionable analytics "Resultset must have column" error.

Tommil
New Contributor III
New Contributor III

Hi, 

I am trying to create Actionable Analytic to deprovision accesses of manager's direct reports. When I try to run this analytics I get the following error: 

"Resultset Must Have column entvaluekey,acctKey for allowed actions Deprovision Access"

Even though I have those columns created in my SQL. And if I remove the Deprovision Access from 'Allowed Action' the analytics returns results.

Here is the SQL:

SELECT u.userkey as userKey, ev.entitlement_valuekey as 'entvaluekey', ev.entitlement_value as 'entvalue', a.accountkey as 'acctKey', u.username as 'Direct report username', u.employeeid as 'Direct report employeeid', u.email as 'Direct report email', rl.role_name as 'Assigned from role', a.endpointkey FROM users u join user_accounts ua ON ua.userkey = u.userkey join accounts a on a.ACCOUNTKEY = ua.ACCOUNTKEY join account_entitlements1 ae on ae.ACCOUNTKEY = a.ACCOUNTKEY join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey left join roles rl on ae.assignedfromrole = rl.rolekey
where u.manager = (select u1.userkey from users u1 where u1.username = ${Manager})

 

Any ideas what is causing the issue?

Best regards,

Tommi

13 REPLIES 13

pmahalle
All-Star
All-Star

Hi @Tommil ,

Can you try by removing single quote around acctKey and entvaluekey like below and try once. Ideally it should not impact but try it once.

SELECT u.userkey as userKey, ev.entitlement_valuekey as entvaluekey, ev.entitlement_value as entvalue, a.accountkey as acctKey, u.username as 'Direct report username', u.employeeid as 'Direct report employeeid', u.email as 'Direct report email', rl.role_name as 'Assigned from role', a.endpointkey FROM users u join user_accounts ua ON ua.userkey = u.userkey join accounts a on a.ACCOUNTKEY = ua.ACCOUNTKEY join account_entitlements1 ae on ae.ACCOUNTKEY = a.ACCOUNTKEY join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey left join roles rl on ae.assignedfromrole = rl.rolekey
where u.manager = (select u1.userkey from users u1 where u1.username = ${Manager})


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

Tommil
New Contributor III
New Contributor III

Hi @pmahalle ,

I tried to remove the single quotes around acctKey and entvaluekey but it did not resolve the error.

Regards,

Tommi

Hi @Tommil ,

SELECT u.userkey as userKey, ev.entitlement_valuekey as “entvaluekey”, ev.entitlement_value as “entvalue”, a.accountkey as “acctKey”, u.username as “Direct report username”, u.employeeid as “Direct report employeeid”, u.email as “Direct report email”, rl.role_name as “Assigned from role”, a.endpointkey FROM users u join user_accounts ua ON ua.userkey = u.userkey join accounts a on a.ACCOUNTKEY = ua.ACCOUNTKEY join account_entitlements1 ae on ae.ACCOUNTKEY = a.ACCOUNTKEY join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey left join roles rl on ae.assignedfromrole = rl.rolekey where u.manager = (select u1.userkey from users u1 where u1.username = ${Manager})

some columns for the Deprovision Access action.

  • entvaluekey
  • acctKey
  • accName
  • userKey

You have the first three columns, but you are missing the accName column. You can add this column to your query by selecting a.NAME as “accName” from the accounts table.

Please validate and let us know if further details are needed on this.

rushikeshvartak
All-Star
All-Star

SELECT    u.userkey               AS userkey,
          ev.entitlement_valuekey AS entvaluekey,
          ev.entitlement_value    AS entvalue,
          a.accountkey            AS acctkey,
          u.username              AS 'Direct report username',
          u.employeeid            AS 'Direct report employeeid',
          u.email                 AS 'Direct report email',
          rl.role_name            AS 'Assigned from role',
          a.endpointkey ,
          'Deprovision Access' AS 'Default_Action_For_Analytics'
FROM      users u
JOIN      user_accounts ua
ON        ua.userkey = u.userkey
JOIN      accounts a
ON        a.accountkey = ua.accountkey
JOIN      account_entitlements1 ae
ON        ae.accountkey = a.accountkey
JOIN      entitlement_values ev
ON        ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN roles rl
ON        ae.assignedfromrole = rl.rolekey
WHERE     u.manager =
          (
                 SELECT u1.userkey
                 FROM   users u1
                 WHERE  u1.username = ${Manager})

 

https://docs.saviyntcloud.com/bundle/EIC-Admin-v2021x/page/Content/Chapter17-EIC-Analytics/Configuri...


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Tommil
New Contributor III
New Contributor III

Hi,

Thanks for the suggestions.

I tried both of the queries you provided but still receiving the same error, even when adding the "accName" to the SQL query. I also tried to remove the ${Manager} dynamic attribute but receiving the same error.

Regards,

Tommi

Can you please provide screenshot of your Analytic Config ?

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

change variable name from Manager to Managaer_ID


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Tommil
New Contributor III
New Contributor III

Hi,

I did some more testing and removing the ${Manager} dynamic variable and instead showing every manager's direct reports accesses I now got it working.

But is it still possible to use Manager as a dynamic variable in Analytics where there is Deprovisioning access as Action?

Here is some pictures attached about the Analytics config and also Global Configuration Analytics.

Regards,

Tommi

 

 

DixshantValecha
Saviynt Employee
Saviynt Employee

Hi @Tommil,

Kindly inform us whether the issue has been resolved on your end or if you still require further assistance from Saviynt.

Tommil
New Contributor III
New Contributor III

This can be closed

DixshantValecha
Saviynt Employee
Saviynt Employee

Hi @Tommil,
Please help us with the resolution as well.

Tommil
New Contributor III
New Contributor III

Hi @DixshantValecha ,

We continued without using actions in the analytics.

But if there is more information how to get analytics with dynamic variable and deprovisioning as a action working could be shared here

Best regards,

Tommi

Hi @Tommil,

Please validate the suggestion given by @rushikeshvartak