How to filter services used by users
Posted by Ondrej Vanek, Last modified by Ondrej Vanek on 11 December 2015 08:32 AM
To filter which users has activated or deactivated services in IceWarp Server Administration Console under Domains & Acounts- users - policies tab you can follow bellow suggested example of sql query in IceWarp integrated SQL Manager or tool command.
If you are using the default SQLite database or have your accounts on filesystem, you can use tool.exe (or *.sh) with this command:
tool --filter="(u_webmail=1)" display account *@domain.com U_Name
-this comand will list all users of specific domain with enabled webclient service in admin console>domain>user>policies services chapter
If you are using Mysql or Microsoft sql database you have to run following query in IceWarp integrated SQL Manager because these API variables are stored in column U_ServiceAccess as bits:
SELECT U_ID, U_Mailbox, (U_ServicesAccess & 8 ) AS U_IM FROM Users where (U_ServicesAccess & 8 )=0;
-this query will list all users with enabled Instant Messaging service in their settings, you can export the list as csv file.
To filter all users who has e.g. disabled service groupware in their account settings use following query:
SELECT U_ID, U_Mailbox, (U_ServicesAccess & 64 ) AS U_GW FROM Users where (U_ServicesAccess & 64 )<>0;
- if you want to filter all users with enabled webclient execute:
SELECT U_ID, U_Mailbox, (U_ServicesAccess & 16 ) AS U_WebMail FROM Users where (U_ServicesAccess & 16 )=0;
Here is the list of flags and values of ServieAccess column:
U_LocalDomain = $01; // 1 U_SMTP = $02; // 2 U_POPIMAP = $04; // 4 U_IM = $08; // 8 U_WebMail = $10; // 16 U_AS = $20; // 32 U_GW = $40; // 64 U_CR = $80; // 128