Highlighted
05 Base Camper

Prognosis API PQL Query

Hello,

 

I appreciate it if someone could help to get the PQL queries that I can append to the dataset to get the CPU and Memory of an individual node. Currently I am getting results if I run the below query but would like to filter it with a particular node. 

 

SELECT * FROM NtCpu NODE ALL

SELECT * FROM NtCpu NODE  '#Node-Group'  --> Fails says, invalid node group

 

Thanks 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Prognosis API PQL Query

Glad to hear that helped @Devan Arumugam!

 

If you include META.NODE in the request and that will return \<clustername>:<servername>.

For example:

..\irpqlcli "select ClusterName, META.NODE, IPAddress, CpuBusyPercent from CMAPERF NODE '#<clustername>\All Systems'"

Unfortunately, joins aren't a current featuer of PQL. If you have a requirement to merge/join data from two separate Prognosis records via the command line, the only suggestions I can think of would be to either:

a) Identify a common field (or fields) in each record that you can use to merge or join the two records. Then send the output of each command to csv so you can merge the data manually.

b) Use a scripting language to run the commands and merge the output, e.g. PERL or Powershell.

 

If you don't require that data exclusively as CLI output, there are a few other options:

1) You can merge data from two records into a Prognosis display window, using a Line-Line graph. This wouldn't help if you required tabular data however.

2) Collect the records to an SQL database via ODBC and you can use SQL queries to join data from two tables using.

3) Create a PACE collector to join data from two records. You would need to engage with our Professional Services team if you require a product extension to supply this.

There may be other options available via the WebUI, but this would also likely require either existing knowledge or engaging with our Pro services team.

 

If you need to specify a user to execute the PQL query under, you can pass in the username/password at runtime, which is detailed here https://help.prognosis.com/prognosis/118/user-interfaces/command-line-utilities/irpqlcli.

 

Regards,

Scott

 

 

View solution in original post

7 REPLIES 7
Highlighted

Re: Prognosis API PQL Query

Hi @Devan Arumugam ,

 

That node group needs to exist in the NODEGROUP configuration in Prognosis. I've attached a screen shot that demonstrates the correct syntax to display the same record output by requesting data directly from the node, from Entire Network (#AllNodes) and from two levels of the same nodegroup ('#Windows Systems' and '#Windows Systems\Platforms\All Windows Nodes').

The commands used were as follows (but the screen shot should make this a lot clearer):

..\irpqlcli "select * from NtCpu NODE '\W16-P117-MON'"

..\irpqlcli "select * from NtCpu NODE '#AllNodes'"

..\irpqlcli "select * from NtCpu NODE '#Windows Systems'"

..\irpqlcli "select * from NtCpu NODE '#Windows Systems\Platforms\All Windows Nodes'"

 

I hope this helps!

 

Regards,

Scott

 

Highlighted
05 Base Camper

Re: Prognosis API PQL Query

Thanks, Scott. That helped me to check locally to retrieve data. 

 

I was able to pull data for all call manager appliances with the below query and have a couple of questions. 

 

..\irpqlcli "select * from CallManagerApplianceCallManagerPerformanceStatus NODE '#CLUSTERNAME\All Systems'"

It gives data back and there is a column called IP address.

 

1. How do I pull the hostname for each appliance in that cluster instead of IP?

2. How to create a local username and password inside prognosis for API purpose rather using windows credentials?

 

Thanks again for your help. 

 

 

 

Highlighted

Re: Prognosis API PQL Query

Glad to hear that helped @Devan Arumugam!

 

If you include META.NODE in the request and that will return \<clustername>:<servername>.

For example:

..\irpqlcli "select ClusterName, META.NODE, IPAddress, CpuBusyPercent from CMAPERF NODE '#<clustername>\All Systems'"

Unfortunately, joins aren't a current featuer of PQL. If you have a requirement to merge/join data from two separate Prognosis records via the command line, the only suggestions I can think of would be to either:

a) Identify a common field (or fields) in each record that you can use to merge or join the two records. Then send the output of each command to csv so you can merge the data manually.

b) Use a scripting language to run the commands and merge the output, e.g. PERL or Powershell.

 

If you don't require that data exclusively as CLI output, there are a few other options:

1) You can merge data from two records into a Prognosis display window, using a Line-Line graph. This wouldn't help if you required tabular data however.

2) Collect the records to an SQL database via ODBC and you can use SQL queries to join data from two tables using.

3) Create a PACE collector to join data from two records. You would need to engage with our Professional Services team if you require a product extension to supply this.

There may be other options available via the WebUI, but this would also likely require either existing knowledge or engaging with our Pro services team.

 

If you need to specify a user to execute the PQL query under, you can pass in the username/password at runtime, which is detailed here https://help.prognosis.com/prognosis/118/user-interfaces/command-line-utilities/irpqlcli.

 

Regards,

Scott

 

 

View solution in original post

Highlighted
05 Base Camper

Re: Prognosis API PQL Query

Thanks, Scott. I will check them shortly. 

 

But regarding the question to a local user account, I would like to have an account set up in the case of LDAP failure. We access Prognosis WebUI with LDAP synced credentials. 

 

Thanks again. 

Highlighted

Re: Prognosis API PQL Query

You can specify a local user account when running the IRPQLCLI command as well. If you pass the username without a domain name prefix, it will normally default to localhost for authentication. Alternatively, you could also specify the hostname of the Prognosis node in place of the domain e.g.:

..\irpqlcli -u "<hostname>\<username>" -p "<password>" "select ClusterName, META.NODE, IPAddress, CpuBusyPercent from CMAPERF NODE '#<clustername>\All Systems'"

 

Highlighted
05 Base Camper

Re: Prognosis API PQL Query

@Scott_Clement  - Thanks for the info. But my question is related to the below help document. 

 

Looks like prognosis can be logged in only with LDAP accounts. What if the LDAP is down for some reason. Don't prognosis have a way of logging in with a local user account created inside the application? 

Highlighted
05 Base Camper

Re: Prognosis API PQL Query

I tested by creating a local user account in the server and could see it is working great. Thanks for your help. 

Webinar: Keep the modern workforce connected

Unified Communications has always been an important part of companies' digital transformation efforts due to its ability to enable rich virtual collaboration and communication. But with COVID-19, we've reached a break-through point.

Join Bill Haskins, Sr. Analyst & Partner, Unified Communications at Wainhouse Research, and John Ruthven, CEO at IR discuss UC challenges companies are experiencing due to the COVOID-19 crisis.

Join webinar