I wanted to do this and spent a WHILE figuring out how, so I thought
i'd share. I wanted an easy way to spit my CUCM dialplan out into an
easily readable file so I could stuff it into excel and look back on
it later. Also, SQL is WAY quicker than waiting for pages to load. if
you remember any of the below, it could ptentially help you
troubleshoot your dialplan faster than loading pages.
Use the SQL query below to spit out all of your translation patterns,
calling or called transformations, or your entire dialplan. it will
show you the pattern, Partition, Discard digits, Mask and prefix
digits.
modify the line
AND TKPU.moniker LIKE "%CALLING%" \
...to match the type of DN or pattern you'd like to see, or remove it
completely to see your whole dialplan. if you want to see what the
available values are, enter the command
admin: run sql SELECT * FROM typepatternusage
To use the SQL script,
Get into the admin CLI, and cut and paste one of the below statements in:
run sql \
SELECT first 2 NP.dnorpattern, RP.name, \
CASE \
WHEN NP.fkdigitdiscardinstruction is NULL then (SELECT
fkdigitdiscardinstruction from numplan NP2 where NP.pkid = NP2.pkid) \
WHEN NP.fkdigitdiscardinstruction is not NULL then (SELECT name
from digitdiscardinstruction DDI where DDI.pkid =
NP.fkdigitdiscardinstruction) \
END DiscardDigits, NP.prefixdigitsout,
NP.calledpartytransformationmask, NP.callingpartytransformationmask,
TKPU.moniker \
FROM numplan NP, routepartition RP, typepatternusage TKPU\
WHERE NP.fkroutepartition = RP.pkid \
AND NP.tkpatternusage = TKPU.enum \
AND TKPU.moniker LIKE "%CALLED%" \
ORDER BY TKPU.moniker
run sql \
SELECT first 2 NP.dnorpattern, RP.name, \
CASE \
WHEN NP.fkdigitdiscardinstruction is NULL then (SELECT
fkdigitdiscardinstruction from numplan NP2 where NP.pkid = NP2.pkid) \
WHEN NP.fkdigitdiscardinstruction is not NULL then (SELECT name
from digitdiscardinstruction DDI where DDI.pkid =
NP.fkdigitdiscardinstruction) \
END DiscardDigits, NP.prefixdigitsout,
NP.calledpartytransformationmask, NP.callingpartytransformationmask,
TKPU.moniker \
FROM numplan NP, routepartition RP, typepatternusage TKPU\
WHERE NP.fkroutepartition = RP.pkid \
AND NP.tkpatternusage = TKPU.enum \
AND TKPU.moniker LIKE "%CALLING%" \
ORDER BY TKPU.moniker
....You shoudl get something that looks like this:
admin:run sql \
SELECT first 2 NP.dnorpattern, RP.name, \
CASE \
WHEN NP.fkdigitdiscardinstruction is NULL then (SELECT
fkdigitdiscardinstruction from numplan NP2 where NP.pkid = NP2.pkid) \
WHEN NP.fkdigitdiscardinstruction is not NULL then (SELECT name
from digitdiscardinstruction DDI where DDI.pkid =
NP.fkdigitdiscardinstruction) \
END DiscardDigits, NP.prefixdigitsout,
NP.calledpartytransformationmask, NP.callingpartytransformationmask,
TKPU.moniker \
FROM numplan NP, routepartition RP, typepatternusage TKPU\
WHERE NP.fkroutepartition = RP.pkid \
AND NP.tkpatternusage = TKPU.enum \
AND TKPU.moniker LIKE "%CALLED%" \
ORDER BY TKPU.moniker
dnorpattern name discarddigits prefixdigitsout
calledpartytransformationmask callingpartytransformationmask moniker
============== ====== ============= ===============
============================= ==============================
==========================================
91516.678XXXX HQ-TFM PreDot
NULL
PATTERN_TRANSFORMATION_CALLED_PARTY_NUMBER
\+1516.678XXXX HQ-TFM PreDot
NULL
PATTERN_TRANSFORMATION_CALLED_PARTY_NUMBER
admin:
admin:
admin:
admin:
admin:run sql \
SELECT first 2 NP.dnorpattern, RP.name, \
CASE \
WHEN NP.fkdigitdiscardinstruction is NULL then (SELECT
fkdigitdiscardinstruction from numplan NP2 where NP.pkid = NP2.pkid) \
WHEN NP.fkdigitdiscardinstruction is not NULL then (SELECT name
from digitdiscardinstruction DDI where DDI.pkid =
NP.fkdigitdiscardinstruction) \
END DiscardDigits, NP.prefixdigitsout,
NP.calledpartytransformationmask, NP.callingpartytransformationmask,
TKPU.moniker \
FROM numplan NP, routepartition RP, typepatternusage TKPU\
WHERE NP.fkroutepartition = RP.pkid \
AND NP.tkpatternusage = TKPU.enum \
AND TKPU.moniker LIKE "%CALLING%" \
ORDER BY TKPU.moniker
dnorpattern name discarddigits prefixdigitsout
calledpartytransformationmask callingpartytransformationmask moniker
=========== ===== ============= ===============
============================= ==============================
========================================
3XXX B-TFM NULL NULL NULL
XXXXXXX
PATTERN_TRANSLATION_CALLING_PARTY_NUMBER
4XXX B-TFM NULL NULL NULL
XXXXXXXXXXX
PATTERN_TRANSLATION_CALLING_PARTY_NUMBER
admin:
....Hope this was helpful!
-Peter
_______________________________________________
For more information regarding industry leading CCIE Lab training, please visit
www.ipexpert.com
