cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Need a list of ALL Query definitions regardless of authority

SOLVED
Highlighted
Level 8

Need a list of ALL Query definitions regardless of authority

Need a list of ALL Query definitions regardless of authority, I'm administrator of Integrity.

I need to list all queries on our production system to see who is creating queries.

As we make changes to the work flows or fields I need to ensure I notify those users whose query could be effected by the change.

If there not a CLI that will support this function can a third party SQL query tool be used to extract this information?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Need a list of ALL Query definitions regardless of authority

Hi all,

There is a diag CLI command which helps with this a lot. You will need admin rights to be able to run it.

im diag --diag=viewallobjectsforsession on

After running this command, you will be able to see every query, report, chart, dashboard, etc even if the user did not explicitly share it with you. This only grants read access, if you need to modify anything (such as share a query belonging to a user that left the company to their replacement) you can copy the old query and modify the copy.

All of the queries/reports/charts/dashboards will show up in your client UI as well as in the CLI, so to get to the list of query definitions, you'll want to run a different CLI command after the diag (and pipe the output to a file).

im queries --fields=createdBy,name,queryDefinition

Unfortunately this is not possible to do from the SQL backend as the query definitions are stored in a column with other query metadata in a binary format and can't really be queried against.

As an alternate approach if you have only a few fields/types/states that you need to check, you can always look at the "References" tab of the particular admin object in the Admin Client and it will populate a list of everything (admin and regular user) that references that object, including queries, reports, charts, etc. The references view can be a bit slow and is probably not as useful as extracting the query definitions themselves, but it is very useful if you just need a quick way to see everything that could be impacted if you change a particular field or type.

Hope that helps,

Matt

8 REPLIES 8

Re: Need a list of ALL Query definitions regardless of authority

Joe is my new best friend. Thank you Joe!!!!

Hey PTC what do you have for us on this one? I've been a very patient user, but my inability to see users' queries easily in the normal UI is now becoming a barrier to scaling. Please act and thanks.

Re: Need a list of ALL Query definitions regardless of authority

Hi all,

There is a diag CLI command which helps with this a lot. You will need admin rights to be able to run it.

im diag --diag=viewallobjectsforsession on

After running this command, you will be able to see every query, report, chart, dashboard, etc even if the user did not explicitly share it with you. This only grants read access, if you need to modify anything (such as share a query belonging to a user that left the company to their replacement) you can copy the old query and modify the copy.

All of the queries/reports/charts/dashboards will show up in your client UI as well as in the CLI, so to get to the list of query definitions, you'll want to run a different CLI command after the diag (and pipe the output to a file).

im queries --fields=createdBy,name,queryDefinition

Unfortunately this is not possible to do from the SQL backend as the query definitions are stored in a column with other query metadata in a binary format and can't really be queried against.

As an alternate approach if you have only a few fields/types/states that you need to check, you can always look at the "References" tab of the particular admin object in the Admin Client and it will populate a list of everything (admin and regular user) that references that object, including queries, reports, charts, etc. The references view can be a bit slow and is probably not as useful as extracting the query definitions themselves, but it is very useful if you just need a quick way to see everything that could be impacted if you change a particular field or type.

Hope that helps,

Matt

Re: Need a list of ALL Query definitions regardless of authority

The reference tab is only good for ADMIN types not for user created queries

Re: Need a list of ALL Query definitions regardless of authority

Hmm, I'm not sure if its a version difference.

In 10.1, the reference tab shows me both admin and user objects

Re: Need a list of ALL Query definitions regardless of authority

Sorry my mistake it does show up needed to run the command and look at the results on the same server.

Working to verify this works well also created the following command so that I can use a spreadsheet over the results.

im queries --fields=createdBy,name,id,isAdmin,lastModified --fieldsDelim=$ --hostname=mks--port=7001 >querieslist.txt

Re: Need a list of ALL Query definitions regardless of authority

The authorization command would need to be ran each time prior to implementing any changes to change any new queries created, correct?

Re: Need a list of ALL Query definitions regardless of authority

I am not sure how the im diag --diag=viewallobjectsforsession on command works exactly in the backend, whether it allows the admin to bypass the read permissions (meaning it would affect any newly created queries) or it adds some hidden readonly permission on all exisitng queries (meaning it would only affect the items that already exist when the diag is run). Official PTC support would need to chime in on that.

The authorization lasts as long as the admin's session is active, so it will reset to default behavior when the admin's session ends from a timeout, server disconnect, client restart, etc.

Even if the diag is already on, turning it on again does not seem to negatively impact anything so you could make a batch file that has both the im diag and the im queries commands in it and just run the batch file whenever you wanted to do your analysis.

Re: Need a list of ALL Query definitions regardless of authority

The diag im diag --diag=viewallobjectsforsession on allows the current user to see all objects for the session, regardless of whether the objects are shared or not.

To answer Joe's question, each time you start a new session, if you need to be able to view objects that are not otherwise share to your user, you must re-run the diag to be able to access those unshared objects.

I do not have access to implementation details, so I cannot tell you what happens "under the covers" in this case.

Regards,

Kael


Kind Regards,
Kael Lizak

Senior Technical Support Engineer
PTC Integrity Lifecycle Manager