Focal Point
[CLOSED] Generic logging of report usage

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2087052116

October 18, 2011, 04:08 AM
Wep5622
[CLOSED] Generic logging of report usage
We've been looking at approaches to log which focus executables are used and how frequent they are being used, so far without success. Ideally we'd like to be able to report on those "logs" as well, meaning we'd like to create some reports querying that data.

The goals of logging this information are to see whether reports that our users requested from us are actually being used, to find reports that are no longer needed and, possibly, find reports that underperform or result in errors.

We have a setup where IIS handles the requests. It passes on focexec requests to Tomcat via ISAPI.

We looked into parsing the IIS log files (some of you may recall an older topic I started about how to parse such files), but it doesn't contain all the information we require - as soon as WebFOCUS uses a POST request, all the information we have is that WFServlet was requested.

We also looked into adding code to the server profile, but at that time the names of the relevant focus executable(s) don't appear to be known yet.

I've looked at the Tomcat logs as well, but there doesn't appear to be much of relevance in them.

I'm sure several of you would be interested in having something like this, or maybe you already do and you can tell what you did, what does and doesn't work, etc. So, if you have input on this, please post.

This message has been edited. Last edited by: Kerry,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
October 18, 2011, 12:12 PM
rfbowley
Two words: Resource Analyzer.

Unfortunately that is another liscense from IBI, but it will give you all the detail you need.

The only other way to do it that I know of is to design a table with all the metrics you would care to save and have each fex either execute a stored procedure to log the info, or if it is a FOCUS table, MODIFY.


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
October 18, 2011, 12:44 PM
Francis Mariani
Three words: Do It Yourself. I have successfully implemented this in several environments. I add an INCLUDE in the fex that captures the elapsed time and writes a row to a FOCUS DB or RDBMS table storing fex name, user id, elapsed time, etc. Easy peasy and no licence.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 18, 2011, 03:16 PM
Waz
We have done similarly.

An include that writes to a FOC db the information needed.

If you go down this path, then I would suggest calling this include at the beginning and end of each call. This way if the fex crashes badly, you will know that is hasn't/didn't finish.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

October 18, 2011, 03:24 PM
Francis Mariani
Good idea!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 18, 2011, 03:28 PM
jbmuir
Have you considered using a Tomcat filter to log the request?

Here is a template to get you started:

// LogFilter - This class defines a Tomcat Servlet 2.3 Filter
//
// ------------------------------------------------------------------------
package org.yourfabulousorg.filters;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;

public final class LogFilter implements Filter
{
    // Data.

    private FilterConfig config = null;

    // Public Methods.

    // init: initialize the filter.
    // ------------------------------------------------------------------
    public void init (FilterConfig c)
    {
        this.config = c;
    }

    // destroy: destroy the filter.
    // ------------------------------------------------------------------
    public void destroy ()
    {
        this.config = null;
    }

    // doFilter: let's do some filtering. Log the request.
    // ------------------------------------------------------------------
    public void doFilter (ServletRequest req, ServletResponse res,
            FilterChain chain) throws ServletException, IOException
    {
        // You write your java code to log the fabulous request here.
        
        // Chain to next filter.
        chain.doFilter(req,res);
    }
}


Which you place in:

/path/to/tomcat/lib/org/yourfabulousorg/filters

And you'll have to modify the web.xml in the WFServlet to add in your filter:

    <!-- Log Filter (Your Fabulous Org) -->
    <filter>
      <filter-name>LogFilter</filter-name>
      <filter-class>org.yourfabulousorg.filters.LogFilter</filter-class>
    </filter>

    <filter-mapping>
      <filter-name>LogFilter</filter-name>
      <url-pattern>/*</url-pattern>
    </filter-mapping>


Hope this gives you some ideas...
Google will help with this task too...
-James


WF 7.1.6 moving to WF 7.7, Solaris 10, HTML,PDF,XL
October 19, 2011, 10:58 AM
DavSmith
Wep, I implemented the same as Francis and WAZ. It's low cost, low maintenance and high payback.

Three things are needed:


I can describe the Master and -INCLUDE here with out taking up too much space.

Here is the master:
FILENAME=focstt, SUFFIX=FOC     , $
  SEGMENT=SEG, SEGTYPE=S1, $
    FIELDNAME=IN_DATE, USAGE=HYYMDs, $
    FIELDNAME=USER_ID, USAGE=A25, $
    FIELDNAME=SERVER, USAGE=A30, $
    FIELDNAME=APPROOT, USAGE=A50, $
    FIELDNAME=FOCEXEC, USAGE=A50, $
    FIELDNAME=FEXDESC, USAGE=A75, $
    FIELDNAME=OUT_DATE,USAGE=HYYMDs,$


Here is the -INCLUDE to be added to the beginning and end of every fex you want to track called FOCSTAT.FEX :
-**************************************************************************************
-* At top of FEX, add
-* -SET &FEX='<name of fex/htm>';
-* -SET &FEXDESC='<brief description>';
-* -SET &INOUT='OUT';
-* -MRNOEDIT -INCLUDE FOCSTAT
-*
-* and after the program run:
-*
-* -SET &INOUT='OUT';
-* -MRNOEDIT -INCLUDE FOCSTAT
-*
-**************************************************************************************
-DEFAULT &INOUT=ENDINOUT;
-DEFAULT &MREUSAGE='Y';
-IF &MREUSAGE EQ 'N' GOTO ENDINOUT;
-GOTO &INOUT
-**************************************************************************************
-IN
-**************************************************************************************
-SET &IN_DATE=HCNVRT(HGETC(12,'HYYMDm'), '(H17)' , 17, 'A20');
-DEFAULTS &FEX='FOCFEXNAME.EVAL'
-DEFAULTS &FEXDESC='THIS IS A TEST &TOD.EVAL'
USE FOCSTT ON FOCSU01
END
MODIFY FILE FOCSTT
FREEFORM IN_DATE USER_ID SERVER APPROOT FOCEXEC FEXDESC
COMPUTE IN_DATE=HINPUT(17, '&IN_DATE', 12, 'HYYMDm');
MATCH IN_DATE
  ON NOMATCH INCLUDE
  ON NOMATCH IF FOCURRENT EQ 0 THEN GOTO EXIT ELSE GOTO EXIT;
  ON MATCH GOTO EXIT
DATA
USER_ID='&IWAUSER',SERVER='&&CSERVER',APPROOT='&APPROOT',FOCEXEC='&FEX',FEXDESC='&FEXDESC',$
END
-RUN
-GOTO ENDINOUT
-**************************************************************************************
-OUT
-**************************************************************************************
-SET &OUT_DATE=HCNVRT(HGETC(12,'HYYMDm'), '(H17)' , 17, 'A20');
MODIFY FILE FOCSTT05
COMPUTE IN_DATE=HINPUT(17, '&IN_DATE', 12, 'HYYMDm');
COMPUTE OUT_DATE=HINPUT(17, '&OUT_DATE', 12, 'HYYMDm');
MATCH IN_DATE
  ON NOMATCH REJECT
  ON MATCH UPDATE OUT_DATE
DATA
USER_ID='&IWAUSER',SERVER='&&CSERVER',APPROOT='&APPROOT',FOCEXEC='&FEX',FEXDESC='&FEXDESC',$
END
-RUN
-**************************************************************************************
-ENDINOUT




In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
October 19, 2011, 11:51 AM
Wep5622
Good stuff guys!

I like the java-class approach, as (once installed) it will log every fex execution without having to add code to the fexes. That will work for both old and future fexes right away.

Of course, getting it to write to for example a database table will be a bit more involved. Should be doable though, time to dust off my Java skills :P

@DavSmith: You don't need to specify the name of the focexec, it's available from &FOCFOCEXEC. For the full path you can probably use that variable with WHENCE FOCEXEC, but I'm not so sure you can get the result of that in a DM variable.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
October 19, 2011, 12:15 PM
DavSmith
Wep, I hear you on the &FOCFOCEXEC. I tried using that 3 years ago when I built this but I was running into it not always automatically picking up the correct fex name. Could not figure out why, so I went with manually setting it.

You are right in that the Tomcat method will record every thing (unless you somehow explicitly ignore some fexes), for us, we wanted the ability to easily choose which fexes got recorded. We did not want sub/nested/included fexes, just the main one. Good luck on brushing up your Java!



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
October 19, 2011, 02:01 PM
njsden
&FOCFOCEXEC seems to be impacted by "where" you run your requests from .. Report Caster, MRE, Server Console, Developer Studio Text Editor, etc.

When running "myreport.fex", I can get values such as: "myreport", "_myreport", "_ADHOCREQ", "RC..." (I don't remember the last one).

Bottom line, I'm with you on the fact that it's better not to always rely on &FOCFOCEXEC and prefer to create our own &variable to track currently running procedures. That way we have some level of "control".

This message has been edited. Last edited by: njsden,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
October 19, 2011, 02:22 PM
DavSmith
Exactly, NJSDEN! Thanks for completing the follow through on that. I'm sure there is a reason the &FOCFOCEXEC variable is...uhhh...variable {groan}...I'm just not sure why.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
October 19, 2011, 03:10 PM
njsden
quote:
the &FOCFOCEXEC variable is...uhhh...variable


Big Grin



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
October 19, 2011, 04:16 PM
Francis Mariani
&FOCFEXNAME - "Returns the name of the FOCEXEC running even if it was executed using an EX command or a -INCLUDE command from within another FOCEXEC. This variable differs from the &FOCFOCEXEC variable because &FOCFOCEXEC returns the name of the calling FOCEXEC only."

Woohoo!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 19, 2011, 08:20 PM
njsden
quote:
Whoohoo!

I was getting excited too but after playing with the code below I got similar results as the ones I used to experience in the past Frowner :

TABLE FILE CAR
PRINT CAR
BY COUNTRY
HEADING
"FOCFEXNAME = &FOCFEXNAME"
"FOCFOCNAME = &FOCFOCEXEC"
END


Reporting Server

- From within Dev.Studio text editor:
FOCFEXNAME = myreport.fex 
FOCFOCNAME = _myreport.fex 


- From Dev.Studio explorer:
FOCFEXNAME = MYREPORT 
FOCFOCNAME = _myreport 


- From Report Caster:
FOCFEXNAME = myapp/myreport 
FOCFOCNAME = RCASTER  


MRE

- From Dev.Studio text editor:
FOCFEXNAME = ADHOCRQ  
FOCFOCNAME = _ADHOCRQ 


- From Dev.Studio explorer:
FOCFEXNAME = MYREPORT 
FOCFOCNAME = _myreport 


- From Report Caster:
FOCFEXNAME = MYREPORT 
FOCFOCNAME = RCASTER  



I didn't try playing with -INCLUDE, EX or EXEC or running the report from the MRE applet interface but I can already see that the value of those variables is not always consistent across environments.

One can obviously create a small function to "normalize" the value of FOCFEXNAME, making it always lowercase, replacing ".fex" with nothing and getting its second token if "/" is found in the name but still makes me uncomfortabe having to manipulate it.

Whether or not one chooses to go with FOCFEXNAME it's probably a matter of personal preference, or lack thereof in my particular case Wink

This message has been edited. Last edited by: njsden,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
October 20, 2011, 03:47 AM
Wep5622
And then there's the issue with having two fexes with the same name in different paths. If you log just the fex-name, how do you know which of the two got called?

It would seem to me that it would be useful to have such information available in a DM variable or two. As proven by you guys, that's currently not (reliably) the case.

I think we'd want two (sets of) variables, like we currently have, but more reliable:

In web-development I'm also used to having access to the HTTP referrer, an HTTP request where the current request was linked from. In WebFOCUS, that would be quite useful to identify parents of drilldown fexes, the HTMLFORM that launched the current fex, etc.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
October 20, 2011, 09:46 AM
DavSmith
Just to add to the fun, there is another variable called &FOCINCLUDE to add to the mix. But, it too, has problems.

All 3 variable have been discussed before here.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle