Gathering Multiple Intervals of Data

[SRS]

I am trying to create a PQL query that gives me the peak usage over a 24 hour period on a SIP trunk.  I want to use a PQL because I want to add a REST command so I can pick up the data and import it into Splunk.

I have the data for one interval using this query:

<query>SELECT deviceName, CallsAttemptedLastInterval, META.NODE, ClusterName, META.SERVERTIME, META.INTERVAL, CallsActive FROM CallManagerApplianceTrunk WHERE DeviceName = 'XXX-ACME-Inbound-VZ-SIP-TRK' NODE ALL</query>.  I get results like this:

<headers>
<header>devicename</header>
<header>callsattemptedlastinterval</header>
<header>meta_node</header>
<header>clustername</header>
<header>meta_servertime</header>
<header>meta_interval</header>
<header>callsactive</header>
</headers>
<rows>
<row>
<value>XXX-ACME-Inbound-VZ-SIP-TRK</value>
<value>0</value>
<value>\AMERCUCM</value>
<value>AMERCUCM</value>
<value>2020-03-13T08:45:03.9623030-04:00</value>
<value>2020-03-13T08:45:03.9623030-04:00</value>
<value>367</value>
</row>
</rows>

How do I get the peak data over a 24 hour period using a query like this?

Also, I have been looking at other views to see if I can copy something there, but a lot of times the query will fail because of a parameter I am missing.  How do I find those missing parameters?  This one is obvious, but how do I pass them using a PQL query?

C:\Prognosis\Server>irpqlcli "SELECT * FROM CallManagerApplianceTrunkView4 NODE ALL"

Error - Code '47' Token '@Dev_Name' Message 'Parameter has not been assigned a value '@Dev_Name''

C:\Prognosis\Server>

Another question I have is in the list of PQL views, some views have no pql query associated with them, in this instance its CallManagerApplicanceTrunkView4:

CallManagerApplianceTrunkView2_Background | SELECT DeviceName, CallsAttemptedLastInterval, META.NODE, ClusterName, META.SERVERTIME, META.INTERVAL, CallsActive FROM CallManagerApplianceTrunk NODE ALL EVERY 30 SECONDS KEEP 30 INTERVALS TIMEOUT PERSISTENT DISTRIBUTED |

CallManagerApplianceTrunkView4 | |

CallManagerCallSummaryAssociateView21_Background | SELECT AssociateField, NumberOfFairVoiceStreams, META.NODE, META.SERVERTIME, META.INTERVAL, META.ASSOCIATE, NumberOfGoodVoiceStreams, NumberOfPoorVoiceStreams, NumberOfUnacceptableVoiceStreams FROM CallManagerCallSummaryAssociate WHERE SubCategory = 'MOS' NODE '#All Telephony Systems\Cisco CallManager Appliance\Clusters' ASSOCIATE 'QOS' EVERY 60 SECONDS KEEP 15 INTERVALS TIMEOUT PERSISTENT DISTRIBUTED

Why do these particular views have no queries associated with them?

Thanks for the help!

[budij · Accepted solution]

Hi,

There are multiple questions in the post. Let me answer it one at a time. Let me know if I missed one.

#1 - Get the peak data over a 24 hour period.

Let me just clarify the question here. You just want to get the maximum value for the CallsActive field in the past 24 hours.

There are several ways to do this. You can do this using PQL. But, we don't normally recommend it, because that means you'll have to keep all 24 hours data in PQL memory itself. The query that you have is by default selecting the data every 10 seconds. I'd suggest to increase the request interval, e.g. 10 minutes, or 1 hour interval (see the example below). And, then you can add the KEEP clause to instruct PQL that it should keep the data for the last 24 intervals (hence 24 hours in the example below). The second query in the example below will collapse all rows in PQL and group by the node and cluster name, and give you the maximum number of calls active. You can then write the third query in the rest api data xml file.

1. ..\x64\irpqlcli.exe "CREATE VIEW CallsActive24Hours AS SELECT deviceName, CallsAttemptedLastInterval, META.NODE, ClusterName, META.SERVERTIME, META.INTERVAL, CallsActive FROM CallManagerApplianceTrunk WHERE DeviceName = 'XXX-ACME-Inbound-VZ-SIP-TRK' NODE ALL EVERY 1 HOUR KEEP 24 INTERVALS"
2. ..\x64\irpqlcli.exe "CREATE VIEW CallsActiveMax AS SELECT MAX(CallsActive) from CallsActive24Hours GROUP BY META.NODE, ClusterName"
3. ..\x64\irpqlcli.exe "SELECT * FROM CallsActiveMax"

Documentation link: https://help.prognosis.com/prognosis/117/pql-retrieving-live-data-25168726.html

Although you can do this using PQL, I would suggest that you create a database collection for it and select the data for the past 24 hours through PQL. This way, the data is written to disk and is persisted and PQL memory is not high (no need to store 24 hours worth of data).

#2 - Inserting the parameter for a query on the command line, e.g.

irpqlcli "SELECT * FROM CallManagerApplianceTrunkView4 NODE ALL"

Error - Code '47' Token '@Dev_Name' Message 'Parameter has not been assigned a value '@Dev_Name''

You will need to use a -b parameter to supply the prompt value.

irpqlcli "SELECT * FROM CallManagerApplianceTrunkView4 NODE ALL" -b dev_name=abc

You can find out all prompt values required by using the -t parameter. The query below will print out all prompt values required for the given view.

irpqlcli "SELECT * FROM CallManagerApplianceTrunkView4 NODE ALL" -t

#3 - The view queries in sys.pql.views sometimes doesn't show the query itself. The query shows is only for persisted views only. The ones that are not persisted will not have the query. This behaviour will change in the future.

[SRS]

Thank you so much.  I don't feel quite as stupid anymore because this looks more complicated than I imagined.

[budij]

That's okay. Once you've done a bit more PQL queries, you'll understand the tricks of what it can and cannot do. Just keep asking the questions if you need help. Thanks.

[SRS]

To all future forum members, this is how I ended up doing this:

I made a database collection for ISDNCalls24Hours with all the columns I was interested in.  I then wrote this query: 

SELECT gwayname, devname, chanavai, chanused AS peakactivecalls, META.SERVERTIME, META.INTERVAL from IptDevicePSTNInterface DATABASE 'ISDNCallsActive24Hours' PERIOD NOW()-24 HOURS TO END WHERE devname= @Param

This returns the channels used over 24 hours in 10 second intervals. I just replace the @Param with the device name.
Now I can get the MAX(chanused) to get the peak calls in a 24 hour period