Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Reporting against BOT Tables in WF8
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Reporting against BOT Tables in WF8
 Login/Join
 
Member
posted
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
 
Posts: 22 | Registered: March 29, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
What specific version of WebFOCUS are you running?

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


WebFOCUS 8206, Unix, Windows
 
Posts: 1818 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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?


WebFOCUS 7.1.3
Windows XP
all output
 
Posts: 22 | Registered: March 29, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
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.04 (Dev)
 
Posts: 66 | Location: OK | Registered: November 19, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1796 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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 Frowner

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
 
Posts: 22 | Registered: March 29, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1961 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
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
WF(Prod):8202M1
WF(Test):8202M4
OS/Platform:Win 10
Outputs:All
 
Posts: 549 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Good One


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1796 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Reporting against BOT Tables in WF8

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.