Running reports automatically

Any and all non-support discussions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Running reports automatically

Postby cbsys » Thu Jul 18, 2013 4:25 am

Hi,

Is there a way of running reports automatically and outputting the results to a file/csv or email ?
Obviously i can use cron, but i would like the output of the report to go to a file and then emailed to me
cbsys
 
Posts: 54
Joined: Wed Nov 09, 2011 11:09 am

Re: Running reports automatically

Postby DomeDan » Thu Jul 18, 2013 6:14 am

Hi again!

You can write a one line command to do this for you and put that in crontab, something like this:

Code: Select all
DATE=`date +'%Y-%m-%d'`; \
FILE="/tmp/Agent_Performance_$DATE.csv"; \
wget http://127.0.0.1/vicidial/AST_agent_performance_detail.php --post-data "query_date=$DATE&end_date=$DATE&group[]=--ALL--&user_group[]=--ALL--&shift=ALL&file_download=1" --http-user=1234 --http-passwd=1234 -O $FILE ; \
echo "Hello" | mail -s "Agent Performance $DATE" -a $FILE foo@bar.com

and you can also add "; rm $FILE" to clean up afterwards
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden

Re: Running reports automatically

Postby cbsys » Thu Jul 18, 2013 6:17 am

Thanks, thats great!
cbsys
 
Posts: 54
Joined: Wed Nov 09, 2011 11:09 am

Re: Running reports automatically

Postby williamconley » Tue Aug 13, 2013 1:24 am

OK, That was cool. Elegant even.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Running reports automatically

Postby DomeDan » Tue Aug 13, 2013 3:02 am

Thank you for those words! :D
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden

Re: Running reports automatically

Postby Nefariousparity » Sat May 31, 2014 11:56 pm

I hate to bring up an old thread, but how would we do a date range with this, and that would be ultimate!

**Edit** Eureka!!

Example

DATE1=`date +'%Y-%m-%d' -d last-week`; \
DATE2=`date +'%Y-%m-%d'`; \
FILE="/tmp/Agent_Performance_$DATE.csv"; \
wget http://***.***.***.***/vicidial/AST_age ... detail.php --post-data "query_date=$DATE1&end_date=$DATE2&group[]=1000--ALL--&user_group[]=Dancers&user_group[]=Singers&shift=ALL&file_download=1" --http-user=Jesus_El_Christo --http-passwd=kittens -O $FILE ; \
echo "Hello, here is your kitten dance report" | mail -s "Agent Performance $DATE" -a $FILE askme@please.com

Date Reference page for changes to even this, enjoy!
|| DB Schema Version: 1609 || Asterisk 11.25.1-vici || BUILD: 190902-0839 ||VERSION: 2.14-718a||SVN: 3133||10xTelephony||1x Database||1x Slave||1x Web||1x Archive||ViciBox v.8.0.1
Nefariousparity
 
Posts: 327
Joined: Wed Sep 12, 2012 7:01 pm

Re: Running reports automatically

Postby Alex » Wed Mar 01, 2017 8:29 am

Hello. Can someone guide me in sending the report automatically through mail please.
Also can you please explain what need to be done with the above code.
Thanks in advance.
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby mflorell » Wed Mar 01, 2017 8:45 am

There is no simple web-gui way to do this currently.

We did create the AST_email_web_report.pl script to do this, and that script is included in the VICIdial codebase, but you need to copy and customize it for every report that you wish to run, as well as putting an entry for it in the crontab.

On our unfunded development list is a web-gui utility to automate emailing of reports, but it's pretty far down the list, so we have no estimated completion date.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby Alex » Wed Mar 01, 2017 11:38 am

Hi Matt,
I can customize for my requirement if you can point out the changes please for one sample report.
Adding in crontab i can do myself.
Thanks for your assistance.
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby Alex » Sat Mar 04, 2017 5:40 am

Hi Matt,

I have mentioned the changes made to the /usr/share/astguiclient/AST_email_web_report.pl file below

1. $location = "http://6666:vicibox7\@192.168.1.100/vicidial/AST_agent_performance_detail.php?DB=\\&query_date=$shipdate\\&end_date=$shipdate\\&group[]=--ALL--\\&user_group[]=--ALL--\\&shift=ALL\\&SUBMIT=SUBMIT";

2. `/usr/bin/wget --auth-no-challenge --http-user=6666 --http-password=vicibox7 --output-document=/tmp/X$HTMLfile $location `;

3.
print "allowed run time options:\n";
print " [--quiet] = quiet\n";
print " [--test] = test\n";
print " [--debug] = debugging messages\n";
print " [--debugX] = Super debugging messages\n";
print " [--remove-images] = Will parse through the output file and remove any images in the HTML\n";
print " [--remove-links] = Will parse through the output file and remove any links in the HTML\n";
print " [--email-subject=VICIDIAL Report] = Email message subject, which will be followed by the date of the report\n";
print " [--email-list=alexxyz@gmail.com] = send email results to these addresses\n";
print " [--email-sender=alexabcd@gmail.com] = sender for the email results\n";

### print " [--email-sender=vicidial@localhost] = sender for the email results\n";
print " [--date=today] = date override, can also use 'today' and 'yesterday'\n";
print " [--begin-date=YYYY-MM-DD] = begin date override, can also use '6days', '7days' and '30days', if not filled in will only use the --date\n";
print "\n";

When I compiled the .pl file i got the below response.

vicibox7:/srv/www/htdocs/vicidial # perl /usr/share/astguiclient/AST_email_web_report.pl
no command line options set, using defaults.

-- AST_email_web_report.pl --

This program is designed to wget a web report and email or FTP it.

Running Report
http://6666:vicibox7@192.168.1.100/vici ... il.php?DB=\&query_date=2017-03-04\&end_date=2017-03-04\&group[]=--ALL--\&user_group[]=--ALL--\&shift=ALL\&SUBMIT=SUBMIT
--2017-03-04 16:01:28-- http://6666:*password*@192.168.1.100/vi ... 3-04&group[]=--ALL--&user_group[]=--ALL--&shift=ALL&SUBMIT=SUBMIT
Connecting to 192.168.1.100:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: ‘/tmp/XDISCH_SURVEY_20170304.txt’

[ <=> ] 12,238 --.-K/s in 0s

2017-03-04 16:01:28 (148 MB/s) - ‘/tmp/XDISCH_SURVEY_20170304.txt’ saved [12238]



Can you please help me, if I'm missing something here. Awaiting your kind response.
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby Alex » Sat Mar 04, 2017 5:44 am

I also tried with the below code which was mentioned above in earlier discussions.
I added this into new php file, sendreport.php & saved in /srv/www/htdocs/vicidial & complied it from CLI.

DATE=`date +'%Y-%m-%d'`; \
FILE="/tmp/Agent_Performance_$DATE.csv"; \
wget http://127.0.0.1/vicidial/AST_agent_per ... detail.php --post-data "query_date=$DATE&end_date=$DATE&group[]=--ALL--&user_group[]=--ALL--&shift=ALL&file_download=1" --http-user=1234 --http-passwd=1234 -O $FILE ; \
echo "Hello" | mail -s "Agent Performance $DATE" -a $FILE foo@bar.com

The CLI response is

DATE=`date +'%Y-%m-%d'`; \
FILE="/tmp/Agent_Performance_$DATE.csv"; \
wget http://192.168.1.100/vicidial/AST_agent ... detail.php --post-data "query_date=$DATE&end_date=$DATE&group[]=--ALL--&user_group[]=--ALL--&shift=ALL&file_download=1" --http-user=6666 --http-passwd=vicibox7 -O $FILE ; \
echo "Hello. Today's Report" | mail -s "Agent Performance $DATE" -a $FILE alexxyz@gmail.com
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby mflorell » Sat Mar 04, 2017 6:58 am

For the first one, you have to specify the emails for it to send something anywhere.

For the most recent post, I'm really not sure what you're trying to do there, are you familiar with programming in PHP?

As for automating reports, I'm actually working on a web GUI to do that right now. Hopefully it will be done by next week.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby mflorell » Sat Mar 04, 2017 11:49 pm

The "Automated Reports" feature has been added to svn/trunk.

Just upgrade, then go to System Settings and enable Automated Reports, then allow your user to modify automated reports and go to the Admin -> Automated Reports section.

Enjoy :)
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby Alex » Mon Mar 06, 2017 5:26 am

Hi Matt,
Thanks for your valuable support & adding new feature for reports. Great Work.

Now I'm able to receive mail for reports.
But the mail have attachment of html file not exported CSV report. How can we draw CSV report & also where to mention the data range for the report export?

The report URL what I have added in the field is http://192.168.1.100/vicidial/AST_agent_performance_detail.php

Thanks for assistance.
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby mflorell » Mon Mar 06, 2017 6:38 am

If you want a CSV download you would have to use the URL for that download link. You would also have to change the file extension in the Perl script to be .csv.

That's probably shomething I need to add to the new Automated Reports feature as well.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby Alex » Mon Mar 06, 2017 6:45 am

Hi Matt,
Thanks for the info. But unfortunately I'm not familiar with scripting & programming. So I will wait for your feature update.

Thank you very much for considering my request.
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby mflorell » Mon Mar 06, 2017 9:18 am

In svn/trunk, Automated Reports will now set the proper file extension automatically depending on the type of report you specify in the URL. Let me know if you run into any other issues with this.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby Alex » Wed Mar 08, 2017 6:26 am

Hi Matt,
Thanks for your info. Please confirm if we have to do the SVN upgrade again for availing this export feature.

Also my URL in "Report URL" field is as below. Please confirm if we have to make changes to URL for generating the reports into .CSV.

http://192.168.1.100/vicidial/AST_agent ... MIT=SUBMIT
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby mflorell » Wed Mar 08, 2017 6:29 am

Yes, you would need to do another svn/trunk upgrade.

As for your URL, if you want to use the "today", "yesterday" and other variables, you have to do it like the scripting variables, with the --A-- and --B-- flag surrounding them, so you would need to put "--A--today--B--" for it to properly replace the date. Believve it or not, we have clients with campaigns and in-groups that have "today" in them :)
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby Alex » Wed Mar 08, 2017 7:10 am

Dear Matt,
Thanks for your assistance. I upgraded the SVN trunk to 2696 successfully.

Only concern is what changes need to be made for sending the report into .CSV. Basically I need only .CSV report to be sent to receivers mail ID not the server report URL. Below is the URL I'm using here.

http://192.168.1.100/vicidial/AST_agent ... MIT=SUBMIT

Awaiting your kind response.
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby mflorell » Wed Mar 08, 2017 7:16 am

Start by copying the URL from the report page for the DOWNLOAD link, then replace the dates. The URL for the DOWNLOAD link will have the download flag set in it to export to csv.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby Alex » Wed Mar 08, 2017 7:54 am

Hi Matt,
Wow... Finally...!! :D
I'm able to get mail with report.CSV now. Thank you very very very much for assisting me from the scratch regarding this topic. Really appreciate your great help.

Just a small info, there is no download link for Outbound Calling Report.. :( :o

Thanks a lot again.

Some Sample URLs,

For Inbound Report
http://192.168.1.100/vicidial/AST_CLOSE ... ived_data=

Agent Performance Report
http://192.168.1.100/vicidial/AST_agent ... -B--&group[]=RSTCamp&user_group[]=ADMIN&shift=ALL&DB=&show_percentages=&live_agents=&time_in_sec=&search_archived_data=&show_defunct_users=&breakdown_by_date=&report_display_type=TEXT&stage=&file_download=1


Enjoy..!!
Alex
 
Posts: 53
Joined: Mon Jun 01, 2015 1:37 am

Re: Running reports automatically

Postby mflorell » Wed Mar 08, 2017 9:09 am

Thanks for the post-back!

As for the Outbound calling report, that is correct, there are still a few reports that don't have any download links for CSV export.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Running reports automatically

Postby uselessinfoguru » Thu Sep 07, 2017 12:13 pm

i'm having trouble getting this
Code: Select all
/vicidial/call_report_export.php?DB=&run_export=1&ivr_export=&query_date=--A--7days--B--&end_date=--A--yesterday--B--&date_field=call_date&header_row=YES&rec_fields=NONE&custom_fields=NO&call_notes=NO&export_fields=STANDARD&campaign%5B%5D=1000&group%5B%5D=1001&group%5B%5D=1002&list_id%5B%5D=---ALL---&status%5B%5D=---ALL---&user_group%5B%5D=---ALL---&SUBMIT=SUBMIT
automatic script to email... i'm wondering is the date format correct?
VERSION: 2.14-736a | BUILD: 200204-2336| Cluster Setup
uselessinfoguru
 
Posts: 74
Joined: Tue Jul 25, 2017 10:27 pm

Re: Running reports automatically

Postby williamconley » Thu Sep 07, 2017 2:18 pm

uselessinfoguru wrote:i'm having trouble getting this
Code: Select all
/vicidial/call_report_export.php?DB=&run_export=1&ivr_export=&query_date=--A--7days--B--&end_date=--A--yesterday--B--&date_field=call_date&header_row=YES&rec_fields=NONE&custom_fields=NO&call_notes=NO&export_fields=STANDARD&campaign%5B%5D=1000&group%5B%5D=1001&group%5B%5D=1002&list_id%5B%5D=---ALL---&status%5B%5D=---ALL---&user_group%5B%5D=---ALL---&SUBMIT=SUBMIT
automatic script to email... i'm wondering is the date format correct?

Perhaps if you had some dates in there, I could answer. but I can't begin to determine whether the formatting of a date that isn't in the URL is correctly formatted ... in the URL.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Running reports automatically

Postby uselessinfoguru » Fri Sep 08, 2017 9:28 am

i was thinking the --A--7days--B-- and --A--yesterday--B-- might translate from the vici admin auto reports page like it does in the webforms
VERSION: 2.14-736a | BUILD: 200204-2336| Cluster Setup
uselessinfoguru
 
Posts: 74
Joined: Tue Jul 25, 2017 10:27 pm

Re: Running reports automatically

Postby williamconley » Sat Sep 09, 2017 5:29 pm

uselessinfoguru wrote:i was thinking the --A--7days--B-- and --A--yesterday--B-- might translate from the vici admin auto reports page like it does in the webforms

Wouldn't that be fairly easy to test?
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Running reports automatically

Postby uselessinfoguru » Sat Sep 16, 2017 4:06 pm

I'm not receiving the email when i test, and I'm not sure what log file to log at to see what went wrong.
VERSION: 2.14-736a | BUILD: 200204-2336| Cluster Setup
uselessinfoguru
 
Posts: 74
Joined: Tue Jul 25, 2017 10:27 pm

Re: Running reports automatically

Postby williamconley » Sat Sep 16, 2017 7:51 pm

uselessinfoguru wrote:I'm not receiving the email when i test, and I'm not sure what log file to log at to see what went wrong.


Code: Select all
tail -50 /var/log/mail.info
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Running reports automatically

Postby uselessinfoguru » Mon Sep 18, 2017 8:50 am

ty, Mr. William, you are a godsend.
VERSION: 2.14-736a | BUILD: 200204-2336| Cluster Setup
uselessinfoguru
 
Posts: 74
Joined: Tue Jul 25, 2017 10:27 pm

Re: Running reports automatically

Postby uselessinfoguru » Tue Sep 26, 2017 10:38 am

yes it did work, the date syntax carries over correctly... it just didn't give me the report i wanted, if gave me outbound calls, i wanted inbound
VERSION: 2.14-736a | BUILD: 200204-2336| Cluster Setup
uselessinfoguru
 
Posts: 74
Joined: Tue Jul 25, 2017 10:27 pm


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 70 guests