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,
WebFOCUS 7.1.3 Windows XP all output
February 13, 2020, 01:26 PM
BabakNYC
What specific version of WebFOCUS are you running?
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?
WebFOCUS 7.1.3 Windows XP all output
February 13, 2020, 02:47 PM
evan.brown
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
WF 8.0.08 (Prod); WF 8.2.06 (Dev)
February 13, 2020, 03:03 PM
FP Mod Chuck
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.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
February 13, 2020, 04:15 PM
Az
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.
WebFOCUS 7.1.3 Windows XP all output
February 14, 2020, 08:23 AM
Tom Flynn
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
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
Hallway
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
February 14, 2020, 04:52 PM
FP Mod Chuck
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats