4.9 Question- Scheduled Reports

(34 posts) (3 voices)
  1. nsepetys, Member

    Hello myDBR Representative,

    I just saw the new release of v.4.9. As always, we're excited over here about the new features. In particular, we're looking at the scheduler which might fit into our workflow well. Currently we have a cron managed through the standard job scheduler method (using linux). Offhand, how easy would it be to drop our existing crons into the scheduler.php file and pick up where we left off using myDBR's scheduler? I understand the initial call to scheduler.php may need to run every minute with the number of oddly scheduled myDBR crons we have running during the day, but outside of that what would it take to import the commands we have going? We have dozens of jobs running daily so I just want to make sure that this transition is practical.

    Thanks,
    Noah

  2. myDBR Team, Key Master

    Noah,
    what the new scheduled tasks feature does, it provides a method defining the scheduled report runs within myDBR UI. So you no longer need to edit cron jobs to add a scheduled report run, you can do it directly from the UI.

    You might not want to run the scheduler every minute, just see what the suitable interval for your cron jobs now have (every 10 minutes, every half an hour or every hour). Try to keep the cron interval bigger than your slowest report as myDBR logs the report to be run after it has successfully executed it.

    myDBR UI uses the cron syntax so you can transfer the current pretty much tasks as is.

    --
    myDBR Team

  3. nsepetys, Member

    Good to know. Thanks!

  4. ziuras, Member

    howto debug scheduler?
    I have report which sends emails and if i run it manualy it work.
    I have defined scheduler job in /etc/crontab with wget --no-check-certificate ....
    i have defined scheduler task in mydbr
    00 15 * * * https://report.kmv.local/index.php?m=11 2017.06.17 15.00.00
    i have created local user scheduler in mydbr and configured environment.
    but i dont reiceveid email.

    wh

  5. myDBR Team, Key Master

    Hi,
    the https://report.kmv.local/index.php?m=11 URL is not a valid URL to a report (it's a URL to myDBR folder). Could you check the URL?

    --
    myDBR Team

  6. ziuras, Member

    Ups, my mistake.
    But question is still active - is there any log?

  7. ziuras, Member

    my report link is:
    https://report.kmv.local/report.php?r=136&m=29&h=00837ba87fd905c3bb97dbdc67840965702ac967
    report is without parameters. when i run it from browser its ok.
    but scheduler does not run. i think about scheduler user. ho can i test it?
    i can not login with scheduler user

    i run from console:
    wget --no-check-certificate -O - -t 1 https://report.kmv.local/scheduler.php

    --2017-06-19 10:04:59-- https://report.kmv.local/scheduler.php
    Ieškoma report.kmv.local (report.kmv.local)... 10.18.156.251
    Jungiamasi prie report.kmv.local (report.kmv.local)|10.18.156.251|:443... prisijungta.
    ĮSPĖJIMAS: certificate common name ‘report’ doesn't match requested host name ‘report.kmv.local’.
    HTTP užklausa išsiųsta, laukiama atsakymo... 200 OK
    Dydis: 0 [text/html]
    Saving to: ‘STDOUT’

    [ <=> ] 0 --.-K/s per 0s

    2017-06-19 10:04:59 (0,00 B/s) - written to stdout [0/0]

  8. ziuras, Member

    in settings - Authentication / security = Active directory.
    Scheduled tasks - users (only local users) = dba

    and i can not login into mydbr with dba user.

  9. myDBR Team, Key Master

    ĮSPĖJIMAS: certificate common name ‘report’ doesn't match requested host name ‘report.kmv.local’.
    HTTP užklausa išsiųsta, laukiama atsakymo... 200 OK

    If your certificate does not match the hostname, fix the certificate or use the the "--no-check-certificate" flag in wget.

    in settings - Authentication / security = Active directory.
    and i can not login into mydbr with dba user.

    Your defalt authentication is set to Active Directory. If you want to override it and use myDBR authentication instead, add '&local=1' to the login URL.

    --
    myDBR Team

  10. ziuras, Member

    About &login=1 i forget :)

    crontab:
    0,30 * * * * root /usr/bin/wget --no-check-certificate -O - -q -t 1 https://report.kmv.local/scheduler.php > /dev/null 2>&1

    schduled tasks in mydbr:
    00,30 * * * * https://report.kmv.local/report.php?r=136&m=29&h=00837ba87fd905c3bb97dbdc67840965702ac967 2017.06.19 11.00.00

    setings:
    Scheduled tasks
    Allowed IP addresses (one per row)
    127.0.0.1
    10.18.156.251
    Run scheduled reports under username
    dba

    with dba login i can run this report:
    https://report.kmv.local/report.php?r=136&m=29&h=00837ba87fd905c3bb97dbdc67840965702ac967
    Sent 1 mail with no errors
    Sent 1 mail with no errors
    Sent 1 mail with no errors
    Sent 1 mail with no errors
    Sent 1 mail with no errors
    Sent 1 mail with no errors

    6 mail was send.
    but nothing in scheduled time :(
    and there i miss logs, where i can to see what is going on.

  11. myDBR Team, Key Master

    Try putting the schedule in myDBR bit before the actual cron (for example 59,29). The scheduler will look for the jobs that are to be run. You can also check from the scheduler entry when was the last time the report was run.

    You can also run the wget command from the command line (once the report is due) and see if threre are any errors.
    --
    myDBR Team

  12. ziuras, Member

    SELECT * FROM [mydbr].[dbo].[mydbr_scheduled_tasks]
    id description url timing last_run disabled created_at
    1 https://report.kmv.local/report.php?r=136&m=29&h=00837ba87fd905c3bb97dbdc67840965702ac967 00,30 * * * * NULL 0 2017-06-19 09:56:38.693

    Last run = NULL

  13. ziuras, Member

    wget --no-check-certificate -O - -t 1 https://report.kmv.local/scheduler.php
    --2017-06-19 11:22:06-- https://report.kmv.local/scheduler.php
    Ieškoma report.kmv.local (report.kmv.local)... 10.18.156.251
    Jungiamasi prie report.kmv.local (report.kmv.local)|10.18.156.251|:443... prisijungta.
    ĮSPĖJIMAS(Warn): cannot verify report.kmv.local's certificate, issued by ‘/C=LT/ST=Lithuania/L=Kaunas/O=Celsis/OU=IT/CN=report.kmv.local/emailAddress=log@aa.aa’:
    Rastas savo-pasirašytas sertifikatas(found self signed...).
    HTTP užklausa išsiųsta, laukiama atsakymo... 200 OK
    Dydis(size): 0 [text/html]
    Saving to: ‘STDOUT’

    [ <=> ] 0 --.-K/s per 0s

    2017-06-19 11:22:06 (0,00 B/s) - written to stdout [0/0]

  14. myDBR Team, Key Master

    Add

    $mydbr_defaults['embed_url']['CURLOPT_SSL_VERIFYPEER'] = false;

    into your user/defaults.php and try again.

    --
    myDBR Team

  15. ziuras, Member

    nothing

  16. myDBR Team, Key Master

    So what is the output of the wget run from the command line after you added the option into the defaults.php?

    --
    myDBR Team

  17. ziuras, Member

    wget --no-check-certificate -O - -t 1 https://report.kmv.local/scheduler.php
    --2017-06-19 12:48:06-- https://report.kmv.local/scheduler.php
    Ieškoma(finding) report.kmv.local (report.kmv.local)... 10.18.156.251
    Jungiamasi prie report.kmv.local (report.kmv.local)|10.18.156.251|:443... prisijungta(connected).
    ĮSPĖJIMAS(Warn): cannot verify report.kmv.local's certificate, issued by ‘/C=LT/ST=Lithuania/L=Kaunas/O=Celsis/OU=IT/CN=report.kmv.local/emailAddress=log@aa.aa’:
    Rastas savo-pasirašytas sertifikatas(found self-signed..).
    HTTP užklausa išsiųsta, laukiama atsakymo... 200 OK
    Dydis: 0 [text/html]
    Saving to: ‘STDOUT’

    [ <=> ] 0 --.-K/s per 0s

    2017-06-19 12:48:06 (0,00 B/s) - written to stdout [0/0]

    ~~~~~~~~~~~~~
    from sql LastRun=NULL

  18. ziuras, Member

    10.18.156.220 - - [19/Jun/2017:12:49:48 +0300] "GET /lib/cron.php?next=0+1+*+*+* HTTP/1.1" 200 448 "https://report.kmv.local/index.php?a=scheduled&m=1" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.220 - - [19/Jun/2017:12:49:51 +0300] "GET /lib/cron.php?next=59%2C29+*+*+*+* HTTP/1.1" 200 444 "https://report.kmv.local/index.php?a=scheduled&m=1" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.220 - - [19/Jun/2017:12:49:56 +0300] "GET /lib/cron.php?next=5%2C29+*+*+*+* HTTP/1.1" 200 447 "https://report.kmv.local/index.php?a=scheduled&m=1" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.220 - - [19/Jun/2017:12:49:56 +0300] "GET /lib/cron.php?next=50%2C29+*+*+*+* HTTP/1.1" 200 445 "https://report.kmv.local/index.php?a=scheduled&m=1" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.220 - - [19/Jun/2017:12:49:57 +0300] "POST /index.php?a=scheduled&m=1 HTTP/1.1" 302 2556 "https://report.kmv.local/index.php?a=scheduled&m=1" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.220 - - [19/Jun/2017:12:49:57 +0300] "GET /index.php?a=scheduled HTTP/1.1" 200 3306 "https://report.kmv.local/index.php?a=scheduled&m=1" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.220 - - [19/Jun/2017:12:49:57 +0300] "GET /user/userstyle.css?1497865797&v=3311 HTTP/1.1" 200 540 "https://report.kmv.local/index.php?a=scheduled" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.220 - - [19/Jun/2017:12:49:58 +0300] "GET /lib/cron.php?next=0+1+*+*+* HTTP/1.1" 200 448 "https://report.kmv.local/index.php?a=scheduled" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0"
    10.18.156.251 - - [19/Jun/2017:12:50:05 +0300] "GET /scheduler.php HTTP/1.1" 200 2179 "-" "Wget/1.15 (linux-gnu)"

    10.18.156.251 - mydbr - report.kmv.local
    10.18.156.220 - my pc

  19. myDBR Team, Key Master

    OK,
    we've added another option to bypass the certificate in the scheduler. You should fix your certificate though. The web server rejects your calls as you have incorrect certificate installed.

    Run the updater and add following line to user/defaults.php:

    $mydbr_defaults['embed_url']['CURLOPT_SSL_VERIFYHOST'] = false;

    --
    myDBR Team

  20. ziuras, Member

    the same.

    wget --no-check-certificate -O - -t 1 https://report.kmv.local/scheduler.php
    --2017-06-19 13:19:17-- https://report.kmv.local/scheduler.php
    Ieškoma report.kmv.local (report.kmv.local)... 10.18.156.251
    Jungiamasi prie report.kmv.local (report.kmv.local)|10.18.156.251|:443... prisijungta.
    ĮSPĖJIMAS: cannot verify report.kmv.local's certificate, issued by ‘/C=LT/ST=Lithuania/L=Kaunas/O=Celsis/OU=IT/CN=report.kmv.loca l/emailAddress=log@bb.bb’:
    Rastas savo-pasirašytas sertifikatas.
    HTTP užklausa išsiųsta, laukiama atsakymo... 200 OK
    Dydis: 0 [text/html]
    Saving to: ‘STDOUT’

    [ <=> ] 0 --.-K/s per 0s

    2017-06-19 13:19:17 (0,00 B/s) - written to stdout [0/0]

  21. myDBR Team, Key Master

    Hi,
    make sure you have both options (CURLOPT_SSL_VERIFYPEER and CURLOPT_SSL_VERIFYHOST) set in user/defaults.php. Make also sure that the reports in scheduler are due to be run.

    The messages you see from the command line are normal messages what wget shows. The warnings shown are for your certificate, but as you are using the --no-check-certificate flag, these are just warnings.

    We've tested the scheduler with invalid certificate and with the options set, the report should run.

    --
    myDBR Team

  22. myDBR Team, Key Master

    We've added a debug option to the scheduler.php. if you want debug info from the scheduler run the scheduler with scheduler.php?debug=1 and the debug output is printed from the script.

    You can run the script from the command line:

    $ wget --no-check-certificate -t 1 https://report.kmv.local/scheduler.php -O scheduler_debug.txt

    --
    myDBR Team

  23. ziuras, Member

    root@report:/var/www/user# cat ./defaults.php
    <?php
    // This is used to override values in defaults.php at main level.

    $mydbr_defaults['page_title'] = 'myDBR Celsis';
    $mydbr_defaults['export']['wkhtmltopdf']['command']='/usr/local/bin/wkhtmltopdf';
    $mydbr_defaults['db_connection']['pooled_connections'] = true;
    $mydbr_defaults['embed_url']['CURLOPT_SSL_VERIFYPEER'] = false;
    $mydbr_defaults['embed_url']['CURLOPT_SSL_VERIFYHOST'] = false;

    /*
    Debug flag to determine server cookie problems.
    Enabling this may expose your database login information so use with caution

    $mydbr_defaults['debug_cookie'] = true;
    */
    root@report:/var/www/user#

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    root@report:/var/tmp# wget --no-check-certificate -t 1 https://report.kmv.local/scheduler.php?debug=1 -O scheduler_debug.txt
    --2017-06-20 08:31:47-- https://report.kmv.local/scheduler.php?debug=1
    Ieškoma report.kmv.local (report.kmv.local)... 10.18.156.251
    Jungiamasi prie report.kmv.local (report.kmv.local)|10.18.156.251|:443... prisijungta.
    ĮSPĖJIMAS: cannot verify report.kmv.local's certificate, issued by ‘/C=LT/ST=Lithuania/L=Kaunas/O=Celsis/OU=IT/CN=report.kmv.local/emailAddress=log@aa.bb’:
    Rastas savo-pasirašytas sertifikatas.
    HTTP užklausa išsiųsta, laukiama atsakymo... 200 OK
    Dydis: 0 [text/html]
    Saving to: ‘scheduler_debug.txt’

    [ <=> ] 0 --.-K/s per 0s

    2017-06-20 08:31:47 (0,00 B/s) - ‘scheduler_debug.txt’ saved [0/0]

    root@report:/var/tmp#

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    scheduler_debug.txt - empty, zero size

    Maybe it is something with ubntu, apache or php configuration?

  24. myDBR Team, Key Master

    What is the version and build number of your myDBR installation? You can see that in the About-dialog.

    --
    myDBR Team

  25. ziuras, Member

    myDBR 4.9.0 (build 3311)

  26. myDBR Team, Key Master

    Could you run the automatic updater so you will get the latest build, myDBR 4.9.0 (build 3331)

    --
    myDBR Team

  27. ziuras, Member

    after automatic update i get debug.txt:
    root@report:/var/tmp# cat ./scheduler_debug.txt
    2017-06-20 13:49:48: Scheduler start. Tasks defined: 1. cURL options: CURLOPT_SSL_VERIFYPEER, CURLOPT_SSL_VERIFYHOST
    2017-06-20 13:49:48: Task:1 https://report.kmv.local/report.php?r=136&m=29&h=00837ba87fd905c3bb97dbdc67840965702ac967. Last run Jun 19 2017 01:24:40:870PM > last scheduled run 2017-06-20 13:29:00. Skipping.
    2017-06-20 13:49:48: Scheduler complete
    root@report:/var/tmp#

    now i am waiting for 14:00 to see scheduler job :)

  28. ziuras, Member

    scheduled report:
    05 * * * * https://report.kmv.local/report.php?r=136&m=29&h=00837ba87fd905c3bb97dbdc67840965702ac967 2017.06.20 14.05.00
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    root@report:/etc# wget --no-check-certificate -t 1 https://report.kmv.local/scheduler.php?debug=1 -O scheduler_debug.txt
    --2017-06-20 14:05:46-- https://report.kmv.local/scheduler.php?debug=1
    Ieškoma report.kmv.local (report.kmv.local)... 10.18.156.251
    Jungiamasi prie report.kmv.local (report.kmv.local)|10.18.156.251|:443... prisijungta.
    ĮSPĖJIMAS: cannot verify report.kmv.local's certificate, issued by ‘/C=LT/ST=Lithuania/L=Kaunas/O=Celsis/OU=IT/CN=report.kmv.local/emailAddress=log@aa.bb’:
    Rastas savo-pasirašytas sertifikatas.
    HTTP užklausa išsiųsta, laukiama atsakymo... 200 OK
    Dydis: 364 [text/html]
    Saving to: ‘scheduler_debug.txt’

    100%[====================================================================================================================================================================================================>] 364 --.-K/s per 0s

    2017-06-20 14:05:46 (34,5 MB/s) - ‘scheduler_debug.txt’ saved [364/364]

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    root@report:/etc# cat ./scheduler_debug.txt
    2017-06-20 14:05:46: Scheduler start. Tasks defined: 1. cURL options: CURLOPT_SSL_VERIFYPEER, CURLOPT_SSL_VERIFYHOST
    2017-06-20 14:05:46: Task:1 https://report.kmv.local/report.php?r=136&m=29&h=00837ba87fd905c3bb97dbdc67840965702ac967. Last run Jun 20 2017 02:04:42:353PM > last scheduled run 2017-06-20 13:05:00. Skipping.
    2017-06-20 14:05:46: Scheduler complete
    root@report:/etc#

  29. ziuras, Member

    and lastRun = Null in sql database

  30. myDBR Team, Key Master

    and lastRun = Null in sql database

    What does this mean?

    Another note. Your dates seem to come out of the database is form "Jun 19 2017 01:24:40:870PM". Are you using FreeTDS in your server to access the database and have you configured the date format in FreeTDS's locales.conf?

    [default] date format = %Y-%m-%d %H:%M:%S
    You better open a support ticket in myDBR support email instead of continuing the discussion in the forums.

    --
    myDBR Team


Reply »

You must log in to post.