SQL to extract 'Private' CM-Group Contacts via a specific CM-Group Owner from a Linked Extn No

[MAWalker (United Kingdom)]

I’ve been experimenting with some helper scripts to extract CM-Group Contacts via a CM-Group Name & these work just fine (credit to to Sorvani - sorvani/freepbx-helper-scripts for FreePBX), however, rather than just get the Contacts of a named CM-Group, I would like to adjust the SQL to extract ALL the Contacts from ANY/ALL ‘Private’ CM-Groups associated with a given Owner via their linked ExtnNo.

eg. I start with an Extn No, from that get the Linked User (assuming there is one) & then extract all contacts within any/all ‘Private’ CM-Groups that user has created (ie. that they are the owner of).

I assume this should be possible with SQL JOINS between the various database table/field relationships that tie all that together, however, I don’t know how to expose the DB schema (related Table/Field Names) in order to build the SQL.

Perhaps someone can tell me what the relevant DB tables/fields are called and/or point me to how I can expose that info for myself (alternatively, if you already have & can share the required SQL for this then even better, although I’m happy to do it myself if I can get the info I need).

I can see some of this from the helper scripts, but I’m having to 2nd guess the missing bits;

Extensions Table (devices)
– Extn No (id) <-- Not sure if this is Primary index or whether a separate ExtnId field exists
– Extn Display Name (description)
– Linked User Id/Login Name (???) <-- This might exist here, or equivalent in Users Table
Users Table (???)
– User Id (???) <-- I assume they’ll be a numeric index field for each User Rec
– User Login Name (???)
– User Display Name (???)
– Linked Extn (???) <-- This might exist here, or equivalent in Extns Table

CM-Groups Table (contactmanager_groups)
– Group Id (id)
– Group Name (name)
– Group Type (???) <-- Internal/External/Private etc.
– Group Owner User Id/Login Name (???)
CM-Group Entries Table (contactmanager_group_entries)
– Parent Group Id (groupid)
– Entry Id (id)
– Entry Display Name (displayname)
CM-Group Numbers Table (contactmanager_entry_numbers)
– Parent Entry Id (entryid)
– Number Id (id)
– Number Type (type)
– Number (number)

To summarise, I guess the missing bits for me are;

1. How to get the Linked User Id/Login Name from an Extn No
2. How to get a List of ‘Private’ CM-Group Ids/Names from a given User Id/Login Name.

I hope I’ve made this clear enough to make sense. Any assistance would be much appreciated.

[dicko]

Hopefuly this will get you going for the first bit:-

 mysql asterisk -e "show tables where Tables_in_asterisk like 'contact%'; describe devices;describe users;select * from users limit 5;select * from devices limit 5;"

[MAWalker (United Kingdom)]

That’s great - Thanks very much. I believe that’s exactly what I needed to see for myself what tables & fields are required to tie all those elements together within an SQL lookup.

It looks like I needed the ‘userman_users’ table, rather than just ‘users’ (which seems to relate to Extns along with the Devices table) in order to get the Linked User Details. One of which is the linked ‘User Id’ which I believe corresponds to the Group ‘Owner Id’ in the CM-Groups Table - Perfect.

[dicko]

Excellent, I’d love to see your end product, careful not to inner yourself into a pretzel.

[MAWalker (United Kingdom)]

Thanks to your pointer last night, not only have I learnt a few useful mysql commands (‘show tables’, ‘describe [tablename]’), it also allowed me to find exactly what I needed to create the following SQL statement;

mysql asterisk -e "SELECT contactmanager_groups.name AS CMGroup_Name, contactmanager_groups.type AS CMGroup_Type, userman_users.username AS CMGroup_Owner_Username, userman_users.default_extension AS CMGroup_Owner_Extn, contactmanager_group_entries.displayname AS CMEntry_DisplayName, userman_users_1.username AS CMEntry_Internal_Username, contactmanager_entry_numbers.type AS CMNum_Type, contactmanager_entry_numbers.number AS CMNum_Number, contactmanager_entry_numbers.e164 AS CMNum_NumberE164 FROM ((contactmanager_entry_numbers RIGHT JOIN (contactmanager_group_entries RIGHT JOIN contactmanager_groups ON contactmanager_group_entries.groupid = contactmanager_groups.id) ON contactmanager_entry_numbers.entryid = contactmanager_group_entries.id) LEFT JOIN (userman_users LEFT JOIN devices ON userman_users.default_extension = devices.id) ON contactmanager_groups.owner = userman_users.id) LEFT JOIN userman_users AS userman_users_1 ON contactmanager_group_entries.user = userman_users_1.id ORDER BY contactmanager_groups.name, contactmanager_group_entries.displayname limit 50;"

NB. The above SQL is limited to returning the 1st 50-rows for the purpose of demonstration.

The SQL statement extracts all the pertinent fields together in one DB call to allow Contacts to be extracted using a WHERE of choice (ie. by CM-GroupName, CM-GroupOwnerUsername or CM-GroupOwnerExtn). Consequently, I now have a single Remote PB Helper script that can return a properly formatted XML via a variety of passed Extract modes, which in-turn influences the WHERE clause.

The ‘ByGroupOwnerExtn’ mode of the helper script is specified within the basefile using passed parameter ‘line1Ext’, so that I can use a single common basefile URL to the script, but when built, the configured line key for each handset just shows the Private Contacts for the Linked User of each Extn (or an empty list if the Extn does have a linked user).

Initial testing seems to work a treat.

PS. The Helper script also has a ‘GetExtns’ mode which extracts an Extension list via the following alternate SQL that uses field aliases to match the CM-Group dataset. This enables the XML building loop of the same script to populate from either dataset;

mysql asterisk -e "SELECT 'Devices' AS CMGroup_Name, 'internal' AS CMGroup_Type, '' AS CMGroup_Owner_Username, '' AS CMGroup_Owner_Extn, devices.description AS CMEntry_DisplayName, userman_users.username AS CMEntry_Internal_Username, 'internal' AS CMNum_Type, devices.id AS CMNum_Number, '' AS CMNum_NumberE164 FROM (devices LEFT JOIN users ON devices.id = users.extension) LEFT JOIN userman_users ON devices.id = userman_users.default_extension WHERE ((devices.description NOT LIKE 'Zulu%') AND (devices.description NOT LIKE 'WebRTC%')) ORDER BY devices.description;"

NB. This filters out the WebRTC & Zulu entries via the WHERE clause.

All in all, a productive day so far.