Summary:
Customers may want to retieve status data directly from the IME base.

Below are some sample SQL scripts written for Oracle, although the majority of them will work on SQL Server.

You may request the schema of the database for the version of IME you are running from "support@tumbleweed.com" to assist in developing your own reports.

The attached technical bulletin provides the values of the recipient status field. The valid values in other fields are described in the IME Developers documentation and the IDL, which are only available if you have purchased an IME Developer license.

--------------------------------------------------------------------------------
SQL Script Descriptions:

--------------------------------------------------------------------------------
-- Sample Report - Summary of Packages Sent By Account With Size
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL script will prompt the user for the start and end date and time
-- which must be entered in the format "YYYYMMDD HHMISS".
-- The SQL will then display a list of all accounts within IME which have sent
-- and packages, together with a how many packages they have sent, how many
-- were sucessfully notified (the SMTP notification was delivered), and the
-- combined size of the packages.
-- This SQL script is useful in showing who the accounts on your system are,
-- which accounts are using the system most, and which accounts are taking
-- up most disk space on the system.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Sample Report - Package Detail of Specified Account
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL script will prompt the user for the email address of an IME account.
-- The SQL will then display a list of all packages belonging to that account
-- (either sent or recieved), together with the folder the package is in, who
-- sent the package, and the size of the package. The SQL also displays a count
-- of the folders and packages of that account, and a total size of the
-- packages.
-- This SQL script is useful in determining the usage of the IME system for a
-- specific account.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Sample Report - How Many Notifications Are In The Delvery Queue
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL will display a count of notifications in the delivery queue, broken
-- out by create date and schedule date.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Sample Report (complex) - Recipient State Detail
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL will prompt for the recipient email address.
-- The SQL will then display a list of all the packages sent to that recipient,
-- together with the date/time that the package was sent, and the current
-- status of the SMTP notification for each package.
-- Several fields are commented out in the SQL, and they serve as examples of
-- other potentially interesting data to display.
-- This SQL script is useful in determining the state of notifications for a
-- specific recipient, and may be useful in responding to a recipient who
-- indicates some issue with notification delivery from IME.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Sample Report (complex) - Recipient State Summary
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- The SQL will display a summary by day for the last 7 days of how many
-- notifications are in each state.
-- This SQL script is useful in determining if the IME system is having
-- problems delivering notifications, and to show how many recipients actually
-- view the package.
--------------------------------------------------------------------------------

Resolution:
--------------------------------------------------------------------------------
-- Sample SQL, compatible with IME4.0 and IME5.0
--------------------------------------------------------------------------------

-- "The schema is provided for informational use only. All data manipulation
-- (add/update/delete), should be done only through the IME Server API, and
-- care should be taken not to lock database objects. Important details of
-- the schema may change without notification, and we do not support any
-- changes made to the schema by the customer".


-- Tumbleweed does not support the sample SQL statements below, or guarantee
-- their accuracy or applicability to your need. The scripts may need
-- modifications in the event that the IME schema is updated. We would be
-- glad to provide paid consulting assistance to help develop your
-- customized reports.


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Sample Report - Summary of Packages Sent By Account With Size
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL script will prompt the user for the start and end date and time
-- which must be intered in the format "YYYYMMDD HHMISS".
-- The SQL will then display a list of all accounts within IME which have sent
-- and packages, together with a how many packages they have sent, how many
-- were sucessfully notified (the SMTP notification was delivered), and the
-- combined size of the packages.
-- This SQL script is useful in showing who the accounts on your system are,
-- are which accounts are using the system most, and which accounts are taking
-- up most disk space on the system.
--------------------------------------------------------------------------------
column Sender format a35
column LastName format a30
column NotifyCnt format 999,999
column StoreItemCount format 999,999
column Size_MB format 9,999

SELECT
si.TwSenderAddress Sender,
si.TwSenderLastname Lastname,
SUM(si.TwNotifyCnt) NotifyCnt,
COUNT(si.twcreatetm) StoreItemCount,
SUM(si.TwTotalByte)/1048576 Size_MB
FROM TwStoreItem si
WHERE si.twcreatetm >= to_date('&&StartTm_YYYYMMDD_HHMISS', 'YYYYMMDD HH24MISS')
AND si.twcreatetm < to_date('&&EndTm_YYYYMMDD_HHMISS', 'YYYY-MM-DD HH24:MI:SS')
group BY si.TwSenderAddress, si.TwSenderLastname
ORDER BY Sender, Lastname;
commit;
--------------------------------------------------------------------------------
-- End of SQL
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Sample Report - Package Detail of Specified Account
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL script will prompt the user for the email address of an IME account.
-- The SQL will then display a list of all packages belonging to that account
-- (either sent or recieved), together with the folder the package is in, who
-- sent the package, and the size of the package. The SQL also displays a count
-- of the folders and packages of that account, and a total size of the
-- packages.
-- This SQL script is useful in determining the usage of the IME system for a
-- specific account.
--------------------------------------------------------------------------------
clear breaks
-- break on recipient on folder on report
break on report
compute count of folder on report
compute sum of TwId on report
compute sum of PkgSize_KB on report

SELECT
TwUser.TwAddr Account,
TwFolder.TwName folder,
si.TwSenderAddress sender,
-- si.TwSubject subject,
to_char(si.twcreatetm, 'YYYY-MM-DD HH24:MI:SS') created,
-- to_char(si.twexpiretm, 'YYYY-MM-DD HH24:MI:SS') expired,
si.TwTotalByte/1024 PkgSize_KB
FROM TwStoreItem si, TwPackage, TwFolder, TwUser
WHERE TwUser.TwId = TwFolder.TwUser
AND TwPackage.TwFolderId = TwFolder.TwId (+)
AND si.TwId = TwPackage.TwStoreItem
-- AND si.twcreatetm >= sysdate - (2/24) -- 2 hours
-- AND si.twcreatetm >= sysdate - 7 -- 7 days
-- This will prompt for the Account email address
AND TwUser.TwAddr = '&&Account_Email'
ORDER BY Account, folder, created, sender;
commit;
--------------------------------------------------------------------------------
-- End of SQL
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Sample Report - How Many Notifications Are In The Delvery Queue
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL will display a count of notifications in the delivery queue, broken
-- out by create date and schedule date.
--------------------------------------------------------------------------------
set pagesize 1000
set linesize 160
set tab off

clear columns
column created format a20
column scheduled format a20
break on report
compute count of created on report
compute sum of Recipient_Count on report

SELECT
to_char(si.TwCreateTm, 'YYYY-MM-DD') created,
to_char(si.TwScheduleTm, 'YYYY-MM-DD') scheduled,
COUNT(*) Recipient_Count
FROM TwStoreItem si, TwPackage pk, TwQueue q
WHERE q.TwPackage = pk.TwId
AND pk.TwStoreItem = si.TwId
GROUP BY to_char(si.TwCreateTm, 'YYYY-MM-DD'),
to_char(si.TwScheduleTm, 'YYYY-MM-DD')
ORDER BY created,
scheduled;
commit;
--------------------------------------------------------------------------------
-- End of SQL
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Sample Report (Comples) - Recipient State Detail
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- This SQL will prompt for the recipient email address.
-- The SQL will then display a list of all the packages sent to that recipient,
-- together with the date/time that the package was sent, and the current
-- status of the SMTP notification for each package.
-- Several fields are commented out in the SQL, and they serve as examples of
-- other potentially interesting data to display.
-- This SQL script is useful in determining the state of notifications for a
-- specific recipient, and may be useful in responding to a recipient who
-- indicates some issue with notification delivery from IME.
--------------------------------------------------------------------------------
ttitle ' Recipient Detail '

set pagesize 1000
set linesize 80
set tab off

column sender format a30
column recipient format a40
column created format a20
column scheduled format a20
column expired format a20
column Notified format a20
column subject format a80
col cnt format 99
col state_desc format a60
col blank heading '' format a79

-- For this customer they use a custom attribute in the storeitem table.
col TwCustomString0 heading 'Custom URL' format a79
col TwCustomString1 heading 'Custom Subject' format a79
col TwCustomTime0 heading 'Custom Process Date' format a20


SELECT
-- si.TwSenderAddress sender,
rc.TwAddrA recipient,
-- si.TwSubject subject,
-- rc.TwDeliveryMethod method,
-- rc.TwMedium medium,
to_char(si.TwCreateTm, 'YYYY-MM-DD HH24:MI:SS') created,
-- to_char(si.TwScheduleTm, 'YYYY-MM-DD HH24:MI:SS') scheduled,
-- to_char(rc.TwNotifTm, 'YYYY-MM-DD HH24:MI:SS') Notified,
-- rc.TwProgress Cnt,
-- to_char(rc.TwNState , 'XXXXXX') nstate,
-- NVL(si.TwCustomString0,'(NULLL Custom URL)' ) TwCustomString0,
decode(rc.TwNState,
196608,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_NOT_QUEUED',
196625,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_PREPROCESSING',
196641,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_PENDING',
196657,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_RETRY_WITHOUT_ATTEMPT',
1114369,'TW_RECIPIENT_STATE_STANDARD_NOTIFICATION_RETRY',
2162944,'TW_RECIPIENT_STATE_STANDARD_PROCESSING_NOTIFICATION',
2162960,'TW_RECIPIENT_STATE_STANDARD_NOTIFICATION_OK',
2162976,'TW_RECIPIENT_STATE_STANDARD_NOTIFICATION_CONFIRMED',
2162992,'TW_RECIPIENT_STATE_STANDARD_PICKUP_STARTED',
2163008,'TW_RECIPIENT_STATE_STANDARD_PICKUP_LOGIN_FAIL',
2163024,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_STRONG_CRYPTO_ERROR',
2163025,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_PRIVATE_KEY_ERROR',
2163026,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_SECURITY_UNSUPPORTED_MIME_ERROR',
2163027,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_PROCESSING_ERROR',
2163028,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_UNKNOWN_ERROR',
3342336,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_INTERNAL_FAILURE',
3342352,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_PREPROCESSING_FAILURE',
3342368,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_GIVEUP_FAILURE',
3342384,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_SECURITY_FAILURE',
3211520,'TW_RECIPIENT_STATE_STANDARD_BAD_DOMAIN_FAILURE',
3211536,'TW_RECIPIENT_STATE_STANDARD_BAD_ACCOUNT_FAILURE',
3211552,'TW_RECIPIENT_STATE_STANDARD_BAD_ACCOUNT_BOUNCED_FAILURE',
3211568,'TW_RECIPIENT_STATE_STANDARD_DOMAIN_ACCESS_NOT_ALLOWED',
3280896,'TW_RECIPIENT_STATE_GENERIC_BAD_DELIVERY_METHOD',
3280912,'TW_RECIPIENT_STATE_GENERIC_BAD_ADDRESS',
3280928,'TW_RECIPIENT_STATE_GENERIC_BAD_ADDRESS_MEDIUM',
4390912,'Success - TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_SKIPPED',
4260096,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_LOGIN_OK',
4260112,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_DOWNLOADED',
4260128,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_CONFIRMED',
4260144,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_EXPIRED',
'Decode Value Unknown Error') state_desc
-- '-------------------------------------------------------------------------------' blank
FROM TwStoreItem si, TwRecipient rc
WHERE rc.TwStoreItem = si.TwId
-- AND si.TwCreateTm >= sysdate - (2/24) -- 2 hours
-- AND si.TwCreateTm >= sysdate - 7 -- 7 days
-- WHERE si.TwCreateTm >= to_date('$STARTTM', 'YYYY-MM-DD HH24:MI:SS')
-- AND si.TwCreateTm < to_date('$ENDTM', 'YYYY-MM-DD HH24:MI:SS')
-- AND rc.TwAddra = 'support@tumbleweed.com'
-- prompt for recipient email address
AND rc.TwAddra = '&&Recipient_Email'
ORDER BY created, recipient;
commit;
--------------------------------------------------------------------------------
-- End of SQL
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Sample Report (Complex) - Recipient State Summary
--------------------------------------------------------------------------------
-- IME4.0, IME5.0
-- The SQL will display a summary by day for the last 7 days of how many
-- notifications are in each state.
-- This SQL script is useful in determining if the IME system is having
-- problems delivering notifications, and to show how many recipients actually
-- view the package.
--------------------------------------------------------------------------------
set pagesize 1000
set linesize 160
set tab off

clear columns
column CreateTm format a10
column StateTm format a10
column Counter format 999,999,999
column Recipient_Status format a60

clear breaks
break on CreateTm on report
compute sum of Counter on CreateTm
compute sum of Counter on report

SELECT
to_char(si.TwCreateTm, 'YYYY-MM-DD') CreateTm,
to_char(rc.TwStateTm, 'YYYY-MM-DD') StateTm,
COUNT(rc.twid) Counter,
decode(rc.TwNState,
196608,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_NOT_QUEUED',
196625,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_PREPROCESSING',
196641,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_PENDING',
196657,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_RETRY_WITHOUT_ATTEMPT',
1114369,'TW_RECIPIENT_STATE_STANDARD_NOTIFICATION_RETRY',
2162944,'TW_RECIPIENT_STATE_STANDARD_PROCESSING_NOTIFICATION',
2162960,'TW_RECIPIENT_STATE_STANDARD_NOTIFICATION_OK',
2162976,'TW_RECIPIENT_STATE_STANDARD_NOTIFICATION_CONFIRMED',
2162992,'TW_RECIPIENT_STATE_STANDARD_PICKUP_STARTED',
2163008,'TW_RECIPIENT_STATE_STANDARD_PICKUP_LOGIN_FAIL',
2163024,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_STRONG_CRYPTO_ERROR',
2163025,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_PRIVATE_KEY_ERROR',
2163026,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_SECURITY_UNSUPPORTED_MIME_ERROR',
2163027,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_PROCESSING_ERROR',
2163028,'TW_RECIPIENT_STATE_STANDARD_PUBLIC_KEY_DELIVERY_UNKNOWN_ERROR',
3342336,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_INTERNAL_FAILURE',
3342352,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_PREPROCESSING_FAILURE',
3342368,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_GIVEUP_FAILURE',
3342384,'TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_SECURITY_FAILURE',
3211520,'TW_RECIPIENT_STATE_STANDARD_BAD_DOMAIN_FAILURE',
3211536,'TW_RECIPIENT_STATE_STANDARD_BAD_ACCOUNT_FAILURE',
3211552,'TW_RECIPIENT_STATE_STANDARD_BAD_ACCOUNT_BOUNCED_FAILURE',
3211568,'TW_RECIPIENT_STATE_STANDARD_DOMAIN_ACCESS_NOT_ALLOWED',
3280896,'TW_RECIPIENT_STATE_GENERIC_BAD_DELIVERY_METHOD',
3280912,'TW_RECIPIENT_STATE_GENERIC_BAD_ADDRESS',
3280928,'TW_RECIPIENT_STATE_GENERIC_BAD_ADDRESS_MEDIUM',
4390912,'Success - TW_RECIPIENT_STATE_STANDARD_OR_GENERIC_SKIPPED',
4260096,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_LOGIN_OK',
4260112,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_DOWNLOADED',
4260128,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_CONFIRMED',
4260144,'Success - TW_RECIPIENT_STATE_STANDARD_PICKUP_EXPIRED',
'Decode Value Unknown Error') Recipient_Status
FROM TwStoreItem si, TwRecipient rc
-- if you want just the past weeks data
WHERE si.twcreatetm >= sysdate - 7 -- 7 days
AND rc.TwStoreItem = si.TwId
GROUP BY
to_char(si.TwCreateTm, 'YYYY-MM-DD'),
to_char(rc.TwStateTm, 'YYYY-MM-DD'),
rc.TwNState
ORDER by CreateTm, StateTm, Recipient_Status;
commit;
--------------------------------------------------------------------------------
-- End of SQL
--------------------------------------------------------------------------------

Attached Files:

Original Author:
Original Submitter: DKALT
Keywords: Sample SQL Script Report IME ID#29338