Is it possible to tell when a enduser was created in CUCM 7x?

Answered Question
Jul 16th, 2010

Hi,

I have a lot of end-users in our Cisco Unified Communication Manager 7.x installation, but many of them are leftovers from the older CUCM installation that we upgraded from.

Is there any way that I can see when a given end-user was created in the Communication Manager? (I have tried to make a complete export of all the users, but there were only the following attributes included:

FIRST NAME, MIDDLE NAME, LAST NAME, USER ID, PASSWORD, MANAGER USER ID, DEPARTMENT, PIN, DEFAULT PROFILE, USER LOCALE, TELEPHONE NUMBER, PRIMARY EXTENSION, ASSOCIATED PC, IPCC EXTENSION, MAIL ID, PRESENCE GROUP, SUBSCRIBE CALLING SEARCH SPACE, ALLOW CONTROL OF DEVICE FROM CTI, DIGEST CREDENTIALS, USER GROUP 1, USER GROUP 2, USER GROUP 3, USER GROUP 4, USER GROUP 5, USER GROUP 6, USER GROUP 7, USER GROUP 8, USER GROUP 9, USER GROUP 10, USER GROUP 11, USER GROUP 12, USER GROUP 13, USER GROUP 14, USER GROUP 15, USER GROUP 16, USER GROUP 17, USER GROUP 18, USER GROUP 19, USER GROUP 20, USER GROUP 21, USER GROUP 22, USER GROUP 23, USER GROUP 24, USER GROUP 25, USER GROUP 26, USER GROUP 27, USER GROUP 28, USER GROUP 29, USER GROUP 30, USER GROUP 31, USER GROUP 32, USER GROUP 33, CONTROLLED PROFILE 1, CONTROLLED PROFILE 2, CONTROLLED DEVICE 1, CONTROLLED DEVICE 2, CONTROLLED DEVICE 3, CONTROLLED DEVICE 4, CONTROLLED DEVICE 5, CONTROLLED DEVICE 6, CONTROLLED DEVICE 7, CONTROLLED DEVICE 8, CONTROLLED DEVICE 9, CONTROLLED DEVICE 10, CONTROLLED DEVICE 11, CONTROLLED DEVICE 12, CONTROLLED DEVICE 13, CONTROLLED DEVICE 14, CONTROLLED DEVICE 15, CONTROLLED DEVICE 16, CONTROLLED DEVICE 17, CONTROLLED DEVICE 18, CONTROLLED DEVICE 19, CONTROLLED DEVICE 20, CONTROLLED DEVICE 21, CONTROLLED DEVICE 22, CONTROLLED DEVICE 23, CONTROLLED DEVICE 24, CONTROLLED DEVICE 25, CONTROLLED DEVICE 26, CONTROLLED DEVICE 27, CONTROLLED DEVICE 28, CONTROLLED DEVICE 29, CONTROLLED DEVICE 30, CONTROLLED DEVICE 31, CONTROLLED DEVICE 32, CONTROLLED DEVICE 33, CONTROLLED DEVICE 34, CONTROLLED DEVICE 35, CONTROLLED DEVICE 36, CONTROLLED DEVICE 37, CONTROLLED DEVICE 38, CONTROLLED DEVICE 39, CONTROLLED DEVICE 40, CONTROLLED DEVICE 41, CONTROLLED DEVICE 42, CONTROLLED DEVICE 43, CONTROLLED DEVICE 44, CONTROLLED DEVICE 45, CONTROLLED DEVICE 46, CONTROLLED DEVICE 47, CONTROLLED DEVICE 48, CONTROLLED DEVICE 49, CONTROLLED DEVICE 50, CONTROLLED DEVICE 51, CONTROLLED DEVICE 52, CONTROLLED DEVICE 53, CONTROLLED DEVICE 54, CONTROLLED DEVICE 55, CONTROLLED DEVICE 56, CONTROLLED DEVICE 57, CONTROLLED DEVICE 58, CONTROLLED DEVICE 59, CONTROLLED DEVICE 60, CONTROLLED DEVICE 61, CONTROLLED DEVICE 62, CONTROLLED DEVICE 63, CONTROLLED DEVICE 64, CONTROLLED DEVICE 65, CONTROLLED DEVICE 66, CONTROLLED DEVICE 67, CONTROLLED DEVICE 68, CONTROLLED DEVICE 69, CONTROLLED DEVICE 70, CONTROLLED DEVICE 71, CONTROLLED DEVICE 72, CONTROLLED DEVICE 73, CONTROLLED DEVICE 74, CONTROLLED DEVICE 75, CONTROLLED DEVICE 76, CONTROLLED DEVICE 77, CONTROLLED DEVICE 78, CONTROLLED DEVICE 79, CONTROLLED DEVICE 80, CONTROLLED DEVICE 81, CONTROLLED DEVICE 82, CONTROLLED DEVICE 83, CONTROLLED DEVICE 84, CONTROLLED DEVICE 85, CONTROLLED DEVICE 86, CONTROLLED DEVICE 87, CONTROLLED DEVICE 88, CONTROLLED DEVICE 89, CONTROLLED DEVICE 90, CONTROLLED DEVICE 91, CONTROLLED DEVICE 92, CONTROLLED DEVICE 93, CONTROLLED DEVICE 94, CONTROLLED DEVICE 95, CONTROLLED DEVICE 96, CONTROLLED DEVICE 97, CONTROLLED DEVICE 98, CONTROLLED DEVICE 99, CONTROLLED DEVICE 100, CONTROLLED DEVICE 101, CONTROLLED DEVICE 102, CONTROLLED DEVICE 103, CONTROLLED DEVICE 104, CONTROLLED DEVICE 105, CONTROLLED DEVICE 106, CONTROLLED DEVICE 107, CONTROLLED DEVICE 108, CONTROLLED DEVICE 109, CONTROLLED DEVICE 110, CONTROLLED DEVICE 111, CONTROLLED DEVICE 112, CONTROLLED DEVICE 113, CONTROLLED DEVICE 114, CONTROLLED DEVICE 115, CONTROLLED DEVICE 116, CONTROLLED DEVICE 117, CONTROLLED DEVICE 118, CONTROLLED DEVICE 119, CONTROLLED DEVICE 120, CONTROLLED DEVICE 121, CONTROLLED DEVICE 122, CONTROLLED DEVICE 123, CONTROLLED DEVICE 124, PASSWORD LOCKED BY ADMIN, PASSWORD CANT CHANGE, PASSWORD MUST CHANGE AT NEXT LOGIN, PASSWORD DOES NOT EXPIRE, PASSWORD AUTHENTICATION RULE, PIN LOCKED BY ADMIN, PIN CANT CHANGE, PIN MUST CHANGE AT NEXT LOGIN, PIN DOES NOT EXPIRE, PIN AUTHENTICATION RULE, ENABLE MOBILITY, ENABLE MOBILE VOICE ACCESS, REMOTE DESTINATION LIMIT, MAXIMUM WAIT TIME FOR DESK PICKUP, PASSWORD IMSINFO, PIN IMSINFO, USER PKID, PRIMARY USER DEVICE


Creation date is not mentioned.

Can you help me?

Kind regards,

Carl-Marius

I have this problem too.
0 votes
Correct Answer by Aaron Harrison about 6 years 4 months ago

Hi Carl

Not sure how close this is to what you want, but it's the best my brain can do today...

You can run a query like so from the CLI on CCM (do it via Putty, and log printable output to a text file):

run sql select userid,timechanged,timelastaccessed from credential join enduser on credential.fkenduser = enduser.pkid join credentialdynamic on credentialdynamic.fkcredential = credential.pkid

Open the text file, edit out all the stuff before the tabbed data from the query above starts.

Then open that file in Excel as a text file, and use the following formula in a new column (replacing A1 with a reference pointing to one of the cells containing the date fields in epoch form).

=(((A1-(6*3600))/86400)+25569)

If you then format the cells containing that formule as a date time cell (I do format cells/custom dd/mm/yyyy hh:mm in my horrid Excel 2007 version) you should get readable dates.

On my system, the output of 'timelastaccessed' appears to show the last login date of users (users at this customer NEVER change their passwords/PINs, but dates up till today are shown, and some dates appear as 0/1969 which suggests these have never logged in). Timechanged mainly shows dates when I've been on site batting in large numbers of users.

There seems to be some variation in what's available in the 'credentialdynamic' table, so if you have problems with the above post back, or if it's not quite correct post back the output of this command as something else might help:

run sql select first 1 * from credentialdynamic

Hope this helps!

Aaron

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
Loading.
Correct Answer
Aaron Harrison Fri, 07/16/2010 - 04:29

Hi Carl

Not sure how close this is to what you want, but it's the best my brain can do today...

You can run a query like so from the CLI on CCM (do it via Putty, and log printable output to a text file):

run sql select userid,timechanged,timelastaccessed from credential join enduser on credential.fkenduser = enduser.pkid join credentialdynamic on credentialdynamic.fkcredential = credential.pkid

Open the text file, edit out all the stuff before the tabbed data from the query above starts.

Then open that file in Excel as a text file, and use the following formula in a new column (replacing A1 with a reference pointing to one of the cells containing the date fields in epoch form).

=(((A1-(6*3600))/86400)+25569)

If you then format the cells containing that formule as a date time cell (I do format cells/custom dd/mm/yyyy hh:mm in my horrid Excel 2007 version) you should get readable dates.

On my system, the output of 'timelastaccessed' appears to show the last login date of users (users at this customer NEVER change their passwords/PINs, but dates up till today are shown, and some dates appear as 0/1969 which suggests these have never logged in). Timechanged mainly shows dates when I've been on site batting in large numbers of users.

There seems to be some variation in what's available in the 'credentialdynamic' table, so if you have problems with the above post back, or if it's not quite correct post back the output of this command as something else might help:

run sql select first 1 * from credentialdynamic

Hope this helps!

Aaron

Carl-Marius.Steensen Fri, 07/16/2010 - 04:54

Hi Aaron,

It works, but I can not get the output to a file, the command just prints the output on the screen, but it is displayed to fast so I can not break it, so I am only showed the last 40 users.

You do not know a way to get output directed to a file instead on the screen?

Kind regards,

Carl-Marius

Carl-Marius.Steensen Fri, 07/16/2010 - 05:00

Hi Aaron,

Never mind, I found the setting in the putty program, I just overlooked it the first time I went through the configurations

The setting was the "Change Settings..." on the Putty windows and click "Session" => "Logging" and choose a logging mode.

Thank you so very much for your help! It was precisely what I was looking for!

Kind regards,

Carl-Marius

almarcabato Wed, 04/27/2011 - 11:20

Hi Aaron,

I have the same case with Carl-Marius. I have CUCM 5x. However when I enter the sql command, I'm getting error,

admin:run sql select userid,timechanged,timelastaccessed from credential join enduser on credential.fkenduser = enduser.pkid join credentialdynamic on credentialdynamic.fkcredential = credential.pkid
The specified table (credential) is not in the database.
admin:

Thanks for your help in advance.

Regards,

Almar

Actions

This Discussion

Related Content