SDRTrunk Shell script to parse sdrtrunk recording dir and sort things out by date/talkgroup and merge mp3.

[1966Mustang]

My SDRTrunk preferences for recorded calls is set to /storage/sdrtrunk_recordings/current, so this is where they all end up.

It can gather 10000s of small mp3s in a day.

This was annoying to me.

The script finds all mp3 created 'yesterday' in the 'current' folder.

ppc@linuxbox:/storage/sdrtrunk_recordings/current$ ls -l
total 35968
-rw-rw-r-- 1 ppc ppc  2994 Sep 29 00:00 20230929_000012PossumCreek_PossumCreek_TRAFFIC__TO_608_FROM_5556762.mp3
-rw-rw-r-- 1 ppc ppc  1896 Sep 29 00:00 20230929_000013PossumCreek_PossumCreek_TRAFFIC__TO_609.mp3
-rw-rw-r-- 1 ppc ppc 21714 Sep 29 00:00 20230929_000016PossumCreek_PossumCreek_TRAFFIC__TO_611_FROM_5556191.mp3
-rw-rw-r-- 1 ppc ppc  4794 Sep 29 00:00 20230929_000017PossumCreek_PossumCreek_TRAFFIC__TO_612_FROM_5556768.mp3
-rw-rw-r-- 1 ppc ppc  2992 Sep 29 00:00 20230929_000019PossumCreek_PossumCreek_TRAFFIC__TO_616_FROM_16044.mp3

It then parses the filename and groups and sorts them into created archive/DATE/Talkgroup folders.

ppc@linuxbox:/storage/sdrtrunk_recordings/archive/20230927$
total 536
drwxrwxr-x 2 ppc ppc  61440 Sep 29 14:55 608
drwxrwxr-x 2 ppc ppc   4096 Sep 29 14:55 609
drwxrwxr-x 2 ppc ppc 376832 Sep 29 14:55 611
drwxrwxr-x 2 ppc ppc  90112 Sep 29 14:55 612
drwxrwxr-x 2 ppc ppc   4096 Sep 29 14:55 616

This solved one problem, but the next problem was now I just had 1000's of files in a bunch of directories in archive/DATE/TALKGROUP...

ppc@linuxbox:/storage/sdrtrunk_recordings/archive/20230927/608$ ls -l | head -5
total 3096
-rw-rw-r-- 1 ppc ppc  2634 Sep 27 21:23 20230927_212304PossumCreek_PossumCreek_TRAFFIC__TO_608_FROM_5556521.mp3
-rw-rw-r-- 1 ppc ppc  3712 Sep 27 21:23 20230927_212306PossumCreek_PossumCreek_TRAFFIC__TO_608_FROM_16040.mp3
-rw-rw-r-- 1 ppc ppc 12714 Sep 27 21:23 20230927_212313PossumCreek_PossumCreek_TRAFFIC__TO_608_FROM_5556521.mp3
-rw-rw-r-- 1 ppc ppc  2632 Sep 27 21:23 20230927_212316PossumCreek_PossumCreek_TRAFFIC__TO_608_FROM_16040.mp3

Another example:

ppc@linuxbox:/storage/sdrtrunk_recordings/archive/20230927/609$ ls -l | head -5
total 208
-rw-rw-r-- 1 ppc ppc   815 Sep 27 21:44 20230927_214402PossumCreek_PossumCreek_TRAFFIC__TO_609.mp3
-rw-rw-r-- 1 ppc ppc  2631 Sep 27 22:02 20230927_220224PossumCreek_PossumCreek_TRAFFIC__TO_609_FROM_16040.mp3
-rw-rw-r-- 1 ppc ppc  7311 Sep 27 22:03 20230927_220312PossumCreek_PossumCreek_TRAFFIC__TO_609_FROM_16040.mp3
-rw-rw-r-- 1 ppc ppc  3711 Sep 27 22:03 20230927_220314PossumCreek_PossumCreek_TRAFFIC__TO_609_FROM_16040.mp3

It's rather inconvenient to select all of them and make your mp3 player play all these small files.

So, I made a script to go into the DATE/TALKGROUP directories and merge them in order of occurance into a single DATE_TALKGROUP.mp3 file. This ends up in the 'merged' folder.

ppc@linuxbox:/storage/sdrtrunk_recordings/merged$ ls -l
total 45208
-rw-rw-r-- 1 ppc ppc  2253393 Sep 29 15:46 20230927_608.mp3
-rw-rw-r-- 1 ppc ppc   143145 Sep 29 15:46 20230927_609.mp3
-rw-rw-r-- 1 ppc ppc 14997177 Sep 29 15:45 20230927_611.mp3
-rw-rw-r-- 1 ppc ppc  4746537 Sep 29 15:45 20230927_612.mp3

Now, they are 'fairly' convenient and each DATE/TALKGROUP.mp3 represents all of the recorded call data from those dates.

Oh... A couple of other things it does is generate SQL files about the source mp3s for loading into a postgres database. Not sure what I am going to do with this yet, but it was fun.

Script is freely available at: GitHub - xtpclark/sdrtrunk_recording_parser: Scripts to parse the sdrtrunk recording dir and move stuff around by date and talk group.

If you choose to check it out, please read through or ask questions and/or correct anything...

"It works on my box..."

 

[1966Mustang]

Without parsing every file with exiftool, cuts time dramatically... Just parsing out parts of the file name is adequate to be able to do a match on a lookup table - for instance if you wanted to know what the talkgroup description is.

pclark@nvr:/storage/sdrtrunk_recordings/script$

time bash ./ParseScannerFiles.sh                                                                                                                                                             
                                                                                                                                                                   
Found 11113 mp3 files in current to sort!                                                                                                                                                                                                    
Exiftool data loading disabled.                                                                                                                                                                                                              

-- Insert info parsed/assembled from file names
INSERT 0 11114
(extra row is a placeholder)                                                                                                                                                                                                                               

Parse 11,113 mp3s:
real    3m47.086s
user    2m46.231s
sys     1m15.333s

Sort files into DATE/TG dirs:                                                                                                                                                                                                                                             
real    0m19.125s                                                                                                                                                                                                                            
user    0m13.203s                                                                                                                                                                                                                            
sys     0m6.429s

Merge files in each DATE/TG sorted into 21 TG files:
real    0m21.433s
user    0m14.593s
sys     0m1.059s

Sample result:

current/
├── 20231001_000001Norfolk_Norfolk_TRAFFIC__TO_661_FROM_5555286.mp3
├── 20231001_000021Norfolk_Norfolk_TRAFFIC__TO_661_FROM_5555286.mp3
├── 20231001_111753Norfolk_Norfolk_TRAFFIC__TO_646_FROM_5555132.mp3
├── 20231001_111759Norfolk_Norfolk_TRAFFIC__TO_646_FROM_16038.mp3
└── 20231001_111805Norfolk_Norfolk_TRAFFIC__TO_646_FROM_16038.mp3

archive/
└── 20231001
    ├── 661
    │   ├── 20231001_011021Norfolk_Norfolk_TRAFFIC__TO_661_FROM_5555286.mp3
    │   └── 20231001_011033Norfolk_Norfolk_TRAFFIC__TO_661_FROM_5555286.mp3
    └── 719
        ├── 20231001_111753Norfolk_Norfolk_TRAFFIC__TO_646_FROM_5555132.mp3
        ├── 20231001_111759Norfolk_Norfolk_TRAFFIC__TO_646_FROM_16038.mp3
        └── 20231001_111805Norfolk_Norfolk_TRAFFIC__TO_646_FROM_16038.mp3

merged/
├── 20231001_661.mp3
└── 20231001_646.mp3

I should add the trunked system/tg alias name to the merged mp3 name, something like:
20231001_Norfolk_Police_661.mp3..

 

[a417]

I do something similar with a bash script and rsync.

Your solution is considerably more comprehensive and thorough. I do like the SQL, kind of future proofs things for you, should you get really ambitious.

 

[1966Mustang]

Thank you - It is working well for the sorting/merging etc and makes things a bit easier for me to find. Still a work in progress.

The database part is a wireframe for articulating an intelligent feature request to the SDRTrunk author. I think that at the simplest it might be is to spit out CSVs per MP3. I guess the stats you can generate are fun - How many police/fire/ems calls on a particular day, which radio communicates the most/longest... Which days of the week are busiest. At least that's what I've done with the sql'd data just from parsing the names of the generated mp3's joined with a table of the talkgroups/systems I'm monitoring.

We'll see where it goes!

 

[1966Mustang]

Changing how things work a bit... so that the parser could run multiple times a day.

1. Parse the files in current/ into CSV, load into database. This is configurable to parse files not newer than HH:MM:SS.
i.e. Parse all files older than 1 minute ago.

2. Move the files found into /archive/date/talkgroup folders

3. Simplify the merge mp3 (or make it more flexible..)
Merging all of the mp3's in a particular /archive/date/talkgroup folder to /merged/date_talkgroup.mp3 file - some of which you may not care about... wastes space, and isn't really necessary if you're not going to listen or do anything with them.
(Why record them in the first place is another question I guess)

So, now how it's going to work for the merging mp3's part is:
Run a query (or queries) to merge mp3s - combine talkgroups, categories, time of day, etc. Mix and Match to your heart's content!

For example, you could have a query to merge all talkgroup activity that occured between time/date into a single mp3.

(ffurl = ffmpeg file list entry)

Find out where the public works guys are having lunch... (these folks are hilarious)
select * from vw_all where lower(tag) ~ 'public works' AND dt=CURRENT_DATE and hms between '11:30:30' AND '13:30:00';

Query to merge all traffic that occurred today between some time.
select ffurl from vw_all where dt = CURRENT_DATE and hms between '18:00:00' AND '22:00:00' order by ts_tz;

Query to merge TG activity for the fire departments you monitor that occurred overnight:
select ffurl from vw_all where lower(category) ~'fire' and dt = ( CURRENT_DATE ) and hms between '00:00:00' and '06:00:00' order by ts_tz;

Query to merge specific TG activity that occurred yesterday between some time:
select ffurl from vw_all where talkgroup in (608,611,625) and dt = ( CURRENT_DATE -1 ) and hms between '00:00:00' and '06:00:00' order by ts_tz;
.

Still doing some monkey testing on this but will push the code and sample database to git when I have determined it is healed enough. Kinda fun.

Later

 

[1966Mustang]

And gather stats like... Top 3 active talkgroups

sdr=# select talkgroup, tg_alpha_tg, tg_category, sum(calls) AS total_calls from vw_call_stats  group by 1,2,3 order by 4 desc limit 3;
 talkgroup | tg_alpha_tg  | tg_category | total_calls 
-----------+--------------+-------------+-------------
       608 | NPD 1st Main | Police      |       18529
       611 | NPD 2nd Main | Police      |       17916
       647 | NFD Tac 2    | Fire/EMS    |        4334
(3 rows)

 

[belvdr]

Turn those into views to make things really easy.

 

[1966Mustang]

Code is healed. GitHub - xtpclark/sdrtrunk_recording_parser: Scripts to parse the sdrtrunk recording dir and move stuff around by date and talk group.

 

[belvdr]

You have duplicate indexes here:

CREATE INDEX trs_tg_tg_decimal_idx ON public.trs_tg USING btree (tg_decimal);
CREATE INDEX trs_tg_tg_decimal_tg_alpha_tg_idx ON public.trs_tg USING btree (tg_decimal, tg_alpha_tg);

The last one will cover both. Likely won't cause any performance issues, but it's unnecessary.

 

[1966Mustang]

@belvdr under investigation...

sdr=# select * from pg_stat_user_indexes ;
 relid  | indexrelid | schemaname | relname |           indexrelname            | idx_scan | idx_tup_read | idx_tup_fetch 
--------+------------+------------+---------+-----------------------------------+----------+--------------+---------------
 722071 |     722081 | public     | trs_tg  | trs_tg_tg_decimal_idx             |        9 |            9 |             9
 722032 |     722132 | public     | extdat  | extdat_ext_filename_idx           |        1 |        26709 |             0
 722032 |     722133 | public     | extdat  | extdat_ext_date_idx               |       49 |       639080 |        434834
 722032 |     722134 | public     | extdat  | extdat_ext_talkgroup_idx          |     1389 |       972467 |        872629
 722071 |     722135 | public     | trs_tg  | trs_tg_tg_alpha_tg_idx            |        0 |            0 |             0
 722071 |     722136 | public     | trs_tg  | trs_tg_tg_decimal_tg_alpha_tg_idx |     9310 |         9426 |          9426
 722853 |     722862 | public     | pref    | pref_pref_name_idx                |       16 |           12 |            12
 722032 |     722874 | public     | extdat  | extdat_pkey                       |        0 |            0 |             0
(8 rows)

 

[1966Mustang]

Big update to the database/parsing.