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 < [email protected]> 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 <[email protected]> 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:[email protected]] >> *Sent:* Tuesday, September 12, 2017 12:39 PM >> *To:* Ben Amick <[email protected]> >> *Cc:* [email protected] >> *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 <[email protected]> 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:[email protected]] >> *Sent:* Tuesday, September 12, 2017 12:07 PM >> *To:* Ben Amick <[email protected]>; [email protected] >> *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:[email protected] >> <[email protected]>] *On Behalf Of *Ben Amick >> *Sent:* Tuesday, September 12, 2017 11:56 AM >> *To:* [email protected] >> *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 >> [email protected] >> 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 >> [email protected] >> https://puck.nether.net/mailman/listinfo/cisco-voip >> >
_______________________________________________ cisco-voip mailing list [email protected] https://puck.nether.net/mailman/listinfo/cisco-voip
