Complex Queries previously used

See Wiki page Database queries and commands.

Run the following command -

SELECT source_ip, count(source_ip) as total from log.msg_info group by source_ip order by total desc limit 10;

It is possible to perform this query for a specific sender, subject or time frame by changing the query accordingly

Run the following command -

psql secure postgres -c 'COPY (#SQL Query#) TO STDOUT WITH CSV HEADER' &> users.csv

The CSV file will be created in the Root folder

Run the following command -

psql secure postgres -c 'COPY (select o.oname,w.foreign_address from pineapp.objects as o left join policy.personal_white_list_rules on oid=victim_id left join policy.white_list_rules as w on id=rule_id) TO STDOUT WITH CSV HEADER' &> w_user_rules.csv

The CSV file will be created in the Root folder

Run the following command after changing necessary attributes -

UPDATE pineapp."userDetails" set send_spam_report=1 where uid in (SELECT uid from pineapp."userAlias" LEFT JOIN pineapp."userDetails" using (uid) LEFT JOIN pineapp.objects on (uid=oid) where deleted='f' and email ilike <Email Mask>);

Run the following command -

SELECT oid, oname, dn, description from pineapp.objects inner join pineapp.perm_template_list using(tid) where isgroup=0 and deleted=false order by description asc;

Run the following command -

SELECT foreign_address from policy.white_list_rules where id in (SELECT rule_id from policy.personal_white_list_rules inner join pineapp.objects on (victim_id=oid) where source=1);

Run the following command -

SELECT email from pineapp."userAlias" group by email having count(email)>1;

Run the following command -

SELECT * from pineapp.objects where oid in (select uid from pineapp."userAlias" where email in (SELECT email from pineapp."userAlias" group by email having count(email)>1));

Run the following command -

SELECT * FROM log.delivery as d LEFT JOIN log.delivery_info as di ON d.index = di.msgdbindex WHERE direction = 2 AND respond_desc not ilike '%Remote_host_said:_250%' order by di.start_time desc

Change Direction value to '1' if you wish to generate the log for Outgoing traffic

Run the following command -

SELECT victim_id, min(oname) as user_name, count(*) as rules_num from #Policy-Rules-Table# left join pineapp.objects on victim_id = oid group by victim_id order by rules_num DESC;

Run the following command -

SELECT d.domain,di.destination FROM config.local_domains as d LEFT JOIN config.domain_details as di ON d.id = di.id order by d.domain;

• To open the database prompt, type psql secure postgres
• To display a specific number of rows, use limit # (where # stands for number of rows)
• To search for a specific field content, use where X ilike 'Y' (where X stands for field name and Y stands for content)
• If the field content is unclear and a wildcard is needed, add % before and after the string (where X ilike '%Y%')
• To search for a numerical value, use = (where uid=3)
• All commands are case sensitive
• Remember to always end the command with ;

SELECT * from pineapp.mailboxes …;

 oid  |       mailbox       | quota | forvard |    forvard_to     | forvard_save | vacation | vacation_subj | vacation_message | default_box
------+---------------------+-------+---------+-------------------+--------------+----------+---------------+------------------+-------------
      |                     |       |         |                   |              |          |               |                  |            

oid - Corresponds with 'oid' from pineapp.objects table and to 'victim_id' in 'policy' tables


SELECT * from pineapp.objects …;

 oid | isgroup |         oname         | tid | ishidden | dn |            ldap_ogid             | ldap_sync | deleted | source
-----+---------+-----------------------+-----+----------+----+----------------------------------+-----------+---------+--------
     |         |                       |     |          |    |                                  |           |         |

oid - Corresponds with 'oid' from pineapp.mailboxes table and to 'victim_id' in 'policy' tables

tid - Permission Set:

• tid=-1 - None
• tid=0 - Default
• tid=1 - Manager
• tid=2 - Quarantine Manager
• tid=3 - Network Manager
• tid=4 - Mail & System
• tid=5 - Personal Quarantine Manager
• tid=6 - Personal Spam Manager
• tid=7 - Domain Quarantine Manager
• tid=8 - Domain Spam Manager
• tid=9 - Read-Only

SELECT * from pineapp.“userAlias” …;

 uid |         email         | def
-----+-----------------------+-----
     |                       |  

uid - Corresponds with 'uid' from pineapp.“userDetails” table


SELECT * from pineapp.“userDetails” …;

 uid |        fullName         | dept | notes |        loginName        |             password             | send_spam_report | q_priority
-----+-------------------------+------+-------+-------------------------+----------------------------------+------------------+------------
     |                         |      |       |                         |                                  |                  |

uid - Corresponds with 'uid' from pineapp.“userAlias” table

policy.personal_attachment_rules      policy.white_list_rules                 policy.domain_white_list_rules 
policy.personal_black_list_rules      policy.user_strip_content_rules         policy.user_black_list_rules
policy.general_rules                  policy.personal_general_rules           policy.domain_strip_content_rules  
policy.personal_kvm_rules             policy.user_white_list_rules            policy.user_attachment_rules
policy.global_attachment_rules        policy.personal_spam_rules              policy.domain_spam_rules
policy.attachment_rules               policy.global_black_list_rules          policy.group_spam_rules    
policy.global_general_rules           policy.user_spam_rules                  policy.domain_kvm_rules      
policy.black_list_rules               policy.global_kvm_rules                 policy.group_kvm_rules 
policy.global_spam_rules              policy.personal_white_list_rules        policy.domain_general_rules    
policy.global_strip_content_rules     policy.personal_strip_content_rules     policy.group_general_rules  
policy.global_white_list_rules        policy.user_kvm_rules                   policy.domain_black_list_rules 
policy.group_attachment_rules         policy.user_general_rules               policy.group_black_list_rules  
policy.domain_attachment_rules     

SELECT * from policy.white_list_rules …;

 id | direction |  foreign_address  | action_id_type | action_id | forward_id | mirror_id | notify_id | enabled
----+-----------+-------------------+----------------+-----------+------------+-----------+-----------+---------
    |           |                   |                |           |            |           |           |        

SELECT * from policy.black_list_rules …;

 id | direction |   foreign_address    | action_id_type | action_id | forward_id | mirror_id | notify_id | enabled
----+-----------+----------------------+----------------+-----------+------------+-----------+-----------+---------
    |           |                      |                |           |            |           |           |        

SELECT * from policy.user / personal / domain / group global …;

 victim_id | rule_id
-----------+---------
           |        

victim_id - Corresponds with 'oid' from pineapp tables
rule_id - Corresponds with 'id' in the different policy tables

SELECT * from pineapp.notify_templates;

secure=# SELECT * from pineapp.notify_templates;
 id | name | sender_body  | sender_subject | recipient_body | recipient_subject | admin_body | admin_subject |
----+------+--------------+----------------+----------------+-------------------+------------+---------------+
    |      |              |                |                |                   |            |               |

Use Base64 Decoder tool (http://www.patshaping.de/projekte/kleinkram/base64.php) to translate the template name

SELECT * from policy.notify_actions where template_id=…;

id | mask | email | template_id
---+------+-------+-------------
   |      |       | 

SELECT * from policy.general_rules;

 id | direction | foreign_address | action_id_type | action_id | forward_id | mirror_id | notify_id | enabled | subject
----+-----------+-----------------+----------------+-----------+------------+-----------+-----------+---------+---------
    |           |                 |                |           |            |           |           |         |

How to select Domains white_list rules per cust id (mind the limit at the end).
select domain,foreign_address from config.local_domains as C INNER JOIN policy.domain_white_list_rules as A on (C.id=A.victim_id) INNER JOIN policy.white_list_rules as B on (a.rule_id=b.id) where C.cust_id=33 order by domain limit 5;**

ONE1Samba users with high Permission

manager

quarantine manager

network manager

mail & system

read only

company

domain quarantine manager

copy (select obj.tid, ua.email, obj.oname, ptl.description from pineapp.objects obj left join pineapp.perm_template_list ptl on obj.tid = ptl.tid left join pineapp.“userAlias” ua on obj.oid = ua.uid where obj.deleted = 'f' AND obj.tid > 0 AND obj.tid != 6 AND obj.tid != 5 AND obj.tid != 8 AND ua.def = 't' order by obj.tid) to '/tmp/Pineapp_users_High_Permission.csv' csv;