Any easy way to check ALL the broadLog schemas?

Hi Hector, 

Since you have multiple schemas and I’m assuming you’ll want to check their counts more than once, I would recommend creating a workflow unless you have direct database access:

Here is how I’d build out the workflow:

1. One query per broadLog schema

   • Add a Query activity.
   • Set the Targeting dimension to each broadLog schema in turn (e.g. nms:broadLogRcp, cus:broadLogX, etc.).
   • Filter by date and status as needed.
   • In Additional data, compute a constant field, e.g. @channelLabel = 'email', 'push', 'sms_custom', etc.

2. Union them with a Union activity

   • Chain all queries into a Union.
   • Make sure the outgoing schema has at least:
     • @channelLabel
     • maybe @iDeliveryId
     • whatever else you want to group by.

3. Aggregate

   • Use an Aggregate activity:
     • Group by @channelLabel
     • Add a count() of records.

4. Optionally export

   • Use Extract file to write a CSV with:
     • Channel, Count of messages
     • Optionally per day/per delivery.
        

If you have DB access, executing SQL such as the below example would be the quickest option. Note, syntax and table names will likely differ based on your specific DB type and any custom fields:

-- Example: total messages SENT per channel across all broadlogs, last 30 days
SELECT
  'broadLogCustomer' AS sourceTable,
  COUNT(*)           AS totalMessages,
  SUM(CASE WHEN iStatus = 1 THEN 1 ELSE 0 END) AS sent,
  SUM(CASE WHEN iStatus = 2 THEN 1 ELSE 0 END) AS failed
FROM cusbroadlogcustomer
WHERE tsEvent >= now() - INTERVAL '30 days'

UNION ALL

SELECT
  'broadLogProspect' AS sourceTable,
  COUNT(*),
  SUM(CASE WHEN iStatus = 1 THEN 1 ELSE 0 END),
  SUM(CASE WHEN iStatus = 2 THEN 1 ELSE 0 END)
FROM cusbroadlogprospect
WHERE tsEvent >= now() - INTERVAL '30 days'

UNION ALL

SELECT
  'broadLogAppSub' AS sourceTable,
  COUNT(*),
  SUM(CASE WHEN iStatus = 1 THEN 1 ELSE 0 END),
  SUM(CASE WHEN iStatus = 2 THEN 1 ELSE 0 END)
FROM cusbroadlogappsub
WHERE tsEvent >= now() - INTERVAL '30 days';

Then, if you want a global total:

SELECT
  SUM(totalMessages) AS allMessages,
  SUM(sent)          AS allSent,
  SUM(failed)        AS allFailed
FROM (
  -- paste the UNION ALL block above here
) t;