Mail accounts and loose folders report to CSV using AWK. How to achieve that from this example?

Question

I'm using this shellscript to generate a list of inboxes and the sizes of all folders, for each user, as follows:

john.doe@mydomain.com's max mailbox size = 0 MB, current mailbox size = 18,78 GB.

size (MB)  msgcount     unread folder
--------- --------- ---------- ----------------------------
        0         0          0 /Chats
       42         0            /Drafts
    13118     28014         37 /Inbox
        0         6          0 /Junk
        0         1          0 /Orders
      323     13385         17 /Raster
     5772      3760          0 /Sent
        1       183          0 /Payments
        0         2          0 /Trash
-------------------------------------------------------

I need to mine data from this and throw it into CSV where on each line I'll have the email account and the values for Trash, Sent and Junk folders values. The problem is the "Inbox" because, as you can see, users created folders outside the tree (like "Raster" and "Payments"). So I need to find a way to sum everything that's not "thrash/sent/junk" for each user from this report.

Can you clarify if the input contain data for one/multiple users ? Also good idea to post expected output for sample input — dash-o, Oct 17 '19 at 12:32
your shell-script itself could be useful as well, since you generate this table it's quite possible you have necessary data already in form of variables... — gkhaos, Oct 17 '19 at 12:39
The script I've used was [this one](https://github.com/stsimb/zimbra-scripts/blob/master/zimbra-size.sh). Just did an for-loop reading the mail addresses from a text file. — Fábio Brizzolla, Oct 17 '19 at 13:49

dash-o · Accepted Answer · 2019-10-18T04:53:01.780

0

The following awk can be used as a starting point. It collect data, and read the data into memory, and print the summary at the END event.

awk -v OFS=, '
function do_print () {
        print user, s_trash, n_trash, s_sent, n_sent, s_junk, n_junk, s_other, n_other
}

/max mailbox/ { user = $1 ;
        s_trash = n_trash = s_sent = n_sent = s_junk = n_junk = s_other = n_other = 0
        next ;
}
    # Parse lines starting with '/'
$4 ~ /^\/Trash/ { s_trash += $1 ; n_trash += $2 ; next }
$4 ~ /^\/Sent/ { s_sent += $1 ; n_sent += $2 ; next }
$4 ~ /^\/Junk/ { s_junk+= $1 ; n_junk += $2 ; next }
   # Everything else goes to other
$4 ~ /^\// { s_other += $1 ; n_other = $2 ; next }

  # Print whenever there is a line of '='
/==================/ { do_print() }

END { do_print() }
'

edited Oct 18 '19 at 04:53

answered Oct 17 '19 at 12:43

dash-o

13,723
1
10
37

The input file contains hundreds of email accounts. I've tried to specify an regex as Field Separator ( -F'={55}') as every new account details is separated using 55 equals characters. But only the very last mail account is processed. I'm not so good with AWK, so probably I'm missing something very simple. – Fábio Brizzolla Oct 18 '19 at 00:52
@FábioBrizzolla The FS is used to separate fields within the record. Take a look at awk tutorial (https://www.tutorialspoint.com/awk/index.htm) for more information on how awk is working. I've posted a change which I believe will work, but hard to tell since the input that you posted does include only one entry with the delimiter between recorder. – dash-o Oct 18 '19 at 04:51
you're awesome. Thank you very very very much. Its working like a charm!!! – Fábio Brizzolla Oct 18 '19 at 20:18

score 0 · Answer 2 · answered Dec 04 '19 at 18:03

this is my php script to get quota from all users in domain

it outputs something like this

function zmbGetQuotaUsageRequest($domain, $adminAuthToken, $output='array') {

    # URL que somente administradores conseguem acessar
    $url = 'https://mail.meudominio.com.br/service/wsdl/ZimbraAdminService.wsdl';

    $requestParams = array(
        'allServers' => 1,
        'domain'     => $domain,
    );

    $context = stream_context_create([
        'ssl' => [
            'allow_self_signed' => true,
            'SNI_enabled'       => true,
            'verify_peer'       => false,
            'verify_peer_name'  => false,
        ]
    ]);

    try {
        $soapHeader = array(
            new SoapHeader(
                'urn:zimbra',
                'context',
                new SoapVar('<ns2:context><authToken>'.$adminAuthToken.'</authToken></ns2:context>', XSD_ANYXML)
            )
        );

        $client = new SoapClient($url, [
            'stream_context' => $context,
        ]);

        $client->__setSoapHeaders($soapHeader);
        $response = $client->GetQuotaUsageRequest($requestParams);
    }
    catch (SoapFault $e) {
        $fault  = $client->__soap_fault;
        $code   = (string) $fault->detail->Error->Code;
        $reason = (string) $fault->faultstring;
        $response['fault'] = [ 'code' => $code, 'reason' => $reason ];
        return $response;
    }


    # Converter stdClass Object em array
    $response = json_decode(json_encode($response), true);

    # Converter a resposta wsdl do zimbra em array usando email como chave
    $banco = [];
    foreach ($response['account'] as $idx => $atr) {
        $name = $atr['name'];
        unset($atr['name'], $atr['id']);
        $banco[$name] = $atr;
    }

    ksort($banco);
    $bancojson = json_encode($banco, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);

    return ($output == 'json') ? $bancojson : $banco;
}

Mail accounts and loose folders report to CSV using AWK. How to achieve that from this example?

2 Answers2