Excel reports

[LarryE]

Excel Reports – Is there a way to run the reports on a trigger rather than time/date?

[JoelHaggar]

You can't currently do this but we did add in the infrastructure to allow Modules to create their own event handlers so there is a plan to add in an event handler to trigger a report. We will probably add this in for Mango 3.0.

[LarryE]

Thanks Joel

[phildunlap]

Hi Larry,

Excel reports does have API endpoints to run a report, so it would be possible to write a script to invoke a report in the API through a process event handler.

[LarryE]

Phil, Could you give an example?

Thanks

[phildunlap]

For sure!

Here's my python script (adapted from this thread: https://infiniteautomation.com/forum/topic/2658/any-generic-guide-to-rest-api-python/3):

#!/usr/bin/python

import requests

s = requests.Session()
s.headers.update({'Accept': 'application/json', 'Accept-Encoding': 'gzip,deflate,sdch', 'password': 'asdf'})

#Show session headers
#print s.headers, "\n\n"

r = s.get('http://localhost/rest/v1/login/reportRunnerUser')
#r = s.get('http://localhost:8080/rest/v1/login/admin');
#Show headers sent
#print r.request.headers, "\n\n"

#Show Login response JSON
#print r.text, "\n\n"

#Remove password from headers
del s.headers["password"]

#you may need to set your X-Xsrf-Token header from the cookie, if so uncomment next line.
#s.headers["X-Xsrf-Token"] = s.cookies["XSRF-TOKEN"]

#Trigger the report
r = s.get('http://localhost/rest/v1/excel-reports/run/triggeredReportXid')
#print r.text, "\n\n"

r = s.get('http://localhost/rest/v1/logout')

So I...

1. Created a user named 'reportRunnerUser' who has password 'asdf' and permissions 'reportRunner'
2. Added 'reportRunner' to the edit permission field of the Excel Report, whose xid is 'triggeredReportXid'
3. Created a process event handler on my point that had an active command of python /home/mango/testRunExcelReport.py

Here's a much more complex script I wrote a while ago to run some set of reports on individual days for the past year or so (for inspirational purposes only):

import requests 
import datetime
import json
import time
from StringIO import StringIO
import re

class TZ(datetime.tzinfo) :
	def utcoffset(self, dt): return datetime.timedelta(hours=8)
class UTC(datetime.tzinfo) :
	def utcoffset(self, dt): return datetime.timedelta(hours=0)

baseUrl = "http://10.10.10.49"
excelReport = 'knownXid'
adminPassword = 'admin'
startDate = datetime.datetime(2017, 1, 17, tzinfo=TZ())
endDate = datetime.datetime(2017, 1, 19, tzinfo=TZ())
timestep = datetime.timedelta(days=1)

s = requests.Session()
headers = {'Accept': 'application/json', 
	'Accept-Encoding': 'gzip,deflate,sdch', 
	'password': adminPassword, 
	"User-Agent": "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.71 Safari/537.36", 
	"Host": "10.10.10.49", 
	"Referer": "http://10.10.10.49/swagger",
	"Accept-Language":"en-US,en;q=0.8",
	"Pragma": "no-cache",
	"Cache-Control": "no-cache",
	"Connection": "keep-alive"}

r = s.get(baseUrl + '/rest/v1/login/admin', headers=headers)
#print "Response headers: ", r.headers, "\n\n"

token = re.search("XSRF-TOKEN=([^; ]+)", r.headers["set-cookie"])
headers['Cookie'] = re.sub("Path=/", "", r.headers["set-cookie"])
headers["Cookie"] = re.sub(";,", ";", headers["Cookie"][:-1])
headers["X-Xsrf-Token"] = token.group(1)
headers["Content-Type"] = "application/json"
del headers["password"]
#print "\nPOST AUTH HEADERS: \n", headers, "\n\n"
#get the list of templates
r = s.get(baseUrl + '/rest/v1/excel-report-templates', headers=headers)
#print "\n QUERY REPORTS GOT RESPONSE:\n", r.text, "\n\n"
response = json.load(StringIO(r.text))
excelReportObj = None
for er in response["items"] :
	if er["xid"] == excelReport :
		excelReportObj = er 
if excelReportObj is None :
	print "Ah nuts, couldn't find the excel report specified."
	exit() 

#print json.dumps(excelReportObj)
epoch = datetime.datetime(1970, 1, 1, tzinfo=UTC())
def setReportForDate(dt) :
	dt1 = int((dt-epoch).total_seconds()*1000)
#	print "dt1: ", dt1
	excelReportObj["timeSeries"][0]["startTimestamp"] = dt1
	dt2 = int(((dt + timestep) - epoch).total_seconds()*1000)
#	print "dt2: ", dt2
	excelReportObj["timeSeries"][0]["finishTimestamp"] = dt2
#	print "Report configured for start: %d  || and end: %d" % (excelReportObj["timeSeries"][0]["startTimestamp"],excelReportObj["timeSeries"][0]["finishTimestamp"])
#	print json.dumps(excelReportObj)
	return excelReportObj

reportCount = 0
while startDate < endDate :
	r = s.put(baseUrl + '/rest/v1/excel-report-templates/%d'%excelReportObj["id"], data=json.dumps(setReportForDate(startDate)), headers=headers)
#	print "\n", r.text, "\n\n"
	startDate = startDate + timestep
	s.get(baseUrl + '/rest/v1/excel-reports/run/%s'%excelReportObj["xid"], headers=headers)
	reportCount += 1
	time.sleep(1)

print "Queued and ran %d reports consider running 'scp Mango/web/modules/excelReports/web/report-data/* C:\\ReportData\\" %(reportCount,)

[LarryE]

Thanks so much Phil I'll give it a try.