querying the log from the command line?

4 posts / 0 new
Last post
PG4I
PG4I's picture
querying the log from the command line?

Suppose I have cqrlog running and I want to quickly check my log for a callsign? I can of course enter it in the callsign field and any qso's will show up in the table above, but what about using the mysql command for this from the command line?

Can anyone give an example how to do this? Thanks!

73 de Jo PG4I

oh1kh
querying the log from the command line?

HI Jo!

When cqrlog is running and "save data to local machine" you can connect database from command line with:

mysql --socket=/home/saku/.config/cqrlog/database/sock cqrlog001

Change saku to your username and cqrlog001 to log that you use. (The number part changes, 001 is the first log)
You will get prompt:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [cqrlog001]>

First we check tables:

MariaDB [cqrlog001]> show tables;
+---------------------------------+
| Tables_in_cqrlog001 |
+---------------------------------+
| call_alert |
| club1 |
| club2 |
| club3 |
| club4 |
| club5 |
| cqrlog_config |
| cqrlog_main |
| db_version |
| dxcc_id |
| freqmem |
| log_changes |
| long_note |
| notes |
| profiles |
| upload_status |
| version |
| view_cqrlog_main_by_callsign |
| view_cqrlog_main_by_qsodate |
| view_cqrlog_main_by_qsodate_asc |
| zipcode1 |
| zipcode2 |
| zipcode3 |
+---------------------------------+
23 rows in set (0.001 sec)

Qsos are in "cqrlog_main" table. Then it is good to check columns of cqrlog_main:


MariaDB [cqrlog001]> show columns from cqrlog_main;
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| id_cqrlog_main | int(11) | NO | PRI | NULL | auto_increment |
| qsodate | date | NO | MUL | NULL | |
| time_on | varchar(5) | NO | | NULL | |
| time_off | varchar(5) | YES | | | |
| callsign | varchar(20) | NO | MUL | NULL | |
| freq | decimal(10,4) | NO | | NULL | |
| mode | varchar(12) | NO | | NULL | |
| rst_s | varchar(20) | YES | | | |
| rst_r | varchar(20) | YES | | | |
| name | varchar(40) | YES | MUL | | |
| qth | varchar(60) | YES | MUL | | |
| qsl_s | varchar(4) | NO | | | |
| qsl_r | varchar(3) | NO | | | |
| qsl_via | varchar(30) | YES | | | |
| iota | varchar(6) | YES | | | |
| pwr | varchar(10) | YES | | | |
| itu | int(11) | YES | | 0 | |
| waz | int(11) | YES | | 0 | |
| loc | varchar(10) | YES | | | |
| my_loc | varchar(10) | YES | | | |
| county | varchar(30) | YES | | | |
| award | varchar(50) | YES | | | |
| remarks | varchar(200) | YES | | | |
| adif | int(11) | YES | MUL | 0 | |
| band | varchar(6) | YES | MUL | | |
| qso_dxcc | int(11) | YES | | 0 | |
| profile | int(11) | YES | | 0 | |
| idcall | varchar(20) | YES | MUL | | |
| state | varchar(4) | YES | | | |
| lotw_qslsdate | date | YES | | NULL | |
| lotw_qslrdate | date | YES | | NULL | |
| lotw_qsls | varchar(1) | NO | | | |
| lotw_qslr | varchar(1) | NO | | | |
| cont | varchar(3) | YES | | | |
| qsls_date | varchar(10) | YES | | NULL | |
| qslr_date | varchar(10) | YES | | NULL | |
| club_nr1 | varchar(100) | YES | MUL | | |
| club_nr2 | varchar(100) | YES | MUL | | |
| club_nr3 | varchar(100) | YES | MUL | | |
| club_nr4 | varchar(100) | YES | MUL | | |
| club_nr5 | varchar(100) | YES | MUL | | |
| eqsl_qsl_sent | varchar(1) | NO | | | |
| eqsl_qslsdate | date | YES | | NULL | |
| eqsl_qsl_rcvd | varchar(1) | NO | | | |
| eqsl_qslrdate | date | YES | | NULL | |
| rxfreq | decimal(10,4) | YES | | NULL | |
| satellite | varchar(30) | YES | | | |
| prop_mode | varchar(30) | YES | | | |
| stx | varchar(6) | YES | | NULL | |
| srx | varchar(6) | YES | | NULL | |
| stx_string | varchar(50) | YES | | NULL | |
| srx_string | varchar(50) | YES | | NULL | |
| contestname | varchar(40) | YES | | NULL | |
| submode | varchar(20) | YES | | NULL | |
+----------------+---------------+------+-----+---------+----------------+
54 rows in set (0.004 sec)

Knowing column fied names is important for making a query. You can replace column names by *, but then all columns are shown (long list).
Let's make a query to see if we have worked any OH-stations and when, what frequency and mode.

MariaDB [cqrlog001]> select qsodate,callsign,freq,mode from cqrlog_main where callsign like "OH%";
+------------+----------+---------+------+
| qsodate | callsign | freq | mode |
+------------+----------+---------+------+
| 2012-12-29 | OH0R | 1.8152 | CW |
| 2013-01-26 | OH0R | 1.8427 | CW |
| 2011-08-21 | OH1AF | 3.5000 | SSB |
| 2013-10-26 | OH1F | 21.2505 | SSB |
| 2013-11-23 | OH1F | 21.0058 | CW |
| 2013-01-27 | OH1HS | 1.8282 | CW |
| 2012-12-22 | OH1UM | 18.1221 | SSB |
| 2013-01-27 | OH2XX | 1.8123 | CW |
| 2013-12-08 | OH2YL | 10.1201 | CW |
| 2013-01-27 | OH4X | 1.8107 | CW |
| 2013-08-17 | OH5B/LH | 3.5000 | SSB |
| 2013-01-27 | OH5Z | 1.8110 | CW |
| 2012-12-29 | OH6MW | 1.8110 | CW |
| 2013-01-26 | OH6MW | 1.8434 | CW |
| 2012-02-15 | OH7CW | 28.0250 | CW |
| 2012-11-25 | OH7WW | 28.0978 | CW |
| 2011-11-03 | OH8KTN | 28.0313 | CW |
| 2013-01-26 | OH8X | 1.8103 | CW |
| 2012-10-28 | OH9A | 28.6569 | SSB |
+------------+----------+---------+------+
19 rows in set (0.001 sec)

So lets see full data fo OH1HS qso.

MariaDB [cqrlog001]> select * from cqrlog_main where callsign="OH1HS";
+----------------+------------+---------+----------+----------+--------+------+-------+-------+------+------+-------+-------+---------+------+------+------+------+------+--------+--------+-------+---------+------+------+----------+---------+--------+-------+---------------+---------------+-----------+-----------+------+-----------+-----------+----------+----------+----------+----------+----------+---------------+---------------+---------------+---------------+--------+-----------+-----------+------+------+------------+------------+-------------+---------+
| id_cqrlog_main | qsodate | time_on | time_off | callsign | freq | mode | rst_s | rst_r | name | qth | qsl_s | qsl_r | qsl_via | iota | pwr | itu | waz | loc | my_loc | county | award | remarks | adif | band | qso_dxcc | profile | idcall | state | lotw_qslsdate | lotw_qslrdate | lotw_qsls | lotw_qslr | cont | qsls_date | qslr_date | club_nr1 | club_nr2 | club_nr3 | club_nr4 | club_nr5 | eqsl_qsl_sent | eqsl_qslsdate | eqsl_qsl_rcvd | eqsl_qslrdate | rxfreq | satellite | prop_mode | stx | srx | stx_string | srx_string | contestname | submode |
+----------------+------------+---------+----------+----------+--------+------+-------+-------+------+------+-------+-------+---------+------+------+------+------+------+--------+--------+-------+---------+------+------+----------+---------+--------+-------+---------------+---------------+-----------+-----------+------+-----------+-----------+----------+----------+----------+----------+----------+---------------+---------------+---------------+---------------+--------+-----------+-----------+------+------+------------+------------+-------------+---------+
| 670 | 2013-01-27 | 16:06 | 15:37 | OH1HS | 1.8282 | CW | 599 | 599 | | | | | | | 100 | 18 | 15 | | KP01TN | | | | 224 | 160M | 0 | 0 | OH1HS | | 2013-11-21 | NULL | Y | | EU | NULL | NULL | | | | | | Y | 2013-11-03 | | NULL | NULL | | | NULL | NULL | NULL | NULL | NULL | NULL |
+----------------+------------+---------+----------+----------+--------+------+-------+-------+------+------+-------+-------+---------+------+------+------+------+------+--------+--------+-------+---------+------+------+----------+---------+--------+-------+---------------+---------------+-----------+-----------+------+-----------+-----------+----------+----------+----------+----------+----------+---------------+---------------+---------------+---------------+--------+-----------+-----------+------+------+------------+------------+-------------+---------+
1 row in set (0.001 sec)

As you see, it does not show up very nicely. Another try:

MariaDB [cqrlog001]> select qsodate,time_on,callsign,freq,mode,rst_s,rst_r,name from cqrlog_main where callsign="OH1HS";
+------------+---------+----------+--------+------+-------+-------+------+
| qsodate | time_on | callsign | freq | mode | rst_s | rst_r | name |
+------------+---------+----------+--------+------+-------+-------+------+
| 2013-01-27 | 16:06 | OH1HS | 1.8282 | CW | 599 | 599 | |
+------------+---------+----------+--------+------+-------+-------+------+
1 row in set (0.001 sec)

And when that is enough:
MariaDB [cqrlog001]> quit
Bye

These are the basics.

Single query can also be run from bash script and the received informaton can be used for following lines of script.
Output can be changed with parameters -N and -B

[saku@hamtpad ~]$ mysql --socket=/home/saku/.config/cqrlog/database/sock cqrlog001 -e "select qsodate,time_on,callsign,freq,mode,rst_s,rst_r,name from cqrlog_main where callsign='OH1HS';"
+------------+---------+----------+--------+------+-------+-------+------+
| qsodate | time_on | callsign | freq | mode | rst_s | rst_r | name |
+------------+---------+----------+--------+------+-------+-------+------+
| 2013-01-27 | 16:06 | OH1HS | 1.8282 | CW | 599 | 599 | |
+------------+---------+----------+--------+------+-------+-------+------+
[saku@hamtpad ~]$ mysql --socket=/home/saku/.config/cqrlog/database/sock cqrlog001 -Ne "select qsodate,time_on,callsign,freq,mode,rst_s,rst_r,name from cqrlog_main where callsign='OH1HS';"
+------------+-------+-------+--------+----+------+------+------+
| 2013-01-27 | 16:06 | OH1HS | 1.8282 | CW | 599 | 599 | |
+------------+-------+-------+--------+----+------+------+------+
[saku@hamtpad ~]$ mysql --socket=/home/saku/.config/cqrlog/database/sock cqrlog001 -Be "select qsodate,time_on,callsign,freq,mode,rst_s,rst_r,name from cqrlog_main where callsign='OH1HS';"
qsodate time_on callsign freq mode rst_s rst_r name
2013-01-27 16:06 OH1HS 1.8282 CW 599 599
[saku@hamtpad ~]$ mysql --socket=/home/saku/.config/cqrlog/database/sock cqrlog001 -NBe "select qsodate,time_on,callsign,freq,mode,rst_s,rst_r,name from cqrlog_main where callsign='OH1HS';"
2013-01-27 16:06 OH1HS 1.8282 CW 599 599

I hope this helps a bit. Google is a good source for mysql commanding syntax.

--
Saku
OH1KH

PG4I
PG4I's picture
Hello Saku!

Hello Saku!

Very helpful, thank you very much!

Regards,
Joop

oh1kh
querying the log from the command line?

My pleasure!

You can even access log without cqrlog running.
Start cqrlog.
Start console and give:
ps ax | grep mysqld_safe

You should see something like:

[saku@hamtpad ~]$ ps ax | grep mysqld_safe
4504 ? S 0:00 /usr/bin/sh /usr/bin/mysqld_safe --defaults-file=/home/saku/.config/cqrlog/database/mysql.cnf --datadir=/home/saku/.config/cqrlog/database/ --socket=/home/saku/.config/cqrlog/database/sock --port=64000
4741 pts/0 S+ 0:00 grep --color=auto mysqld_safe
[saku@hamtpad ~]$

copy the:
/usr/bin/mysqld_safe --defaults-file=/home/saku/.config/cqrlog/database/mysql.cnf --datadir=/home/saku/.config/cqrlog/database/ --socket=/home/saku/.config/cqrlog/database/sock --port=64000

That line starts database without running cqrlog. Save it for future use. You can now close cqrlog.

To stop that kind of mysqld_safe thread (I did not find any other way, maybe someone has better solution)
you must kill 2 tasks:

find them:
ps ax | grep mysqld

There are 2 lines with "cqrlog" in parameters like:

[saku@hamtpad ~]$ ps ax | grep mysqld
1250 ? Ssl 0:01 /usr/libexec/mysqld --basedir=/usr
5635 pts/0 S+ 0:00 /usr/bin/sh /usr/bin/mysqld_safe --defaults-file=/home/saku/.config/cqrlog/database/mysql.cnf --datadir=/home/saku/.config/cqrlog/database/ --socket=/home/saku/.config/cqrlog/database/sock --port=64000
5779 pts/0 Sl+ 0:00 /usr/libexec/mysqld --defaults-file=/home/saku/.config/cqrlog/database/mysql.cnf --basedir=/usr --datadir=/home/saku/.config/cqrlog/database/ --plugin-dir=/usr/lib64/mysql/plugin --log-error=/home/saku/.config/cqrlog/database//mysql.err --pid-file=hamtpad.pid --socket=/home/saku/.config/cqrlog/database/sock --port=64000
5835 pts/1 S+ 0:00 grep --color=auto mysqld
[saku@hamtpad ~]$

Pick numbers from lines and kill them with:

kill -9 5635 5779

--
Saku
OH1KH