[horde] which table holds the data of last_login?

[Anant Athavale]

----- Message from asa at isac.gov.in ---------
     Date: Thu, 10 Jan 2008 16:11:28 +0530
     From: Anant S Athavale <asa at isac.gov.in>
Reply-To: asa at isac.gov.in, anant_athavale at yahoo.com
  Subject: Re: [horde] which table holds the data of last_login?
       To: Otto Stolz <Otto.Stolz at uni-konstanz.de>
       Cc: horde at lists.horde.org


> ----- Message from Otto.Stolz at uni-konstanz.de ---------
>      Date: Thu, 10 Jan 2008 11:08:00 +0100
>      From: Otto Stolz <Otto.Stolz at uni-konstanz.de>
> Reply-To: Otto Stolz <Otto.Stolz at uni-konstanz.de>
>   Subject: Re: [horde] which table holds the data of last_login?
>        To: Anant Athavale <asa at isac.gov.in>
>        Cc: horde at lists.horde.org
>
>
>> An afterthought:
>>
>> I had proposed:
>>>   select from_unixtime(pref_value,"%Y-%m-%d") as "Last Login",
>>>          pref_uid                             as User
>>>   from horde_prefs
>>>   where pref_name="last_login"
>>>     and from_unixtime(pref_value,"%Y%m%d")<20071010
>>>   order by pref_value;
>>
>> The horde_prefs database contains only records of users
>> that have logged in at least once. So this DB query will
>> miss those users who have never logged in.
>>
>> To catch even those, you'd have to
>> - get a complete list from your users? database,
>> - remove from that list all users that have logged in
>>   during the last 3 months (cf. my 1st message in this
>>   thread),
>> - and voilà.
>> I cannot offer any code for this procedure, though.
>>
>> Best wishes,
>>   OS
>
> Thanks for your very quick response.  I tried out you mysql command.
> But, I find that, pref_value for pref_name->last_login contains even
> more information.  The value of pref_value looks like this:
> a:2:{s:4:"time";i:1199956603;s:4:"host";s:11:"10.21.3.117";}
>
> So, I need to take out 1199956603 from this entire value before giving
> it to from_unixtime function.  Is that right?  You have any simple
> solution to achieve this.
>
> Regards,
> Anant.
>
>
>
>
>
> ----- End message from Otto.Stolz at uni-konstanz.de -----
>
>
>

Hi,

I myself found the following mysql syntax to get it.  Can you confirm,  
this is the simplest solution.

select substring_index(substring_index(pref_value,';',2),':',-1) from  
horde_prefs where pref_name='last_login';  (to get unixtimestamp  
alone) to be passed to from_unixtime.

Regards,

Anant Athavale.