Need a list of ALL Query definitions regardless of authority

[jsummers]

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?

[dhegland]

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.

[matt_giltaji · Accepted solution]

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

[jsummers]

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

[matt_giltaji]

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

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

[jsummers]

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

[jsummers]

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

[matt_giltaji]

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.

[KaelLizak]

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

[Chittaranjan_11]

Dear All, I am working on getting all the list of query definition from the server irrespective of users authority. I tried to execute the below mention command for session ON 

1. [ im diag --diag=viewallobjectsforsession on ], its working fine.

2. [ im queries --fields=createdBy,name,queryDefinition,lastModified,shareWith --fieldsDelim=$ >D:\CLI\querylist.txt ]

followed by i ran the next command too, but faced an error *** MKS124803: Internal error detected: Exception was: java.lang.IllegalArgumentException: Comparison method violates its general contract!

Could you help me to resolve this issue? Attached the error screenshot

[LLawton]

Hello,

I ran the exact command you specified, except for "F:" instead of "D:" (because I don't have a D drive).

It worked fine. I thought the separator could be a problem, but obviously not. Maybe the date format?

Basically I'm looking for things that may "violate your java contract". What an ugly message !;-)

I'm on version 11.2, based in the US so date format comes out as this: Jan 22, 2018 3:22:03 PM

Have you tried on another computer or do you consistently reproduce the error? 

Oh, now I saw the screenshot and realized there was no date. So forget about that.

Sorry, I don't have any other ideas.