Can you query data from related voice streams database?

[MattAnderson]

We want to be able to query data from the related voice streams database for SBCs.  An example would be to query SBC voice quality information to see performance over several days, potentially for the purpose of graphing.  Is there an irpqlcli option for doing this?  I know there is a report option for SBC voice quality but this doesn't provide enough detail.  

[GeraldC1]

Hey great question and it so happens that Prognosis user interface can do this kind of thing for creating your own reports especially if the out of box report does not have enough detail. Does this Guid help? 

How to make a simple database collection and report

HTH

[MattAnderson]

Thanks Gerald, I'll take a look.  Is there a command line query option available as well?  Something like sql that can be used to query a Prognosis DB?  

[GeraldC1]

Hi Matt,

Yes there is. There is a command line query language mentioned here and it can query a Progosis database:

online help > Operations > Miscellaneous Operations > Prognosis Query Language (PQL)

A very simple example:

C:\Prognosis\Server\Configuration>..\irpqlcli "select * from ntcpu"

The syntax gets a bit quirky beyond this simple example and requires some training and we recommend organising this with your IR account manager if you'd like to explore that further.

Alternatively you could also set up Prognosis to send data to an SQL server, and then use SQL queries that way. 

HTH

[MattAnderson]

Any examples you can provide on querying a Prognosis DB?  NtCpu is a data field but if I want to pull historical data from a DB I'm not sure how to do that.  Would querying SessionBorderControllerCall, as an example, provide me historical call data?  Let's say historical call quality on a CUBE going a week back.  

[LouisZ]

Hi Matthew,

To get historical data for a Prognosis Record, the data must have been collected using a Prognosis Database. In this case, the SBCCALL record's historical data can be found inside the 'Related Voice Streams' database. With this information, you can modify the query to include the specific database to get the information from. If you do not specify the database, you will get the most recent or live data.

To query the data inside the database you can follow these steps: inside the Prognosis\Server\Configuration folder, you can run this command to get all rows in the SessionBorderControllerCall record from the Related Voice Streams database during a specific interval.

..\irpqlcli "select * from SBCCALL DATABASE 'Related Voice Streams' PERIOD 2019-02-13T00:00:00 TO 2019-02-20T00:00:00"

If you append -r to the executable command line, you can get a more readable format. Also note that you can use "SessionBorderControllerCall" instead of "SBCCALL", they are both aliases for the same record.

For example:

..\irpqlcli "select * from SessionBorderControllerCall DATABASE 'Related Voice Streams' PERIOD 2019-02-13T00:00:00.0000000 TO 2019-02-20T00:00:00.0000000" -r

Similar to SQL, you can also replace the "select *" with "select <comma-separated-fields-here>".

For example:

..\irpqlcli "select SessionBorderControllerName,ConnectTime,DisconnectTime,InboundMos,OutboundMos from SessionBorderControllerCall DATABASE 'Related Voice Streams' PERIOD 2019-02-13T00:00:00 TO 2019-02-20T00:00:00" -r

Hope that helps,

Louis

[MattAnderson]

Thanks!  This really helps.  Is there any documentation that provides examples like this?  The online help doesn't appear to have anything.  Is this just standard process query language?  

[GeraldC1]

> Is this just standard process query language? 

It is based on SQL (Standard Query Language) with semantic and syntax changes to fit the Prognosis data request model.

HTH