Query for non-used accounts

[vandehey]

Hi gang! Does anyone know of a query that I could run, that would poll for unused email accounts? We currently give accounts to all users, but I know that many don't use them. A query that would list open account, that have not been logged into in over a month would be great. Does anyone know how this could be done?

Thanks!

[bdial]

this is easier than that. log into the admin web interface. on the bottom of th left hand side you'll see a "Searches" area that should have "Inactive accounts (30 days)"

[cs_phillips]

Is there any way to export this report out to any other format? I did it last year, but can't remember how. All I remember was that it was insanely time-consuming and I guessed that I was reinventing the wheel.

[Rich Graves]

Utility script that I call "rootsearch":

#!/bin/sh
/opt/zimbra/openldap/bin/ldapsearch -LLL -x -D"`/opt/zimbra/bin/zmlocalconfig -s zimbra_ldap_userdn|awk '{print $3}'`" -w"`/opt/zimbra/bin/zmlocalconfig -s zimbra_ldap_password|awk '{print $3}'`" -H `/opt/zimbra/bin/zmlocalconfig ldap_url|awk '{print $3}'` $*

And then you can list usernames and other potentially useful information with

rootsearch '(zimbraLastLogonTimestamp<=20090528141404Z)' uid zimbraprefmailforwardingaddress zimbraaccountstatus uid

You can only do <= searches on timestamps. To do a > search, you need negation: (!(zimbraLastLogonTimestamp<=20090528141404Z))

[cs_phillips]

thanks for the quick repsonse. unfortunately, i'm not a big unix buy. i was able to run the first command in putty, but i'm thinking i have to use your code to create the rootsearch utility???
this looks like exactly what I need, but i inherited this system and i'm still not up to speed on the unix side of things. (windows fan-boy here)
any other information you could provide would be extremely helpful and more than appreciated.

[tiger2000]

i write a script several days ago for similiar purpose and i hope this could help your case.

Run script like this :
( to get users never login and login 30 days ago into /tmp/user.list file)
./myscript.sh 30

#!/bin/bash

if [ $# -ne 1 ]; then
   echo "Usage: $0 day"
   echo "Example: $0 30"
   exit 2
fi

domain="@example.com"
ACCT=/tmp/acct.txt
OUTPUT=/tmp/user.list

rm -rf $ACCT $OUTPUT

zmaccts | grep $domain | sed /wiki/d | sed /admin/d | sed /spam./d | sed /ham./d
 > $ACCT

today=`date '+%s'`
userinput=`echo "$1*24*60*60" | bc`

cat $ACCT |
while read line; do
   name=`echo $line | awk '{print $1}'`
   if [ `echo $line | awk '{print $5}'` = "never" ]; then 
      echo "$name never" >> $OUTPUT
          continue
   else
      lastlogin=`echo $line | awk '{print $5}' | awk -F/ '{print 2000+$3 $1 $2}'
`
   fi

  last=`date -d $lastlogin '+%s'`
  if [ `echo "$today - $last" | bc` -ge $userinput ]; then
     echo "$name $lastlogin" >> $OUTPUT
  fi

done

[cs_phillips]

Thanks. I'll try that out.

[msonen]

Thanks!

Do you have to run it as the zimbra user?

And I am assuming that the 30 is for the number of days back the person has logged in. So for querying only the people that have never logged in you would pass a 0?

[msonen]

Nevermind. Thanks I got it working.