[cisco-voip] Inactive Device Query?

Wed Sep 13 14:16:16 EDT 2017

Anthony, from my understanding, that's exactly what Variphy is doing, doing
database correlation on the collected CDR records.  Variphy pulls in all
CDR records into a mysql database and runs queries locally vs doing any
queries back to CUCM for this info.

Registered vs Unregistered info is good, but the info to gain when a phone
was last used or how much usage it gets is a commonly requested feature
I've seen from customers.  They don't want an unused resource sitting on a
desk for months on end.

On Wed, Sep 13, 2017 at 12:58 PM, Anthony Holloway <
avholloway+cisco-voip at gmail.com> wrote:

> 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/18e4408a/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/18e4408a/attachment.jpg>