cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
459
Views
1
Helpful
4
Replies

Query that would show shared lines with devices

Jay Schulze
Level 1
Level 1

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

4 Replies 4

Hi Jay,

Do you really need a sql query?

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

Please let me know.

 

Regards

 

Carlo

 

Please rate all helpful posts "The more you help the more you learn"

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.

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

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.