Running reports automatically

[cbsys]

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

[DomeDan]

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

[cbsys]

Thanks, thats great!

[williamconley]

OK, That was cool. Elegant even.

[DomeDan]

Thank you for those words!

[Nefariousparity]

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!

[Alex]

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.

[mflorell]

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.

[Alex]

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]

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]

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

[mflorell]

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]

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

[Alex]

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.

[mflorell]

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.

[Alex]

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.

[mflorell]

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.

[Alex]

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

[mflorell]

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

[Alex]

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.

[mflorell]

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.

[Alex]

Hi Matt,
Wow... Finally...!!
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..

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..!!

[mflorell]

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.

[uselessinfoguru]

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?

[williamconley]

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.

[uselessinfoguru]

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

[williamconley]

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?

[uselessinfoguru]

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.

[williamconley]

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

[uselessinfoguru]

ty, Mr. William, you are a godsend.

[uselessinfoguru]

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