Hello:
Does anyone know how to use the BOT tables to report off them to show what distro list is associated with what schedules. Unfortunately and sadly IBI cannot and do not know how to provide an answer. For an IT solutions company, I am appalled that they do not understand their own table structures and don't even have an ER diagram of their own tables. And to think that when an IB consultant goes out on an engagement, one of the key requirements from IBI is to request an ER diagram.
I hope there is someone out there who like us, have the same need for reporting off the BOT tables.

This message has been edited. Last edited by: FP Mod Chuck,

What specific version of WebFOCUS are you running?

Perhaps this is a starting point:
https://webfocusinfocenter.inf..._reposreports150.htm

Hello BabakNYC:

We are in WF8206. Yes we had that link. Thanks. But it does not tell us how to JON the tables and after trying all kinds of JOINs we still can't link the 4 tables:
BOTSCHED
BOTDEST
BOTDIST
BOTADDR

Do you have .fex that allow your users to query what jobs are sending reports to them as an example?

I believe this is correct:

  
SCHEDULEID IN BOTSCHED TO SCHEDULEID IN BOTDIST
DISTLIST IN BOTDIST TO ADDRBOOKID IN BOTDEST
DISTLIST IN BOTDIST TO ADDRBOOKID IN BOTADDR

Az

I looked at the database diagram in SQL Server and it doesn't appear to be a way to JOIN the 4 tables together. As Evan said you can join the BOTSCHED to BOTLIST with the SCHEDULEID and you can JOIN BOTADDR to BOTDEST with the ADDRBOOKID but there is no connection between the two groupings. DISTLIST is not populated when you use a Distribution list in the schedule it is only populated with individual e-mail addresses when that option is used.

Chuck/Evan: That is exactly our findings too. But in order to connect a schedule to a email address we need to connect the 4. At least that is my understanding. It is as if there is another table missing but I cant find it and IBI as no help. They said they knew nothing about hoe the tables connected and that I would need to purchase more software if I wanted to get information about how these tables connect. I am just stunned

Or am I missing something in my understanding of the tables?

Do you and Evan have ant reports that go against these tables? All our existing reports in WF77 no longer function because of the table changes.

Here, IF you use SQL Passthru, BUT, if you're really on 7.1.3, this may not work. We're using DB2.
Tables changed when we upgraded to 8.2. This searches for an email string, just remove the filters:
USER table is in our datamart, remove...

  
-SET &EMAIL_STR = 'CPSFUELTSM' ;
ENGINE DB2 SET DEFAULT_CONNECTION &DBNAME
ENGINE DB2
SELECT DISTINCT
CASE BS.CASTER_USER
   WHEN 'admin' THEN 'FCO'
   ELSE U.USER_FSTNM||' '||U.USER_LSTNM
    END AS OWNER
,BSIT.INTERVALTYPE AS FREQUENCY
,BDIST.MAILSUBJECT
,BT.TASKOBJ AS EXECUTABLE

,CASE BDIST.METHOD_CODE
   WHEN 'L' THEN 'LIBRARY'
   WHEN 'M' THEN 'EMAIL'
   ELSE 'UNKNOWN'
    END AS DELIVERY_METHOD
,BDIST.DISTLIST AS DIST_LIST
,BD.DESTFN AS DIST_DETAIL

FROM VFCR.BOTSCHED BS

JOIN VFCR.BOTTASK BT
       ON BS.PACKETID = BT.PACKETID

LEFT OUTER JOIN VFCR.BOTADDR BA 
       ON BS.IBFSID = BA.IBFSID

LEFT OUTER JOIN VFCR.BOTDEST BD
       ON BA.ADDRBOOKID = BD.ADDRBOOKID

JOIN VFCR.BOTDIST BDIST 
       ON BS.SCHEDULEID = BDIST.SCHEDULEID

JOIN VFCR.BOTSIT BSIT 
       ON BS.SCHEDULEID = BSIT.SCHEDULEID

JOIN VFCR.USER U
       ON BS.CASTER_USER = U.USER_LOG_ID

WHERE
( UCASE(BDIST.DISTLIST) LIKE '%&EMAIL_STR.EVAL%'
   OR
UCASE(BD.DESTFN) LIKE '%&EMAIL_STR.EVAL%')
  AND BS.ACTIVE = 'Y'
FOR FETCH ONLY WITH UR;
TABLE ON TABLE HOLD AS EMAILSRCH FORMAT ALPHA
END
-RUN

TABLE FILE EMAILSRCH
PRINT *
END
-EXIT

hth

Our repository is in SQL Server, and we use this query to get each job and the email addresses that it is distributed to:

  
SELECT schd.JOBDESC                                         'rc_job'
     , schd.CASTER_USER                                     'schd_userId'
     , addr.BOOKNAME                                        'dist_list'
     , dest.FLDVLU                                          'burst_value'
     , IIF(addr.BOOKNAME IS NULL,dist.DISTLIST,dest.DESTFN) 'email_address(s)'
FROM dbo.BOTSCHED AS schd WITH(READUNCOMMITTED)
     LEFT JOIN dbo.BOTDIST AS dist WITH(READUNCOMMITTED) ON schd.SCHEDULEID=dist.SCHEDULEID
     LEFT JOIN dbo.BOTDEST AS dest WITH(READUNCOMMITTED) ON dist.DISTLIST=dest.ADDRBOOKID
     LEFT JOIN dbo.BOTADDR AS addr WITH(READUNCOMMITTED) ON addr.ADDRBOOKID=dest.ADDRBOOKID
ORDER BY 1

But to echo Tom, our repository is probably different than yours