Useful SQL Queries regarding E-mails and Messages related information within GoldMine

Version 1

    Details

    Useful SQL Queries regarding E-mails and Messages related information within GoldMine

    GoldMine Technical Support does  not assist in creating (complex) SQL queries, but the below queries were often requested by customers and  also are often used by GoldMine Technical Support during investigations

    Please keep in mind that all queries are always only provided as-is and you may want or need to adjust additional information you may want to see or not see.

    All SQL queries are SELECT statements and will not change any  data within the GoldMine database and they can either ve performed from  within GoldMine (*) or from the SQL Server Management Studio

    (*) the GoldMine SQL Query feature does not respect Record ownership and curtaining

    A. Via GoldMine's SQL query feature

    1. Tools >> SQL Query
    2. Copy and paste the desired SELECT query from below into the Query pane
    3. Button Query
    >> the result set will appear in the lower pane
    4. the query can be saved via the Save button for a later use

    B. via SQL Server Management Studio
    1. Open the SQL Server Management Studio
    2. Button New Query
    3. Make sure to select the (desired) GoldMine database from the database drop down
    4.
    Copy and paste the desired SELECT query from below into the Query pane
    5. run the query via the button Execute (red exclamation mark)
    >> the result set will appear in the lower pane


    Resolution



    (1) Find an E-mail with related (primary) contact information based on a search term within the body of the E-mail (GMPE 8.0 and below)

     

    SELECT C1.ACCOUNTNO,C1.COMPANY,C1.CONTACT, MB.MAILDATE ,MB.MAILREF

    FROM CONTACT1 C1

    JOIN CONTHIST CH

    ON C1.ACCOUNTNO = CH.ACCOUNTNO

    JOIN MAILBOX MB

    ON CH.LINKRECID = MB.RECID

    WHERE  MB.RFC822 LIKE '%TYPE TEXT HERE%' AND MB.USERID = ‘TYPE USERID HERE

     

    Find an E-mail based on something in the body of the E-mail (GMPE 8.5 and above)

     

    SELECT CONTACT1.COMPANY, CONTACT1.CONTACT, MAILBOX.MAILREF, MAILBOX.MAILDATE

    FROM CONTACT1 INNER JOIN

     MAILBOX ON CONTACT1.ACCOUNTNO = MAILBOX.ACCOUNTNO

    WHERE     (CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),MAILBOX.RFC822)) LIKE '%TYPE TEXT HERE%' AND USERID LIKE 'TYPE USERID HERE')

     

    This can be also used to find a specific attachment within the Emails – e.g. '%image02.png '

     


     

    Counts of the number of e-mails in Inbox, Outbox and Deleted Items per user

    SELECT MB.USERID,MB.FOLDER,COUNT(*) AS TOTAL

    FROM MAILBOX MB

    WHERE MB.FOLDER IN ('X-GM-TRASH', 'X-GM-INBOX', 'X-GM-OUTBOX')

    GROUP BY  MB.USERID,MB.FOLDER

    ORDER BY MB.USERID,MB.FOLDER

     

    Counts of the number of e-mails in Email Center folders per user

     

    SELECT  MB.USERID,MB.FOLDER,MB.FOLDER2,COUNT(*) ASTOTAL

    FROM  MAILBOX MB

    WHEREUSERID <> '' andFOLDER not in ('X-GM-TD-SETTING','X-GM-ICALINFO','X-GM-SUBFILED','X-GM-SUBSENT','X-GM-FOLDERS')

    GROUP BY  MB.USERID,MB.FOLDER,MB.FOLDER2

    ORDER BYMB.USERID,MB.FOLDER,MB.FOLDER2

     

    Attachments of e-mails

     

    SELECTCONTACT1.COMPANY, CONTACT1.CONTACT,MAILBOX.MAILREF, MAILBOX.MAILDATE,SUBSTRING(CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),MAILBOX.RFC822)),

    CHARINDEX('Content-Disposition: attachment; filename=',CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),MAILBOX.RFC822)))

    +33,DATALENGTH(CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),MAILBOX.RFC822))))

    FROMCONTACT1 INNER JOIN

     MAILBOX ONCONTACT1.ACCOUNTNO = MAILBOX.ACCOUNTNO

    WHERE     (CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),MAILBOX.RFC822)) LIKE '%Content-Disposition: attachment; filename=%' ANDUSERID LIKE '%Type in Username%')