docs.loway.ch :: Loway Documentation Center

Diagnostics and tools

Uniloader is meant to help automate a number of little tasks that pertain to administering and running a QueueMetrics system.

Diagnostics: AMI connection test

Uniloader lets you test an AMI port from the command line. It will also check that the queuemetrics context is present on the system, and will make sure that the AMI user has the required "originate" privilege.

$ ./uniloader test ami -?

NAME:
   uniloader test ami - Tests an AMI connection

USAGE:
   uniloader test ami [command options] [arguments...]

DESCRIPTION:
   This command test an AMI connection.

It checks that the `queuemetrics` context is present and its
functions are present. It checks originates to `10@queuemetrics`
and prints available queues.

OPTIONS:
   --host "127.0.0.1"				Your Asterisk server
   --port "5038"				The AMI port on Asterisk
   --login 					The AMI user as defined in manager.conf
   --secret 					The AMI secret [$AMISECRET]
   --testChannel "Local/10@queuemetrics"	The channel to use when testing originates.
   --testExtCtxt "10@queuemetrics"		The ext@ctxt to use when testing originates.

In order to use it, you can call it like:

$ uniloader test ami --login admin --secret amp111

It will print out a comprehensive report, like:

Testing AMI connection to 10.10.5.27:5038 - Username 'admin' secret '******'
AMI Connected: Asterisk Call Manager/1.3

  N.   Meaning                             ext@queuemetrics         Present?
-_----------------------------------------------------------------------------
    1    Dummy extension                           10                 Ok
    2    Chanspy – inbound calls                   11                 Ok
    3    Sets a call status                        12                 Ok
    4    Chanspy – outbound calls                  14                 Ok
    5    Add call feature                          16                 Ok
    6    Remove call feature                       17                 Ok
    7    Agent pause                               22                 Ok
    8    Agent unpause                             23                 Ok
    9    AddMember                                 25                 Ok
   10    RemoveMember                              26                 Ok
   11    Custom dial                               28                 Ok
◉  12    Send SMS to SIP device                    29                 MISSING
   13    Soft hangup of call in queue              30                 Ok
   14    Redirect call in queue                    31                 Ok
   15    Agent pause with hotdesking               32                 Ok
   16    Agent unpause with hotdesking             33                 Ok
   17    Addmember with hotdesking                 35                 Ok
   18    Removemeber with hotdesking               37                 Ok

Known Queues                            Completed   Abandoned
-_-------------------------------------------------------------
 - 300                                         10           5
 - 301                                          0           0
 - default                                      0           0
 - 400                                          0           0

Originate on 10@queuemetrics worked.

This shows:

The version of AMI in use
Whether all default queuemetrics extensions are present, and which ones are missing
The queues configured in Asterisk, and their current usage statistics
Whether the user has "originate" privileges

If connection is possible, it returns with a status code of zero; if not possible, or wrong credentials are used, it returns with an error code so that you can script it.

Originating custom channels

It is possible to use have Uniloader originate arbitrary channels on the PBX by telling it the channel and the extension and context to connect.

$ uniloader amitest --login admin --secret 123 --testChannel SIP/701 --testExtCtxt 706@from-internal

In the example above, first channel 'SIP/701' is brought up, and then it is connected to extension '706' in context 'from-internal'.

Diagnostics: Test upload link

If you want to make sure that your upload credentials to a server (either HTTP/S or SQL) are working, you can test them by using:

$ ./uniloader test upload -?

NAME:
   uniloader test upload - Tests a data upload connection

USAGE:
   uniloader test upload [command options] [arguments...]

OPTIONS:
   --uri, -u 			The connection URI. Valid URIs start with file:, mysql:, http:, https:
   --login, -l "webqloader"	The login for your connection
   --pass, -p "qloader"		The password for your connection [$UPASSWD]
   --token, -t 			In MySQL mode, the partition. In HTTP/S mode, usually blank or server-id
   --timeout "10"		The time-out to wait for (in seconds) on errors.

If the command runs and succeeds, it will print out the current high water mark for the back-end and return with a status of zero. If there is any error, or the format of the connection is invalid, it will return with a status different than zero.

For example, this command tests a local database that contains data:

$ uniloader testupload  --uri "mysql:tcp(127.0.0.1:3306)/queuemetrics?allowOldPasswords=1" \
                        --login queuemetrics --pass javadude --token P001
Testing upload credentials.
2017/07/27 14:28:14 Error: no db object
2017/07/27 14:28:14 Assert: DB Connection works
2017/07/27 14:28:14 [,P001] Driver error: retrying in 200 ms

High Water Mark is 1472824811 [2016-09-02 16:00:11 +0200 CEST]
Connection OK

As back-ends keep on retrying for errors automatically, the tool waits for a missed answer within 'timeout' seconds before giving up and marking the connection as invalid.

Diagnostics: Test connection to Mysql/MariaDB database

This tool will check that you have a working connection to your Mysql or MariaDB database, and that the credentials you use are correct.

NAME:
   uniloader test mysql - Tests a MySQL/MariaDB connection

USAGE:
   uniloader test mysql [command options] [arguments...]

DESCRIPTION:
   This command tests connection credentials.

It just connects to the database and runs an empty query
to confirm everything is working as expected.

OPTIONS:
   --dburi "tcp(127.0.0.1:3306)/queuemetrics?allowOldPasswords=1" The database to connect to
   --login "root"             A database user
   --pwd                A database password

An example run:

uniloader test mysql --dburi 10.10.5.27/somedb --login user --pwd pass

Will print:

2019/04/02 09:04:13 Testing MySQL connection to 'user:pass@tcp(10.10.5.27:3306)/somedb?allowOldPasswords=1'

2019/04/02 09:04:13  -- Connection took 54.183µs
2019/04/02 09:04:13  -- Query took 21.387871ms
2019/04/02 09:04:13 Local time on database is: 2019-04-02 09:04:13

Diagnostics: Test connection to Postgres database

This tool will check that you have a working connection to your Postgres database, and that the credentials you use are correct.

$ uniloader test postgres -?

NAME:
   uniloader test postgres - Tests a Postgres connection

USAGE:
   uniloader test postgres [command options] [arguments...]

DESCRIPTION:
   This command tests a Postgres connection.

It just connects to the database and runs an empty query
to confirm everything is working as expected.

OPTIONS:
   --ps-uri "localhost/fusionpbx"	A Postgres database to connect to
   --ps-login "fusionpbx"		A database user
   --ps-pwd 				A database password [$FUSIONPWD]

Example run:

uniloader test postgres --ps-uri 10.10.5.182/fusionpbx --ps-login fusionpbx --ps-pwd ""

Will print:

2019/02/22 09:59:32 Testing Postgres connection to 'postgres://fusionpbx:@10.10.5.182/fusionpbx'

2019/02/22 09:59:32  -- Connection took 298.534µs
2019/02/22 09:59:32  -- Query took 21.780999ms
2019/02/22 09:59:32 Local time on database is: 2019-02-22T00:50:58.357799+01:00

If you know that the database credentials are correct and you get a connection error that says unknown authentication response: 10, it is likely that your database is using SCRAM-SHA-256 authentication. This is supported in Uniloader from version 23.09.3 onwards - in case, update Uniloader and try again.

Diagnostics: Test Freeswitch’s ESL port

This tool tries connecting to Freeswitch’s ESL port and tries displaying queues and agents defined.

$ uniloader test fsw-esl -?

NAME:
   uniloader test fsw-esl - Test Freeswitch's ESL port

USAGE:
   uniloader test fsw-esl [command options] [arguments...]

DESCRIPTION:
   This command tries to connect to FreeSwitch's Event Socket.

OPTIONS:
   --host "127.0.0.1"	Your FreeSwitch server
   --port "8021"	The ESL port on FreeSwitch
   --auth "ClueCon"	The ESL auth secret [$AUTH]

For example:

uniloader test fsw-esl --host 127.0.0.1 --port 8021 --auth ClueCon

Will display a successful dialog:

2019/02/22 10:01:37 Testing Freeswitch connection to '127.0.0.1:8021' with auth token 'ClueCon'

2019/02/22 10:03:29 <ESL: Content-Type: auth/request
2019/02/22 10:03:29 <ESL:
2019/02/22 10:03:29  ======= Attempting log in
2019/02/22 10:03:29 >ESL auth ClueCon

2019/02/22 10:03:29 <ESL: Content-Type: command/reply
2019/02/22 10:03:29 <ESL: Reply-Text: +OK accepted
2019/02/22 10:03:29 <ESL:
2019/02/22 10:03:29  ======= Login OK
2019/02/22 10:03:29  ======= Showing queues in mod_callcenter
2019/02/22 10:03:29 >ESL api callcenter_config queue list

2019/02/22 10:03:29 <ESL: Content-Type: api/response
2019/02/22 10:03:29 <ESL: Content-Length: 543
2019/02/22 10:03:29 <ESL:
2019/02/22 10:03:29 <ESL: name|strategy|moh_sound|time_base_score|tier_rules_apply|tier_rule_wait_second|tier_rule_wait_multiply_level|tier_rule_no_agent_no_wait|discard_abandoned_after|abandoned_resume_allowed|max_wait_time|max_wait_time_with_no_agent|max_wait_time_with_no_agent_time_reached|record_template|calls_answered|calls_abandoned|ring_progressively_delay|skip_agents_with_external_calls|agent_no_answer_status
2019/02/22 10:03:29 <ESL: 75082016-6394-4738-b896-b9121c060612|longest-idle-agent|local_stream://default|system|false|30|true|true|900|false|0|90|5||1|10|0|true|On Break
2019/02/22 10:03:29 <ESL: +OK
2019/02/22 10:03:29  ======= Showing agents defined in mod_callcenter
2019/02/22 10:03:29 >ESL api callcenter_config agent list

2019/02/22 10:03:29 <ESL: Content-Type: api/response
2019/02/22 10:03:29 <ESL: Content-Length: 464
2019/02/22 10:03:29 <ESL:
2019/02/22 10:03:29 <ESL: name|system|uuid|type|contact|status|state|max_no_answer|wrap_up_time|reject_delay_time|busy_delay_time|no_answer_delay_time|last_bridge_start|last_bridge_end|last_offered_call|last_status_change|no_answer_count|calls_answered|talk_time|ready_time|external_calls_count
2019/02/22 10:03:29 <ESL: 739a4112-d755-4977-bf2b-d2b9037babd0|single_box||callback|{call_timeout=15}user/200@10.10.5.182|Available|Waiting|0|10|90|90|30|1550762836|1550762841|1550763105|1550762723|0|1|5|1550763195|0
2019/02/22 10:03:29 <ESL: +OK
2019/02/22 10:03:29  ======= Logging off
2019/02/22 10:03:29 >ESL exit

2019/02/22 10:03:29 <ESL: Content-Type: command/reply
2019/02/22 10:03:29 <ESL: Reply-Text: +OK bye
2019/02/22 10:03:29 <ESL:

Diagnostics: Test data download from Enswitch

This tool will check that you can download data from your Enswitch PBX, as one of its tenants, as QueueMetrics Live would do.

Example run:

uniloader test enswitch
         --uri https://www.my.pbx
         --login qm@cli
         --pwd ab1234
         --single-tenant 1234

Note that you always need to pass the numeric Customer ID in the single-tenant parameter.

Will print:

2021/03/31 19:08:07 Testing Enswitch connection to 'esw/https://www.my.pbx qm@cli ab1234 123'
2021/03/31 19:08:09 Downloaded 6 rows in 1748 ms
2021/03/31 19:08:09 Showing last 10 records:
2021/03/31 19:08:09 # 1: {1617156858|1617156848.656|q1|NONE|ENTERQUEUE||0447xxx}
2021/03/31 19:08:09 # 2: {1617156859|1617156848.656|q1|NONE|ABANDON|1|1|1}
2021/03/31 19:08:09 # 3: {1617166397|1617166387.152|q1|NONE|ENTERQUEUE||0427xxx}
2021/03/31 19:08:09 # 4: {1617166412|1617166387.152|q1|Agent/621|RINGNOANSWER|15000}
2021/03/31 19:08:09 # 5: {1617166425|1617166387.152|q1|Agent/621|CONNECT|28|Local/621@enswitch-local/n|8000}
2021/03/31 19:08:09 # 6: {1617166440|1617166387.152|q1|Agent/621|COMPLETEAGENT|28|15|1}

You can check credentials and download queue/agent configuration with the sub-command pbxinfo enswitch.

Diagnostics: Test an AudioVault server

It is possible to run a query on an AudioVault server as-if QueueMetrics would do it. See AudioVault for more details on how to enable it.

This test is useful to:

detect connectivity issues: if you use a proxy in front of AudioVault for HTTPS, you may want to make sure that it is working correctly
demonstrate slow search performance
print the correct configuration to be entered on QueueMetrics to enable AudioVault

To set up an AudioVault server, our suggested operational sequence is the following:

find a unique-of a call that you want to find from one of the recordings you have on disk
run uniloader av find to make sure that the search path is set up correctly and that your call-id can be found
run uniloader av serve to expose the service on a local port
run uniloader test audiovault pointing to the local HTTP URL to make sure that the service is configured correctly
set up an HTTPS proxy
run uniloader test audiovault pointing to the public HTTPS URL to make sure that everything is still working with the proxy in front

To run it, just use:

uniloader test audiovault --public-url http://127.0.0.1:4012 --token Sup3rZ3brA -c 443652.1

Note that there is no need to pass a timestamp if the call-id has the format "12345678.12" like is the default for Asterisk systems.

And this will output:

Connecting to server on http://127.0.0.1:4012/search/ with  token 'Sup3rZ3brA'
Searching for UID '443652.1' around timestamp '443652'
... took 4.553417ms
========= RESULTS FOUND: 1
  #| Type|     Size|                                  File
---|-----| --------|--------------------------------------
  1|  MP3|    66821|                     call-443652.1.mp3

It will also print out working QueueMetrics configuration.properties lines that will use the same configuration:

audio.server=it.loway.app.queuemetrics.callListen.listeners.JsonListener
audio.jsonlistener.url=http://127.0.0.1:4012/search/
audio.jsonlistener.method=POST
audio.jsonlistener.searchtoken=Sup3rZ3brA
audio.jsonlistener.verbose=false
audio.html5player=true

Diagnostics: Direct QueueMetrics database access

It is possible to use Uniloader to directly access a QueueMetrics queue_log database to perform low-level operations.

Database contents

It is possible to print the contents of a database:

$ uniloader qmdb  --dburi "localhost/queuemetrics" --login queuemetrics  --pwd javadude info

   #       Partition  # Entries          From date            To date    Time span
   1            P001   51129261   2018.09.03 07:09   2019.09.03 09:09     1y 0d 1h

This displays the contents of each partition.

Exporting a partition

You can export a partition to a text file in queue_log format; this can be useful for backup purposes or to upload it again to a different instance or a different partition:

$ uniloader qmdb  --dburi "localhost/qm" --login qm  --pwd 123 \
                  export --partition P001 --filename mylog.txt

Exporting partition: P001 (from: 0 to: 999999999999) to 'mylog.txt' in batches of 50000 rows
Finding zones: ........................................
Source zones: 27 -> Packed zones: 5 (Efficiency: 80%)
Processing zone 1 of 5: 0% done
Processing zone 2 of 5: 20% done
Processing zone 3 of 5: 40% done
Processing zone 4 of 5: 60% done
Processing zone 5 of 5: 80% done
Success: Written 154599 lines to 'mylog.txt'

When this runs, the contents of the queue_log table are split into "zones" that have a maximum of rows as defined in the batchsize parameter. The larger the batchsize, the more memory Uniloader uses, but of course the operation is quicker. As a rule of thumb, you can expect each 1000 rows to take ~700k of memory.

Exporting and reimporting

A common case for exporting data is to re-import it somewhere else or to a different partition within the same database.

This can be easily achieved:

uniloader qmdb  --dburi "localhost/queuemetrics" --login qm --pwd 234 \
                                      export --partition P001 --filename mylog.txt


uniloader -s mylog.txt upload --uri "mysql:otherserver/queuemetrics" \
                                      --login qm --pass 123 --token P002

Deduplicating data

If you happen to have duplicate data on a partition (eg. because the loader was run multiple times in parallel), you can easily detect it and, if needed, clean it up.

If the process is called with the --write flags, a deduplication will be performed; if not, as default, it will only inspect the partition.

Make a backup of the database before you attempt a clean-up. Data is deleted for good and is not recoverable.

To perform its task, dedupe performs a series of steps:

The contents of the queue_log table is split into a number of "zones", where each zone has a maximum of batchsize rows (default 500k).
Each zone is separately checked for duplicates
Only zones that actually have duplicate data are de-duplicated

You can run it like:

$ uniloader qmdb  --dburi "localhost/qm" --login qm  --pwd 1234 dedupe --write

Deduplicating P001 (0-999999999999) with batches of 30000 rows - write: true
Finding zones: ...................................................................
Source zones: 51 -> Packed zones: 12 (Efficiency: 75%)
Scanned zone 1 of 12 - 8% done - Dupes found: 0
Scanned zone 2 of 12 - 16% done - Dupes found: 0
Scanned zone 3 of 12 - 25% done - Dupes found: 19338
Scanned zone 4 of 12 - 33% done - Dupes found: 28094
Scanned zone 5 of 12 - 41% done - Dupes found: 20226
Scanned zone 6 of 12 - 50% done - Dupes found: 20606
Scanned zone 7 of 12 - 58% done - Dupes found: 10438
Scanned zone 8 of 12 - 66% done - Dupes found: 20126
Scanned zone 9 of 12 - 75% done - Dupes found: 21046
Scanned zone 10 of 12 - 83% done - Dupes found: 25302
Scanned zone 11 of 12 - 91% done - Dupes found: 14790
Scanned zone 12 of 12 - 100% done - Dupes found: 27438
 -- Total duplicates found: 207404

Cleaned zone 1 of 10 - 10% - Removed: 19338
Cleaned zone 2 of 10 - 20% - Removed: 28094
Cleaned zone 3 of 10 - 30% - Removed: 20226
Cleaned zone 4 of 10 - 40% - Removed: 20606
Cleaned zone 5 of 10 - 50% - Removed: 10438
Cleaned zone 6 of 10 - 60% - Removed: 20126
Cleaned zone 7 of 10 - 70% - Removed: 21046
Cleaned zone 8 of 10 - 80% - Removed: 25302
Cleaned zone 9 of 10 - 90% - Removed: 14790
Cleaned zone 10 of 10 - 100% - Removed: 27438
 -- Total duplicates removed: 207404

As duplicate rows may be loaded in memory, make sure that Uniloader has enough RAM to run in (it’s ~700K every 1000 records). It is possible to set e.g. --batchsize 30000 to specify a maximum size for each zone.

As the actual deduplication is performed by the database, it will create large temporary files during this process. Make sure that thee database server has plenty of disk space available. Using a smaller batch size helps if you see the process failing.

The process can be run safely on a live system that is uploading data, though it will severely affect database performance; so we suggest running it off-hours. We also suggest running a database optimization afterwards to compact the database if a lot of deletions were performed.

After deleting data, you need to clean the caches of QueueMetrics or restart it.

Creating private clones of reports

Sometimes, you want users to have private copies of "scratchpad" reports. This can be easily done from the main interface of QM, but to explain to all users how to make a private copy of a report can be burdensome.

We suggest instead that:

You create "template" reports, and make them public but protect them with an editing key that users do not have;
Using this command, you create a private copy of the report for each user, and set its editing key to ''.

This way, each user will have its own scratch copy of said reports, and shared changes won’t trigger optilock exceptions (or just unwanted modifications).

To do this, we imagine that:

the report you want to share exists and is named "A". Its name must be unique in the system.
you want each user to have a private copy called "A (pvt)", so we need to add a suffix "(Pvt)"
the users you want to give it to have their logins named named alice and bob in QM

uniloader qmdb --login queueumetrics --pwd javadude \
   clonereport --sourceTitle 'A' --destSuffix '(pvt)' --toUsers alice,bob --editingKey ''

This command will make sure that everything is in order; users and reports exist, and users do not have a report with the same name as the one that will be cloned.

To actually make changes, just add the '--forReal' parameter.

uniloader qmdb --login queueumetrics --pwd javadude \
   clonereport --sourceTitle 'A' --destSuffix '(pvt)' --toUsers alice,bob --editingKey '' --forReal

To leave keys unchanged, just set them to "=". That is the default. Any other value will be used to overwrite the key itself, so '' means "no key".

A sucessful output may look like:

2023/10/13 16:16:30 Will attempt cloning report 'A' for users [alice bob]
2023/10/13 16:16:30 Report #1 'A' for user demoadmin (Administrator) has 14 screens and 101 items (VK '' EK 'XXX')
2023/10/13 16:16:30 Starting to copy reports
2023/10/13 16:16:30  Cloning for alice...
2023/10/13 16:16:30   Done
2023/10/13 16:16:30   Report #4734 'A (pvt)' for user alice (Alice Cooper) has 14 screens and 101 items (VK '' EK '')
2023/10/13 16:16:30  Cloning for bob...
2023/10/13 16:16:30   Done
2023/10/13 16:16:30   Report #4850 'A (pvt)' for user bob (Bob Moog) has 14 screens and 101 items (VK '' EK '')
2023/10/13 16:16:30 Reports cloned

The values VK are the visibility key for that report and EK the editing key, respectively.

Before you run such transactions, you may want to make a backup of the QM database.

Diagnostics: Regexp tester

Writing regular expressions to be used in Uniloader is sometimes unwieldy; the syntax is arcane and it is not always clear which expressions will match.

Uniloader lets you test a Golang regular expression from the command line. It will make sure that the syntax is correct and will check it against a number of cases you supply, printing out their average execution time so you can optimize it.

$ uniloader test regexp -?


NAME:
   uniloader test regexp - Tests a Go Regular Expression (RegExp)

USAGE:
   uniloader test regexp [command options] [arguments...]

DESCRIPTION:
   This command test a Regular Expresion.

It makes sure that the RegExp is correct, shows how
it behaves against a number of inputs you provide and
tries measuring its performance.

A complete reference guide on Go RegExps is available
at https://pkg.go.dev/regexp/syntax


OPTIONS:
   --regexp value, -r value  The regular expression you want to test (default: "^.*")

In order to use it, you can call it like:

$ uniloader test regexp -r '^(?i)sip/|zap/' SIP/1234 PJSIP/4576 SIP/trunk-1234

And it will print out a table where you can see which items are a positive match (the ones with someting under the column "Matches?") versus the ones that are skipped.

Testing Regexp '(?i)^sip/|^zap/' - compilation took 24.542µs

| # |     TARGET     | MATCHES? |  TIME  |
+---+----------------+----------+--------+
| 1 | SIP/1234       | [SIP/]   | 15.8µs |
| 2 | PJSIP/4576     | -        | 10.1µs |
| 3 | SIP/trunk-1234 | [SIP/]   | 11.8µs |

Average regexp execution time (across all cases): 12.6µs

The elapsed time is not meant as an exact duration (though it is the average of a number of repeats) but as a guide to optimize your regexp - for example you can see significant changes by adding or removing the ^ that is used to anchor the regexp to the beginning of a line.

A complete reference guide on Go RegExps is available at https://pkg.go.dev/regexp/syntax

To improve matching efficiency and reduce CPU usage, whenever posibile you should "anchor" the regexp to the beginning of the line by using the caret symbol. Regular expressions are very powerful, but, as you know, with great power comes great responsibility and higher CPU load.