Documentation on how to write manual queries

[Community_User]

Originally posted on February 5 2019 at 00:07 UTC

Is there any documentation on how to write manual queries while configuring access reveiws (campaigns / attestations)? Does the query have to be a complete regular SQL query or does Saviynt have its native syntax for the queries (example - u.username like '%admin%' instead of SELECT username from users.....).

[Community_User]

Originally posted on February 8 2019 at 15:26 UTC

I'd like to also request a documented schema that we can easily refer to so that we understand what fields we should be using for what. Would be great to have that posted on this site with a notice if anything changes from release to release.

[Community_User]

Originally posted on February 16 2019 at 07:53 UTC

Please refer to the ER diagram for schema details.

[Community_User]

Originally posted on February 19 2019 at 15:42 UTC

@Nikita
Please know that your diagram does not show all fields. For the "User" table there are 78 fields that is not showing. I will need to see all of it please.

[Community_User]

Originally posted on February 20 2019 at 15:49 UTC

Yes. The lack of a data model or a complete schema is a big impediment to creating reports in Saviynt.

Either there should be a drag and drop feature to pick fields from tables, or the database schema should be posted to the portal so that we can easily refer to it and write our own SQL queries.

[Community_User]

Originally posted on March 12 2019 at 16:09 UTC

We need schema details for manual queries, is it possible to provide full schema description?

[Community_User]

Originally posted on March 26 2019 at 14:45 UTC

Ketan's suggestion was definitely helpful. In our SSM instances (5.3), though, I was unable to actually save the Analytics Control with the "desc xxx" queries; I could only preview. Saving simply failed silently without an explanatory error message. Have others experienced this behavior?

Also, this is a clever way to get the columns for various DB tables, but many of the query configurations in SSM actually seem to involve Groovy scripting and/or HQL instead of "raw SQL." For example, the documentation for the Entitlement Type Details config ( Viewing or Updating Endpoints ) contains the following example query for "Config For Requestable Entitlement In ARS":

ev.customproperty9= '${CollectiveModule}' and ev.customproperty1='TRUE' and ev.status=1

So, for these types of queries, we need to know the underlying object model. In training, our Saviynt trainer mentioned that we could use the Help button on the Create Email Template function to see a list of binding variables, and this seems to partially provide what's needed, but it's not comprehensive and doesn't list all the properties for various object types.

Has anyone found more comprehensive doc on what objects and properties are available to configure queries such as the Entitlement Type query mentioned above?

[Community_User]

Originally posted on March 26 2019 at 18:25 UTC

Hi everyone,

We will be posting a detailed document on schema by next week

[Community_User · Accepted solution]

Originally posted on March 29 2019 at 23:31 UTC

Hi All,

Please refer to the below article for schema details

https://saviynt.freshdesk.com/support/solutions/articles/43000493929-saviynt-security-manager-ssm-sc...

Hope that helps

[Community_User]

Originally posted on April 19 2019 at 10:40 UTC

Hi, Nikhil:

The Schema Guide is definitely very helpful and useful, thanks. But -- per my previous post on this thread -- we still need some level of documentation on the object model / syntax used to configure queries such as the one for "Config For Requestable Entitlement In ARS" in Entitlement Type Details. This seems to be more HQL or Groovy-oriented. Is some documentation along those lines in the works as well?

Thanks!

[Community_User]

Originally posted on September 20 2019 at 01:58 UTC

Hello,

Has there been any updates to the documentation or how to guides around creating and using HQL Queries for creating dashboards and other useful information?

Thanks 🙂

[Community_User]

Originally posted on December 20 2021 at 18:56 UTC

Looking forward for updates on this topic

[Community_User]

Originally posted on March 18 2022 at 13:29 UTC

Me too