locked
Inactive Users? RRS feed

  • Question

  • Im searching for a way to determine what users in OCS are inactive. Is it possible?

     

    Is there a script or some way to query OCS to find out who is not using there (enabled) account? Or time since last logon?

     

     

    Thanks

    Eric

     

    Friday, October 10, 2008 2:57 PM

Answers

  • Sorry for the delay...crazy week.  Run this query in SQL to return the last logon time.

     

    Code Snippet

    select hud.LastNewRegisterTime as "Last Logon",res.UserAtHost as "SIP Address" from rtcdyn.dbo.HomedUserDynamic hud join

    (Select ResourceId, UserAtHost from rtc.dbo.Resource

    group by ResourceId, UserAtHost)

    res

    on hud.OwnerId=res.ResourceId

    order by "Last Logon"

     

     

    This will return a list of users ordered from earliest logon time (starting with NULL) to latest.

     

    Saturday, October 11, 2008 8:25 PM
    Moderator

All replies

  • Presence is stored in a binary blob in the database but can be read using the appropriate OCS API call.  Availability of Online but Idle is returned in the range of 4500 to 5999 and availability of Busy but Idle is returned in the range of 7500 to 8999.

    Friday, October 10, 2008 5:57 PM
    Moderator
  •  

    thanks for your response. But im searching for something else.

     

    Im trying to determine users who have NEVER logged into OCS but are enabled for it. and/or users who have not logged into it in say 6months.

     

    is that possible?

     

    Friday, October 10, 2008 6:00 PM
  • Gotcha.  "Inactive" has a different meaning from a Communicator perspective.  In any case, just grab the output of the UpdateTime column from the ResourceDirectory table and cross reference the ids with the Resource table to identify the users.

    Friday, October 10, 2008 7:07 PM
    Moderator
  •  

    they are all showing up with an UpdateTime that is in the past. Would this be acurate? As I am in OCS currently as is many other users.

     

    Im looking at ResourceDirectory / UpdateTime

    Friday, October 10, 2008 7:25 PM
  • I'm sorry, I was clicking through too fast trying to multitask.  That is the last time that the user object was updated.  You may need an API call to read the last presence update.  I'll tinker around with this a little later to see what I can find.

    Friday, October 10, 2008 8:44 PM
    Moderator
  • Sorry for the delay...crazy week.  Run this query in SQL to return the last logon time.

     

    Code Snippet

    select hud.LastNewRegisterTime as "Last Logon",res.UserAtHost as "SIP Address" from rtcdyn.dbo.HomedUserDynamic hud join

    (Select ResourceId, UserAtHost from rtc.dbo.Resource

    group by ResourceId, UserAtHost)

    res

    on hud.OwnerId=res.ResourceId

    order by "Last Logon"

     

     

    This will return a list of users ordered from earliest logon time (starting with NULL) to latest.

     

    Saturday, October 11, 2008 8:25 PM
    Moderator
  •  

    Worked perfectly! Thanks alot!
    Saturday, October 11, 2008 9:11 PM
  • Great!  When you get a chance please mark the question as answered so that it helps others who may search for this later.

    Saturday, October 11, 2008 9:42 PM
    Moderator
  • Sorry for the delay...crazy week.  Run this query in SQL to return the last logon time.

    select hud.LastNewRegisterTime as "Last Logon",res.UserAtHost as "SIP Address" from rtcdyn.dbo.HomedUserDynamic hud join

    (

     

     

    Select ResourceId, UserAtHost from rtc.dbo.Resource group by ResourceId, UserAtHost)

    res

    order

     

     

    by "Last Logon"
    on hud.OwnerId=res.ResourceId

    This will return a list of users ordered from earliest logon time (starting with NULL) to latest.

     

     For OCS 2007 R2 this did not work for me. I had to use the change rtcdyn.dbo.HomedUserDynamic to rtcdyn.dbo.HomedResourceDynamic.
    Then it gave the info I needed.


    Wednesday, November 11, 2009 9:29 PM