# Auswertungen Helium Specs / Spezialfaelle ***********************

USE helidom;

# *** domains ********
SELECT dom, domainAliasCount, domainAliases FROM domain WHERE domainAliasCount;
SELECT dom, listCount FROM domain WHERE listCount;
SELECT dom, signatureCount FROM domain WHERE signatureCount;
SELECT d.dom, COUNT(a.dom) mailaliase FROM domain d JOIN mailalias a USING(dom) GROUP BY d.dom;
SELECT dom, whitelistAddrCount, whitelistAddresses, whitelistDomCount, whitelistDomains FROM domain WHERE whitelistAddrCount + whitelistDomCount > 0;
SELECT dom, contentFilterCount FROM domain WHERE contentFilterCount;
SELECT dom FROM domain WHERE spamCheckOverrideActive;
SELECT throttleSettings, COUNT(1) n, GROUP_CONCAT(dom) doms FROM domain GROUP BY throttleSettings;

# *** users *************
SELECT dom, COUNT(1) n, GROUP_CONCAT(username) admins FROM usr WHERE isDomainAdmin GROUP BY dom;
SELECT dom, COUNT(1) n, GROUP_CONCAT(username) admins FROM usr WHERE isListModerator GROUP BY dom;
SELECT cont.* FROM (SELECT email, CONCAT_WS('|', company, firstName, lastName, homeCity, homeCountry, messenger) contFields FROM usr) AS cont WHERE contFields > '';
SELECT maxMailboxSize, COUNT(1) n FROM usr GROUP BY maxMailboxSize;
SELECT email FROM usr WHERE autoCleanOverrideActive;
SELECT email FROM usr WHERE disableGreylisting;
SELECT email FROM usr WHERE spamCheckOverrideActive;
SELECT email FROM usr WHERE plusAddressingEnabled;
SELECT email, backupEmailAddress FROM usr WHERE backupEmailAddress > '';

# throttle (e.g. 'bandwidthPerHour=100|bouncesPerHour=50|messagesPerHour=500') *****
SELECT throttleSettings, COUNT(1) n FROM usr GROUP BY throttleSettings;
# user throttle != dom throttle
SELECT u.email, u.throttleSettings usrThrott, d.throttleSettings domThrott FROM usr u JOIN domain d USING(dom)
WHERE u.throttleSettings != d.throttleSettings;
# doms having users w diff user throttles
SELECT d.dom, d.throttleSettings domThrot, COUNT(u.dom) nDiff, GROUP_CONCAT(u.username) `diffUsr`
FROM domain d JOIN usr u USING(dom) WHERE u.throttleSettings != d.throttleSettings
GROUP BY d.dom;
# usrs with high limits / hour: maxBandWidth, maxMsgs, maxBounces
SELECT email `highMaxBandWidth`, throttleSettings FROM usr WHERE thrott_bwid(throttleSettings) > 100; # bandWidth
SELECT email `highMaxMsgs`, throttleSettings FROM usr WHERE thrott_msg(throttleSettings) > 500; # messages
SELECT email `highMaxBounces`, throttleSettings FROM usr WHERE thrott_bounce(throttleSettings) > 250; # messages
# / throttle *****

SELECT email, whitelistAddrCount, whitelistAddresses, whitelistDomCount, whitelistDomains FROM usr WHERE whitelistAddrCount + whitelistDomCount > 0;
SELECT email, blockedSenderCount, blockedSenders FROM usr WHERE blockedSenderCount > 0;
# have content filters beside the blocklist
SELECT email, contentFilterCount, blockedSenderCount FROM usr WHERE (contentFilterCount > 1 OR (contentFilterCount > 0 AND blockedSenderCount = 0));

SELECT email, fileStoreSize FROM usr WHERE fileStoreSize; # have files
SELECT email, signatureCount FROM usr WHERE signatureCount;
SELECT email, forwardsCount, forwards, delonforward FROM usr WHERE forwardsCount;
SELECT email, calendarCount, calendars FROM usr WHERE calendarCount;
SELECT email, noteCount FROM usr WHERE noteCount;
SELECT email, contactCount FROM usr WHERE contactCount;
SELECT email, autoresp_subj, autoresp_body FROM usr WHERE autoresp_subj > '';

SELECT email, fullname FROM usr WHERE email != fullname;