Query that would show shared lines with devices

[Jay Schulze]

I found this one which will give DNs which are shared. But doesn't list the device. I assume maybe would have to join it with d.name from device table but not exactly sure how.

run sql select numplan.dnorpattern, routepartition.name, count(*) from numplan, devicenumplanmap, routepartition where numplan.fkroutepartition = routepartition.pkid and numplan.pkid = devicenumplanmap.fknumplan group by 1, 2 having count(*) > 1 order by 1,2

[Carlo Poggiarelli]

Hi Jay,

Do you really need a sql query?

From Cisco Unified Reporting, you can simply search what you are looking for.

Please let me know.

Regards

Carlo

[Jay Schulze]

Hello Carlo,

Yes I'm aware of the report. Problem is it does the same thing. Only shows the extension. It doesn't show the device(s) it is attached to.

[Maren Mahoney]

Hah! Would you look at that? The report description even says "Provides a list of all phones with a shared line." which it totally doesn't. Just, as you point out, a list of the shared DNs and how many devices hold the DN.

I'm not a SQL expert and I don't know how to modify your query. There are a number of reference docs that might help if you Google for them. But I was thinking that you could do an end-run around your issue by making a list of all phones and their DNs, and then do some Excel magic to pull shared lines out.

run sql select d.name, d.description, n.dnorpattern as DN from device as d, numplan as n, devicenumplanmap as dnpm where dnpm.fkdevice = d.pkid and dnpm.fknumplan = n.pkid and d.tkclass = 1

It would be nice to have it cleaner, but this would get you the information you are looking for. 

Maren

[Jay Schulze]

Yeah it's actually where I got the count query from haha. If you look in tomcat logs you can see the query reports run. But yeah totally false advertisement on the report description.