Need to extract userid and Line 1 information from CUCM

[mdrudholm]

I'm trying to automate the extraction of userid, phone number, and device name using AXL.  The query I have returns all lines associated with a device and I need only the information associated with the phones' Line 1 (since that's the user's personal number in all cases).  The query I'm using is this:

select enduser.userid, numplan.dnorpattern, e164Mask, device.name from enduser inner join enduserdevicemap on enduser.pkid=enduserdevicemap.fkenduser inner join device on enduserdevicemap.fkdevice=device.pkid inner join devicenumplanmap on device.pkid=devicenumplanmap.fkdevice inner join numplan on devicenumplanmap.fknumplan=numplan.pkid

I either need the query to return only Line 1, or to return the line number information so my script can just pull out the Line 1 data.  I've tried using the "FIRST 1" syntax mentioned in the AXL cookbook but that just limits the output to the device's first entry in the table, not the actual Line 1 button.

[Aaron Harrison · Accepted solution]

Hi

You just need to specify numplanindex =1 in the devicenumplanmap table.

run sql select enduser.userid, numplan.dnorpattern, e164Mask, device.name from enduser inner join enduserdevicemap on enduser.pkid=enduserdevicemap.fkenduser inner join device on enduserdevicemap.fkdevice=device.pkid inner join devicenumplanmap on device.pkid=devicenumplanmap.fkdevice inner join numplan on devicenumplanmap.fknumplan=numplan.pkid  where devicenumplanmap.numplanindex = 1 and enduser.userid = 'gbcaxh06' group by userid,name,dnorpattern,e164mask

I've also added a group-by clause, as multiple links between user and dev (e.g. for CTI Controlled and EM device profiles) causes some duplication of results.

Regards

Aaron

[mdrudholm]

That appears to work.  I just had to remove the "and enduser.userid = 'gbcaxh06'".  I assume that was just residue from your testing and I want all records anyway.

Thank you.

[mdrudholm]

.

[mdrudholm]

It turns out that the Area Code information isn't reliably stored in the e164mask, which means I have to figure it the Area Code based on the line's Calling Search Space, but I don't see CSS in either the devicenumplanmap or numplan columns.  How would I include Line CSS in the select?

[Aaron Harrison]

Hi

There'll be an fk_xxx field in the device table that refers to callingsearchspace table for the dev-level CSS.

Also one in numplan (well, several, but one for calling, and lots for CFA/CFNB/Subscribe etc) also prefixed fk_ for the line level CSS.

Let me know if you can't find it and I'll look later when I'm near a CM.

Aaron

[mdrudholm]

Well, it turns out the only reliable place to retrive the full DN is in the e164 "Alternate Number" field, however, I can't seem to find it in the tables I've been querying.