List a node's databases (collection & summarization) via a script

[Guillaume]

Hi,

I'm looking for a way to retrieve the list of databases from Prognosis nodes via a script.
It's pretty easy to do for analysts and thresholds via irpqlcli and the PrognosisStatus record, but databases are a bit more tricky since this record only shows active databases. Databases collection could be stopped (for whatever reason) and summarization databases only run when scheduled.

Since the irgui is able to display even not running databases, I'm guessing there's another way to retrieve that list.

How can I retrieve the list of databases from a Prognosis node (both summarization and collection databases, and both running and not running databases) ?

Thanks!

[GeraldC]

Try this command from Prognosis\server\configuration\
..\irpqlcli "execute Enumerate_DB" -r

HTH

[Guillaume]

Hi Gerald,

Thanks for your message.
I get an error message when I run the command:

E:\Prognosis\Server\Configuration> ..\irpqlcli "execute Enumerate_DB" -r

Error - Code '146' Token '' Message 'Access denied'

[Adam_Annen]

Hi Guillaume,

The above error is almost certainly due to your SECURITY Configuration settings. You could retry this a couple ways:

1. Try running the Admin Command prompt as another user (One that has command permissions)
2. You could edit your SECURITY Configuration and add an extra entry to give permission to a specific user for COMMANDS
3. You could try running the command from a command file in the Windows Client as per below screenshot. (Doing it this way also gives an advanced option where you can type in a specific user to run it as)

Note: Listing out all dbs (Running and not running) is built into Prognosis Self Monitoring V2.x. I know ScottB offered you this solution some time back (V1.x) and I believe you never did install it. Maybe this new need might tweak your interest in this solution? (It's a support freebie we will install and train you on).

- See https://online.prognosis.com/forum/us-ask-the-experts/prognosis-self-monitoring/

[Guillaume]

Hi Adam,

We did install the Prognosis Self Monitoring v1 a while back and had a training with Annie and yourself

Running the command through Prognosis via a command file is a good idea that I did not think about.
Unfortunately, I tried and it gave me the same error.

The user account I use is admin of the Windows server itself and has full admin rights in both the SECURITY configuration and on the WebUI.
We're currently investigating the situation with Gerald.

[Adam_Annen]

Hi Guillaume,

Here is one more thing to try.

Command with username and password specified:
- ..\irpqlcli -u (username) -p (password) "execute Enumerate_DB" -r
- If role based security is in place it will need to pass the username and password and check for the permissions given in the RBS. Substitute your user and password where the placeholders exist above.

Let me know if this helps

[Guillaume]

Thanks Adam!

Specifying my credentials makes it work. That's an improvement.

It's not very convenient for me since I need to run that query via a script and I can't hardcode credentials or prompt a user for its credentials (the script will be scheduled to run every once in a while).

[Guillaume]

Hi,

I've tried renaming the irrbs file to force Prognosis to generate a new one and now the Enumerate_DB query works.

If I then try to create a new role (and grant it all permissions), put my Active Directory group in it, and then remove all permissions from the Public role (because we don't want everyone to connect to Prognosis, we can only give access to a subset of users) and then run the same query again, it fails.

It looks like not specifying credentials to irpqlcli only gives results when the Public role has all permissions.

[GeraldC]

Hi Guillaume,

As per your finding and Adam's advice I can also confirm some more details about it that the locked down Public role is the cause and the No Access in Public takes precedence when there is no -u -p credentials included in the command. Try the following which seems to work in my simplified test lab to see if it works there:

1. in Public role enable just the one Administration - Database tick box; (This should allow this command without -u -p)
2. add a new role with a name like 'RevokeAllUsers' ; BUILTIN\Users group (or similar domain based group of all users); No Access.
3. Test it, and expected result should be:
- users/groups in roles that allow full access should still have full access;
- users/groups in Views only or other restricted roles should still have those restricted access and no access to Administration;
- users not in any role should be blocked from login into View Systems and Administration with "You do not have permission to log in to "View Systems" / "Administration""
- server console command line "..\irpqlcli “execute Enumerate_DB” -r" should be successful with no need for -u (username) -p (password).

Let us know how it goes and if this resolves it.