Thread Info
  • State Suggested Answer
  • Locked Locked
  • Replies 5 replies
  • Answers 1 answer
  • Subscribers 29 subscribers
  • Views 1076 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Web Usage Details Reporting for XG Home Firewall

Patrick Breitenbeck

I am newly using XG Home Firewall (SFOS 17.5.10 MR-10), but I have found the reporting to be inadequate. I have taken a backup copy of the corporate database from the advanced command shell and transferred it to my computer using Putty SCP. I restored a copy of the Postgres database into a new corporate database on my computer. I am using pgAdmin4 to view the tables and functions in pg_proc. I located the "Web Detail Report" of interest using this SQL query "select * from public.tblreport where title = 'Web Detail Report';". The query column in the public.tblreport table shows this query "select substring(CAST(date_trunc(''second'',to_timestamp("5mintime")) AS varchar),0,20) as time, username, "domain", url, category, usergroup,hostipv6 as host,content,categorytype,application,ruleid,activityname from {4} [[ and eduusergroup in ({7}) ]] order by {5} {6} limit {3} OFFSET {2}" for the "Web Detail Report" row. It also shows a value of "available_web_access_log_details" for the tablename column for this same row. However, cannot locate the "available_web_access_log_details" table anywhere in the corporate database. I am also seeing "Select web_usage_proc_012();" in the reportdb.log, but I am unable to locate the the web_usage_proc_012() function or procedure in any of the tables, including the pg_proc system catalog. Where can I locate the available_web_access_log_details table and the web_usage_proc_012() function or procedure to be able to create my own custom report offline from my computer? Thanks in advance.



This thread was automatically locked due to age.
Parents Reply Children
  • 0 in reply to rfcat_vk

    I have been using the XG Home Firewall (SFOS 17.5.10 MR-10) for about a week now. Unfortunately, I have found the reporting to be inadequate and would like to perform additional reporting directly from a copy of the XG firewall Postgres database. 

    I am struggling to locate the underlying tables, views, functions or procedures that drive the "Web Detail Report" (report of interest) that I located in the public.tblreport table. Steps taken so far.

    1. Took backup copy of the corporate database (psql text file dump) from the advanced command shell and transferred it to my computer using Putty SCP.
    2. Restored a copy of the database into a new Postgres corporate database on my computer.
    3. Used pgAdmin4 to view all of the tables and functions in pg_proc.
    4. Located the "Web Detail Report" of interest using this SQL query
      1. select * from public.tblreport where title = 'Web Detail Report'
    5. For this report, it shows the following value for a column named query
      1. select substring(CAST(date_trunc(''second'',to_timestamp("5mintime")) AS varchar),0,20) as time
        , username, "domain", url, category, usergroup,hostipv6 as host,content,categorytype,application,ruleid,activityname
        from {4} [[ and eduusergroup in ({7}) ]]
        order by {5} {6}
        limit {3}
        OFFSET {2}

    The above query is parameterized, so I am unable to determine the tables that are involved.

    The "Web Detail Report" row also shows a value of "available_web_access_log_details" for the tablename column; however, I was unable to  locate this table anywhere in the corporate database. The reportdb.log also showed "select  web_usage_proc_012()", so I tried to locate the web_usage_proc_012 function or procedure in the corporate database, but could not locate it either.

    Where can I locate the available_web_access_log_details table and the web_usage_proc_012() function or procedure in the XG Postgres database to be able to create my own custom report from an offline copy of the XG home firewall database?

    Thanks in advance.

    -- Pat B.

  • 0 in reply to Patrick Breitenbeck

    Hi Pat,

    you appear to be doing the report generation the hard way.

    What additional information are you trying colocate that is not available in the XG reports?

    You might like to connnect to the XG CM and use the CFR (Central Reporting) server it is a free and holds 7 days of history.

    XG reports from the XG are not very good and there are a number of threads asking for major upgrade to UTM (SG) standard in the very least.

    Ian

  • 0 in reply to Patrick Breitenbeck

    Another approach would be to build your own Reports with a third party solution + Syslog. 

    XG can send all information via Syslog to a third party solution and generate reports there.