MODIFYUSERJSON by querying Entitlement properties

[Community_User]

Originally posted on April 19 2020 at 15:39 UTC

Hi Guys,

I would like to know how to use MODIFYUSERJSON for Azure AD connection for querying a Azure SKU( ENTERPRISEPACK) and retrieve the Consumed Units, Prepaid Units and run a logic to check how many Prepaid Units are enabled and how many of them are Consumed. Based on which I would like to populate a flag 'E3' or 'E1' to one of the User CustomProperty. Below screenshot from SKU. I referred Freshdesk reference and it's not very clear.

Appreciate if anyone can frame a logic for me and let me know the steps how to use the same. Or atleast a High level logic would also be very helpful.

[Community_User]

Originally posted on April 20 2020 at 05:16 UTC

Hi Chandan,

If i understand you need to query entitlements assigned to user and fetch information and then update users custom property with the required information. You can use this if this is available in SSM by referring to ocrresponding tables using modifyuserjson.

Thanks

Ajay

[Community_User]

Originally posted on April 20 2020 at 06:55 UTC

Hi Chandan,

I have attached queries to fetch SKUs with available and no available licenses. Kindly check and let me know if you need any more information on this.

Thanks,

Lokesh

[Community_User]

Originally posted on April 20 2020 at 07:18 UTC

Thanks Ajay and Lokesh for your inputs. Saviynt is pretty new to me and I am trying to understand few things as part of one of the ongoing projects.

@Lokesh - If I am not wrong then these queries are for Analytics right?

How can I use them for MODIFYUSERJSON?

[Community_User]

Originally posted on April 22 2020 at 04:56 UTC

Adding further question from Chandra Ramesh: (found it in another thread : https://saviynt.freshdesk.com/a/forums/topics/43000523980

Chandan Ramesh replied

2 days ago on Mon, 20 Apr at 12:21 AM

Hi Guys,

Just a thought can we not achieve this using MODIFYUSERJSON ?

If yes, then any pointers how do I do it. All we need is to get a count of members in a group and if doesn't go beyond certain number then populate a flag into a Customepreoperty of User.

Any idea how to do it through MODIFYUSERJSON?

Chandan Ramesh replied

18 hours ago on Tue, 21 Apr at 3:44 AM

Hi Guys, need some help here, plz. I have been trying to fix this using Saviynt as we do not intend to use any external scripts.

Using MODIFYUSERDATAJSON how can we populate a CustomProperty with total count of accounts from a Entitlement. Below is the use case:

We want to populate User.CustomProperty50 with a static string based on the number of accounts present in an Entitlement.

I have put below logic but it's always returning 0. Not sure if I am referring to right column(ACCTENTMAPPINGINFOCOLUMNFROMENT) in Entitlement_Value table

Neither it's clear as to which table do I need to use for this, I mean should I use account_entitlements1 or entitlement_values table. Also, which is the column name that I need to refer.

Below is the syntax that I have tried with, could you please help me modify the same.

{

"ADDITIONALTABLES":{

"ENTITLEMENT_VALUES": "SELECT * FROM ENTITLEMENT_VALUES where ENTITLEMENT_VALUE LIKE 'CN=204002%'",

},

"COMPUTEDCOLUMNS":[

"CUSTOMPROPERTY50"

],

"PREPROCESSQUERIES":[

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY50 = (select count(ACCTENTMAPPINGINFOCOLUMNFROMENT) FROM ENTITLEMENT_VALUES)"

]

}

[Community_User]

Originally posted on April 22 2020 at 05:32 UTC

Hi Chandan,

Below is the query to find number of accounts for the entitlement value. You will have to connect accounts and Account_entitlements1 table for this.

SELECT count(acc.name)

from Accounts acc,Account_entitlements1 ac1 where acc.accountkey=ac1.accountkey and ac1.entitlement_valuekey=<<REFER YOUR ENT VALUE KEY>>

Thanks

Ajay

[Community_User · Accepted solution]

Originally posted on April 28 2020 at 01:56 UTC

Thank you Ajay. Will check the same.