[cisco-voip] Inactive Device Query?

Wed Sep 13 13:58:21 EDT 2017

The problem with CDR searches is that unless you script/program something,
you cannot ask SQL (that I know of) for something that's not there.  I.e.,
Show me all the records in the table you don't have a record for in the
last 90 days.

So then, you would either need to do one of three things, in my estimation:

1) Search SQL one device at a time

Example:

run sql car select datetimestamporigination, origdevicename, destdevicename
from tbl_billing_data where date(datetimestamporigination) >= today-90 and
(origdevicename = "SEP000000000001" or destdevicename = "SEP000000000001")

2) Dump all CDR records for phones in the last 90 days, then do a vlookup
in excel on an export of your devices to see whose in the CDR report

Example:

*CDR*
run sql car select datetimestamporigination, origdevicename, destdevicename
from tbl_billing_data where date(datetimestamporigination) >= today-90 and
(origdevicename like 'SEP%' or destdevicename like 'SEP%')

*Devices*
run sql select name from device where name like "SEP%"

*Excel*
Assuming you pasted CDR in columns A - C, skipped column D, and pasted
Devices in column E, then used this formula in column F (headers on row 1):

=IFNA(VLOOKUP($E2, $B:$B, 1, FALSE), VLOOKUP($E2, $C:$C, 1, FALSE))

The result in column F will either be the device name, if it was involved
in a call in the last 90 days, or #N/A if it wasn't.

3) Write some code that does all the fancy bits for you

Sorry, no example.  :(

On Tue, Sep 12, 2017 at 11:46 AM Ben Amick <bamick at humanarc.com> wrote:

> I assume it essentaly just goes back and finds the last record in the
> table relevant to that extension/device in the CDR records, right?
>
>
>
> What’s the ballpark cost of that Variphy look like? We already have a
> number of custom SQL reports we export our CDR into so I don’t think I’d be
> able to get it for my org unless I were to front it myself or find a
> considerable ROI to pull out of it.
>
>
>
> Ben Amick
>
> Unified Communications Analyst
>
>
>
> *From:* Charles Goldsmith [mailto:wokka at justfamily.org]
> *Sent:* Tuesday, September 12, 2017 12:39 PM
> *To:* Ben Amick <bamick at HumanArc.com>
> *Cc:* cisco-voip at puck.nether.net
> *Subject:* Re: [cisco-voip] Inactive Device Query?
>
>
>
> Ben, you can glean this info via CDR if you archive those, but not an easy
> task.  Variphy has a report to pull this type of info from CDR and is quite
> handy.
>
>
>
> On Tue, Sep 12, 2017 at 11:31 AM, Ben Amick <bamick at humanarc.com> wrote:
>
> So is there any solution for pre-12 instances for this scenario?
> Specifically, 9.X.
>
>
>
> (That said, there’s some nifty features in v12, notably the message
> archiving enforcement that will be very helpful to my org)
>
>
>
> Ben Amick
>
> Unified Communications Analyst
>
>
>
> *From:* Matthew Loraditch [mailto:MLoraditch at heliontechnologies.com]
> *Sent:* Tuesday, September 12, 2017 12:07 PM
> *To:* Ben Amick <bamick at HumanArc.com>; cisco-voip at puck.nether.net
> *Subject:* RE: Inactive Device Query?
>
>
>
>
> https://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cucm/rel_notes/12_0_1/cucm_b_release-notes-for-cucm-imp-1201/cucm_b_release-notes-for-cucm-imp-1201_chapter_00.html
>
>
>
> Scroll to this section:
>
> New Columns to Manage Devices Efficiently
>
>
>
>
>
> Matthew G. Loraditch – CCNP-Voice, CCNA-R&S, CCDA
> Network Engineer
> Direct Voice: 443.541.1518 <(443)%20541-1518>
> [image: image001.jpg]
>
> Facebook
> <http://cp.mcafee.com/d/k-Kr3x8Sy_tZdBdxyVKVJ55BZBcsehd79J55BZBcsY-Orhhpvuv7ffK6Qkn3hOqerTKzsSgRmlyEa9JGX3oSVsSjrlS6NJOVIsOVtUwqen-LObMUsCOUVRXBQSm4uphu76zBdN_G8FHnjlKC_OEuvkzaT0QSyrvdTVdWUVBAQsIfTdTdAVPmEBC5etSTAaRaYLxfPVlxfX4Okvte9DCRjWNR2kX4MkjY-loj-Ne5jWApvjBPqqb9EVjdwIqid40r3rjApYQg1TCpmYQg0eTYiN8SCUrW7EB_X22fW0z>
> | Twitter
> <http://cp.mcafee.com/d/FZsS720wrhvK-COCMNsTsSyyO-OCe78CzASyyO-OCeuvpdEEILLfzDDT3qabxEVd7dXThKr8qHaNk54SRtxIrsKr9JGX3oSVsSepsKYgd7b_nV5UsejpssWZOWrb2fcEL3zhOCU_R4kRHFGTjvVkffGhBrwqrjdLCXYCZssOOqem7XCXCOsVHkiP9C9jDYunMb-vaI0jZypJk-ItgBeNc54_fBm4_Ijxk-F6nQVsSCyOqekPob6Azh06MSQV6vd40tVClLd403J_4IidFK6-LcV0kM-0aB>
> | LinkedIn
> <http://cp.mcafee.com/d/FZsS92hJ5-XWrar35PtPqabbXaoUsyqejqabbXaoVVZASyyO-Y-euvsdEEK6zAQsTLt6VIxGIH5gkjrlS6NJOVICSHIdzrBPoVBOXN0QsLZvAnxMVdBNPHTbFII8YOyYed7arz_khjmKCHtd_BgY-F6lK1FJAS-rLOrRNPb9EVovKrKr9PCJhbcasva1nQ9gVv5RJwmMDVYGMbP-Ne5jWApvgBe9kWtlxm6ChkDlnPqFZoWxatyoa9-vaI9_oD2FZicLFOVJd5AQsFCMmd96y0dxJFOc-q80XPcHuq807r-9oArjsdARtndHh-RNMW>
> | G+
> <http://cp.mcafee.com/d/avndy0w738OrhvK-COCMNsTsSyyO-OCe78CzASyyO-OCeuvpdEEILLfzDDT3qabxEVd7dXThKr8qHaNk54SRtxIrsKr9JGX3oSVsSepsKYgd7b_nV5UsejpssWZOWrb2fcEL3zhOCU_R4kRHFGTjvVkffGhBrwqrodLCXYCZssOOqem7XCXCOsVHkiP5zSWshHkyv_bUQ1z3882_DOH2vS9MGvkzbWoJmAW6RjWNR2kX4MkjY-loj-Ne5jWApvjBPqqb9EVjdwIqid40r3rjApYQg1TCpmYQg0eTYiN8SCUrf6Dk>
>
>
>
> *From:* cisco-voip [mailto:cisco-voip-bounces at puck.nether.net
> <cisco-voip-bounces at puck.nether.net>] *On Behalf Of *Ben Amick
> *Sent:* Tuesday, September 12, 2017 11:56 AM
> *To:* cisco-voip at puck.nether.net
> *Subject:* [cisco-voip] Inactive Device Query?
>
>
>
> I haven’t seen it mentioned before, but I feel like it’s a common thing to
> go through an audit old phones in the environment, but other than looking
> through lists of terminated users and cross referencing active users, I
> can’t think of any good way to audit phones to see if they’re alive
> anymore, especially with softphones.
>
>
>
> In AD I would just pull a report with powershell of users/computers not
> logged in for >90 days, is there any comparable option in CUCM for inactive
> phones that you could audit against? Like last registration date or some
> such?
>
>
>
> Ben Amick
>
> Unified Communications Analyst
>
>
>
>
> Confidentiality Note: This message is intended for use only by the
> individual or entity to which it is addressed and may contain information
> that is privileged, confidential, and exempt from disclosure under
> applicable law. If the reader of this message is not the intended recipient
> or the employee or agent responsible for delivering the message to the
> intended recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited. If
> you have received this communication in error, please contact the sender
> immediately and destroy the material in its entirety, whether electronic or
> hard copy. Thank you
>
>
> Confidentiality Note: This message is intended for use only by the
> individual or entity to which it is addressed and may contain information
> that is privileged, confidential, and exempt from disclosure under
> applicable law. If the reader of this message is not the intended recipient
> or the employee or agent responsible for delivering the message to the
> intended recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited. If
> you have received this communication in error, please contact the sender
> immediately and destroy the material in its entirety, whether electronic or
> hard copy. Thank you
>
>
> _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip
> <http://cp.mcafee.com/d/2DRPoQ96QnXLFIIcLf6XCQkmnSkNMV4QsCQkmnSkNPPX9J55BZVYsY-Urhhsd79EVLuWdPp3lpmawECSHIdzrBPpdJnor6TbCSk4bL43hO_R-j78CzB5CXTnKnjjd7d-ohsjsKqeneEyyJtdmXb_axVZicHs3jq9JMTvAhPXWrXOarxKVIDeqR4INpKNnwqj-f0T1dnoovaAVgtHBFkJkKpH9oSoGlaW2x5KATtlAumU02rjojovdwLQzh0qmXiFqFsPmiNFtd40r3rjApYQg1TCpmYQg0eTYiN8SOqejqELBb0S7zGXm>
>
>
>
> Confidentiality Note: This message is intended for use only by the
> individual or entity to which it is addressed and may contain information
> that is privileged, confidential, and exempt from disclosure under
> applicable law. If the reader of this message is not the intended recipient
> or the employee or agent responsible for delivering the message to the
> intended recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited. If
> you have received this communication in error, please contact the sender
> immediately and destroy the material in its entirety, whether electronic or
> hard copy. Thank you _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20170913/3fab26d7/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 3985 bytes
Desc: not available
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20170913/3fab26d7/attachment.jpg>