<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
<div>Great suggestion Wes,</div>
<div><br>
</div>
<div>I did a bit of digging as this is something that would be useful to add to our product, found the following post:</div>
<div><br>
</div>
<div><a href="http://www.routerdiscussions.com/viewtopic.php?f=4&t=1233">http://www.routerdiscussions.com/viewtopic.php?f=4&t=1233</a></div>
<div><br>
</div>
<div>The above post actually covers most of your options about user activity from a UCM perspective, the query that relates to user login activity from the post is:</div>
<div><br>
</div>
<div>run sql select eu.userid,cd.lastsuccessfullogintime from enduser as eu inner join credential as c on c.fkenduser=eu.pkid inner join credentialdynamic as cd on cd.fkcredential=c.pkid order by eu.userid</div>
<div><br>
</div>
<div>I extended this query to include some additional information that may help as well as making the date/time output easier to read:</div>
<div><br>
</div>
<div>run sql SELECT FIRST 100 eu.userid, DBINFO('utc_to_datetime', cd.timelastaccessed) AS lastaccess, DBINFO('utc_to_datetime', c.timechanged) AS timechanged, DBINFO('utc_to_datetime',cd.lastsuccessfullogintime) AS successfullogin, tc.name AS credentialtype FROM credentialdynamic AS
cd INNER JOIN credential AS c ON c.pkid = cd.fkcredential INNER JOIN enduser AS eu ON eu.pkid = c.fkenduser INNER JOIN typecredential AS tc ON tc.enum = c.tkcredential</div>
<div><br>
</div>
<div>Here is a sample output from our lab:</div>
<div><br>
</div>
<div>
<div>userid lastaccess timechanged successfullogin credentialtype</div>
<div>================== ===================== ===================== ===================== ==============</div>
<div>rc 2013-04-26 18:52:41.0 2012-11-28 13:06:52.0 2013-04-26 11:29:35.0 Password</div>
<div>rc 2012-11-28 13:06:51.0 2012-11-28 13:06:51.0 -57 19 70 1 1 1 0 0 PIN</div>
<div>stephen.welsh 2013-04-26 22:07:42.0 2013-04-26 12:23:50.0 -57 19 70 1 1 1 0 0 Password</div>
<div>stephen.welsh 2013-04-26 12:23:50.0 2013-04-26 12:23:50.0 -57 19 70 1 1 1 0 0 PIN</div>
<div>agent1 2013-04-26 12:23:50.0 2013-04-26 12:23:50.0 -57 19 70 1 1 1 0 0 Password</div>
<div>agent1 2013-04-26 12:23:50.0 2013-04-26 12:23:50.0 -57 19 70 1 1 1 0 0 PIN</div>
<div><br>
</div>
</div>
<div>This output is from a clean install in our lab with minimal user activity, I suggest you do some tests with the above to see if it changes as you expect, as the CUPC device may just be registering there may be no authentication interaction recorded. It's
also possible that using LDAP authentication may effect how these tables are updated, hopefully you will find something useful here, if you do please let us know ;)</div>
<div><br>
</div>
<div>Thanks</div>
<div><br>
</div>
<div>Stephen</div>
<br>
<div>
<div>On 26 Apr 2013, at 20:54, Wes Sisk <<a href="mailto:wsisk@cisco.com">wsisk@cisco.com</a>></div>
<div> wrote:</div>
<br class="Apple-interchange-newline">
<blockquote type="cite">
<div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
I believe the user authentication tables in the database contain some information about the most recent user authentication time. There were some seemingly onerous requirements about this when we introduced user facing features.
<div><br>
</div>
<div>I'd start with the CUCM data dictionary and any tables related to user authentication.</div>
<div><a href="http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/datadict/8_6_1/datadictionary_861.pdf">www.cisco.com/en/US/docs/voice_ip_comm/cucm/datadict/8_6_1/datadictionary_861.pdf</a><br>
<br>
</div>
<div>maybe enduser</div>
<div><br>
</div>
<div>and if nothing else then query the hidden cdrtime field to see when this user was last modified in the database:</div>
<div>
<div>admin:run sql select pkid,userid,cdrtime from enduser</div>
<div>pkid userid cdrtime </div>
<div>==================================== ====== ========== </div>
<div>8fc66074-1b33-a607-3366-9831b05fc909 joe 1366661094 </div>
</div>
<div><br>
</div>
<div>Otherwise, I think one of the fields in the credential or credential dynamic table will give what you want.</div>
<div><br>
</div>
<div>-wes</div>
<div><br>
<div>
<div>On Apr 26, 2013, at 3:23 PM, Stephen Welsh <<a href="mailto:stephen.welsh@unifiedfx.com">stephen.welsh@unifiedfx.com</a>> wrote:</div>
<br class="Apple-interchange-newline">
<div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
Hi Dave,
<div><br>
</div>
<div>My bad missed the CUPC reference ;)</div>
<div><br>
</div>
<div>"extensionmobilitydynamic" will not apply in this case as the user is not logging in into the device, what you need is when CUPC is online and registered.</div>
<div><br>
</div>
<div>The closet thing you can do is use the Realtime Information Service data to obtain the last registration time, this can be queried using the following command form UCM CLI:</div>
<div><br>
</div>
<div>show risdb query phone</div>
<div><br>
</div>
<div>It can be exported to a file for parsing, unfortunately you will have to do this for each active subscriber and collate the information.</div>
<div><br>
</div>
<div>You can also use the RISPort API, William Bell has a good article on using this API to gather the above information:</div>
<div><br>
</div>
<div><a href="http://www.netcraftsmen.net/component/content/article/70-unified-communications/499-identifying-the-correct-device-status-in-a-risport-response.html">http://www.netcraftsmen.net/component/content/article/70-unified-communications/499-identifying-the-correct-device-status-in-a-risport-response.html</a></div>
<div><br>
</div>
<div>Our product (PhoneView) also gathers RISPort information and includes a "timestamp" field in the export that is the latest date/time from RISPort, this in combination with the Status of the phone would allow you to get the registration time of all devices.
However at this point in time we do not support CUPC as there is limited functionality for remote management of this soft endpoint. We will be adding support for the new DX650 so we could look at adding CUPC too.</div>
<div><br>
</div>
<div>This is obviously based on querying CUCM, there may be more relevant/valuable information directly from CUPS, details on the various API's for CUPS can be found here:</div>
<div><br>
</div>
<div><a href="http://developer.cisco.com/web/cupapi/home">http://developer.cisco.com/web/cupapi/home</a></div>
<div><br>
</div>
<div>If you could share more about your requirement for tracking CUPC I may be able to provide more relevant feedback on getting the right information etc.</div>
<div>Given that you are referring to a presence enabled client using the presence information may provide a much richer dataset, for example keeping track of users available/busy state etc.</div>
<div><br>
</div>
<div>Thanks</div>
<div><br>
</div>
<div>Stephen</div>
<div><br>
<div>
<div>On 26 Apr 2013, at 19:29, Dave Wolgast <<a href="mailto:dwolgas1@rochester.rr.com">dwolgas1@rochester.rr.com</a>></div>
<div> wrote:</div>
<br class="Apple-interchange-newline">
<blockquote type="cite">
<div dir="ltr">Thanks for the quick answer.
<div><br>
</div>
<div style="">To make sure I have this correct, Cisco Unified Personal Communicator (CUPC) login data is stored in the CUCM database under the extensionmobilitydynamic table?</div>
<div style=""><br>
</div>
<div style="">Thanks again!</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Fri, Apr 26, 2013 at 1:48 PM, Stephen Welsh <span dir="ltr">
<<a href="mailto:stephen.welsh@unifiedfx.com" target="_blank">stephen.welsh@unifiedfx.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0px 0px 0px 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; position: static; z-index: auto; ">
<div style="word-wrap:break-word">
<div>Hi Dave,</div>
<div><br>
</div>
<div>The "extensionmobilitydynamic" table holds that information, run the following from the UCM command line:</div>
<div><br>
</div>
<div>run sql SELECT eu.userid, DBINFO('utc_to_datetime', ed.logintime) AS logintime FROM extensionmobilitydynamic AS ed JOIN enduser AS eu ON ed.fkenduser = eu.pkid</div>
<div><br>
</div>
<div>An example looks like this:</div>
<div><br>
</div>
<div>
<div>userid<span style="white-space:pre-wrap"> </span>logintime</div>
<div>==================<span style="white-space:pre-wrap"> </span>=====================</div>
<div>stephen.welsh<span style="white-space:pre-wrap"> </span>2013-04-26 13:28:07.0</div>
<div>david.williams<span style="white-space:pre-wrap"> </span><span style="white-space:pre-wrap"></span>2013-04-26 16:44:20.0</div>
</div>
<div><br>
</div>
<div>If you need to gather and export all that information, you will probably need to use something that interfaces with the AXL API on UCM, there is an AXL Plugin that has some sample code/application that you should be able to use.</div>
<div><br>
</div>
<div>However, I recommend you have a look at <b>PhoneView</b> from UnifiedFX (<a href="http://www.unifiedfx.com/" target="_blank">http://www.unifiedfx.com</a>), one of it's many features is the ability to gather key information about each phone and it's logged
in user including the above information. This can be exported to a spreadsheet for further investigation or reporting.</div>
<div><br>
</div>
<div>
<div>Thanks</div>
<div><br>
<div>
<div>Stephen Welsh</div>
<div>CTO</div>
<br>
<img height="16" width="100"></div>
</div>
</div>
<div><br>
</div>
<br>
<div>
<div>On 26 Apr 2013, at 17:38, Dave Wolgast <<a href="mailto:dwolgas1@rochester.rr.com" target="_blank">dwolgas1@rochester.rr.com</a>></div>
<div> wrote:</div>
<br>
<blockquote type="cite">
<div>
<div class="h5">
<div dir="ltr">Is there a query that can be run on either CUCM or the CUPS server that will list users with their last login time/date?
<div>
<div><br>
</div>
-- <br>
Dave Wolgast<br>
Livonia, NY </div>
</div>
</div>
</div>
_______________________________________________<br>
cisco-voip mailing list<br>
<a href="mailto:cisco-voip@puck.nether.net" target="_blank">cisco-voip@puck.nether.net</a><br>
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip" target="_blank">https://puck.nether.net/mailman/listinfo/cisco-voip<br clear="all">
<div><br>
</div>
-- <br>
Dave Wolgast<br>
Livonia, NY </a></blockquote>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
_______________________________________________<br>
cisco-voip mailing list<br>
<a href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</a><br>
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip">https://puck.nether.net/mailman/listinfo/cisco-voip</a><br>
</div>
<br>
</div>
</div>
</blockquote>
</div>
<br>
</body>
</html>