[prev in list] [next in list] [prev in thread] [next in thread]
List: cisco-voip
Subject: Re: [cisco-voip] CUPS/CUPC last login?
From: Stephen Welsh <stephen.welsh () unifiedfx ! com>
Date: 2013-04-26 21:36:22
Message-ID: BD5EC6E8-D217-4D09-B7A2-B7BA153D9AAB () unifiedfx ! com
[Download RAW message or body]
Great suggestion Wes,
I did a bit of digging as this is something that would be useful to add to our \
product, found the following post:
http://www.routerdiscussions.com/viewtopic.php?f=4&t=1233
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:
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
I extended this query to include some additional information that may help as well as \
making the date/time output easier to read:
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
Here is a sample output from our lab:
userid lastaccess \
timechanged successfullogin \
credentialtype ================== ===================== ===================== \
===================== ============== rc \
2013-04-26 18:52:41.0 2012-11-28 13:06:52.0 2013-04-26 11:29:35.0 \
Password rc 2012-11-28 13:06:51.0 \
2012-11-28 13:06:51.0 -57 19 70 1 1 1 0 0 PIN 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 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 agent1 \
2013-04-26 12:23:50.0 2013-04-26 12:23:50.0 -57 19 70 1 1 1 0 0 \
Password agent1 2013-04-26 12:23:50.0 2013-04-26 \
12:23:50.0 -57 19 70 1 1 1 0 0 PIN
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 \
;)
Thanks
Stephen
On 26 Apr 2013, at 20:54, Wes Sisk <wsisk@cisco.com<mailto:wsisk@cisco.com>>
wrote:
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.
I'd start with the CUCM data dictionary and any tables related to user \
authentication. www.cisco.com/en/US/docs/voice_ip_comm/cucm/datadict/8_6_1/datadiction \
ary_861.pdf<http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/datadict/8_6_1/datadictionary_861.pdf>
maybe enduser
and if nothing else then query the hidden cdrtime field to see when this user was \
last modified in the database: admin:run sql select pkid,userid,cdrtime from enduser
pkid userid cdrtime
==================================== ====== ==========
8fc66074-1b33-a607-3366-9831b05fc909 joe 1366661094
Otherwise, I think one of the fields in the credential or credential dynamic table \
will give what you want.
-wes
On Apr 26, 2013, at 3:23 PM, Stephen Welsh \
<stephen.welsh@unifiedfx.com<mailto:stephen.welsh@unifiedfx.com>> wrote:
Hi Dave,
My bad missed the CUPC reference ;)
"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.
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:
show risdb query phone
It can be exported to a file for parsing, unfortunately you will have to do this for \
each active subscriber and collate the information.
You can also use the RISPort API, William Bell has a good article on using this API \
to gather the above information:
http://www.netcraftsmen.net/component/content/article/70-unified-communications/499-identifying-the-correct-device-status-in-a-risport-response.html
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.
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:
http://developer.cisco.com/web/cupapi/home
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. 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.
Thanks
Stephen
On 26 Apr 2013, at 19:29, Dave Wolgast \
<dwolgas1@rochester.rr.com<mailto:dwolgas1@rochester.rr.com>> wrote:
Thanks for the quick answer.
To make sure I have this correct, Cisco Unified Personal Communicator (CUPC) login \
data is stored in the CUCM database under the extensionmobilitydynamic table?
Thanks again!
On Fri, Apr 26, 2013 at 1:48 PM, Stephen Welsh \
<stephen.welsh@unifiedfx.com<mailto:stephen.welsh@unifiedfx.com>> wrote: Hi Dave,
The "extensionmobilitydynamic" table holds that information, run the following from \
the UCM command line:
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
An example looks like this:
userid logintime
================== =====================
stephen.welsh 2013-04-26 13:28:07.0
david.williams 2013-04-26 16:44:20.0
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.
However, I recommend you have a look at PhoneView from UnifiedFX \
(http://www.unifiedfx.com<http://www.unifiedfx.com/>), 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.
Thanks
Stephen Welsh
CTO
[X]
On 26 Apr 2013, at 17:38, Dave Wolgast \
<dwolgas1@rochester.rr.com<mailto:dwolgas1@rochester.rr.com>> wrote:
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?
--
Dave Wolgast
Livonia, NY
_______________________________________________
cisco-voip mailing list
cisco-voip@puck.nether.net<mailto:cisco-voip@puck.nether.net>
https://puck.nether.net/mailman/listinfo/cisco-voip
--
Dave Wolgast
Livonia, NY
_______________________________________________
cisco-voip mailing list
cisco-voip@puck.nether.net<mailto:cisco-voip@puck.nether.net>
https://puck.nether.net/mailman/listinfo/cisco-voip
[Attachment #3 (text/html)]
<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-communi \
cations/499-identifying-the-correct-device-status-in-a-risport-response.html">http://w \
ww.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>
_______________________________________________
cisco-voip mailing list
cisco-voip@puck.nether.net
https://puck.nether.net/mailman/listinfo/cisco-voip
--===============9193770755845585762==--
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic