(Last updated November 8, 2019)
Below are sample PowerShell and Shell scripts that let you quickly automate the process of exporting data from AquaCUE. They are intended to show you how to handle interaction with the EDS APIs and do not include error handling.
PowerShell Sample
ps_sample.ps1 calls the EDS v1 Consumption endpoint to get a report of the total consumption along with the number of meters reporting >0 flow in your system.
Shell samples
Range.sh lets you quickly use the Export Data Service API to get reads over a range of dates.
Flow.sh lets you quickly use the Export Data Service API to get consumption (flow) data for your meters.
Leak.sh lets you quickly use the Export Data Service API to get a list of meters with leaks, along with the leak start date and the current leak rate.
Read.sh lets you quickly use the Export Data Service API to get billing read data for a selection of meters or all of the meters in your facility.
NOTE TO AQUACUE API USERS IN CANADA
In compliance with Canadian cross-border data regulations, your facility data is or already has been moved to Canada. As a result, if you login to AquaCUE via aquacue.ca you should adjust all of your API calls to address endpoints at api.beaconama.ca.
For example, change
https://api.beaconama.net/v1/eds/read/
to
https://api.beaconama.ca/v1/eds/read/
This change affects all API endpoint calls for facilities that login to beaconama.ca.
Making API Calls with PowerShell
PowerShell is a task automation scripting language from Microsoft that simplifies making REST API calls and handling JSON and CSV data. Powershell runs on Windows, MacOS and Linux.
Before running Powershell scripts:
Windows
- Download and install the latest version of PowerShell. (Currently, the latest version is 6.0.0-rc.2)
- Add C:\Program Files\PowerShell\6.0.0.-rc.2\LK to the PATH environment variable.
- Open Command Prompt and start PowerShell by typing pwsh.
- Next type $PSVersionTable.PSVersion
to verify that you are running the latest version of PowerShell.You should see:
| Major | Minor | Patch | PreReleaseLabel | BuildLabel |
| 6 | 0 | 0 | rc |
MacOS
- Download and install the brew package manager from https://brew.sh.
- Once installed, type brew cask install powershell into a command-line. This installs PowerShell.
For more details on using PowerShell, click here.
Using ps_sample.ps1 to call the EDS API v1 Consumption endpoint
Copy and paste the script below into a file and name it, for example ps_sample.ps1. This script is run from the command line and requires you to provide four parameters: start_date, end_date, username and password.
- Enter a start date and, if desired, time in ISO format on Line 12 after $start_date=“YYYY-MM-DD” or $start_date=“YYYY-MM-DDThh:mm:ssZ”.
- Enter an end date and, if desired, time in ISO format on Line 12 after $end_date=“YYYY-MM-DD” or $end_date=“YYYY-MM-DDThh:mm:ssZ”.
- Enter $username and $password with your user credentials.
- Enter the desired unit of measure on line 12.
- If desired, change the resolution to one of quarter_hourly, hourly or monthly.
- Start COMMAND PROMPT (Windows) or from Terminal (MacOS) type pwsh and press ENTER.
- Run ps_sample.ps1.
[sourcecode lang=”powershell”]
param (
[Parameter(Mandatory=$true)][string]$start_date,
[Parameter(Mandatory=$true)][string]$end_date,
[Parameter(Mandatory=$true)][string]$username,
[Parameter(Mandatory=$true)][string]$password
)
$encoded_creds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("$($username):$($password)"))
$basic_auth = "Basic $($encoded_creds)"
$uri = ‘https://api.beaconama.net/v1/eds/consumption’
$headers = @{‘Authorization’=$basic_auth;’Content-Type’=’application/x-www-form-urlencoded’}
$params = @{startDate=$start_date;endDate=$end_date;unit=’cubicmeter’;outputFormat=’CSV’;headerColumns=’Flow_Time,Flow’;resolution=’daily’}
$r = Invoke-RestMethod -Uri $uri -Headers $headers -Method POST -Body $params
$status_url = ‘https://api.beaconama.net’+$r.statusUrl
While ($true) {
$r2 = Invoke-RestMethod -Uri $status_url -Headers $headers
if ($r2.state -eq ‘done’) {
$report_url = ‘https://api.beaconama.net’ + $r2.reportUrl
$out_file = "report.csv"
$result = Invoke-WebRequest -Uri $report_url -Headers $headers -MaximumRedirection 0 -ErrorAction SilentlyContinue
if ($result.StatusCode -eq 302) {
Invoke-WebRequest -Uri $result.Headers.Location -OutFile $out_file
Write-Output "Report created in file: $($out_file)"
}
Break
} else {
Write-Output $r2.state
Start-Sleep -Seconds 1
}
#
#
#
[/sourcecode]
To run the script named ps_sample.ps1 on Windows type:
[sourcecode lang=”bash”]
c:\ pwsh -ExecutionPolicy Bypass -File ps_sample.ps1 -start_date 2017-12-01 -end_date 2017-12-19 -username ZZZ -password YYY
[/sourcecode]
To run the script named ps_sample.ps1 on MacOS type:
[sourcecode lang=”bash”]
$ pwsh ./ps_sample.ps1 -start_date 2017-12-01 -end_date 2017-12-19 -username ZZZ -password YYY
[/sourcecode]
The scripts will create a CSV file called report.csv. After the script has completed its run, you can review the content of this file. It will look like this:
[sourcecode lang=”bash”]
"Flow_Time","Flow" "2017-12-01","6140.00000"
"2017-12-02","5652.40000"
"2017-12-03","5459.10000"
"2017-12-04","5289.20000"
"2017-12-05","5902.60000"
"2017-12-06","5426.50000"
"2017-12-07","5807.80000"
[/sourcecode]
In PowerShell, if you are behind a proxy server provide the following parameters: start_date, end_date, your AquaCUE username and password, and your proxy username and password. Also, pass the following information when making the network request using Invoke-RestMethod or Invoke-WebRequest:
[sourcecode lang=”powershell”]-Proxy ‘<server:port>’ -ProxyUseDefaultCredentials ‘<proxy password>'[/sourcecode]
See line 19 in the sample script below.
To run this version of ps_sample.ps1 behind a proxy server on Windows type:
[sourcecode lang=”powershell”]
c:\ pwsh -ExecutionPolicy Bypass -File ps_sample.ps1 -proxy ‘<server:port>’ -start_date 2017-12-01 -end_date 2017-12-19 -username ZZZ -password YYY
[/sourcecode]
To run this version of ps_sample.ps1 behind a proxy server on MacOS type:
[sourcecode lang=”powershell”]
$ pwsh ./ps_sample.ps1 -proxy ‘<server:port>’ -start_date 2017-12-01 -end_date 2017-12-19 -username ZZZ -password YYY
[/sourcecode]
[sourcecode lang=”powershell”]
param (
[Parameter(Mandatory=$true)][string]$start_date,
[Parameter(Mandatory=$true)][string]$end_date,
[Parameter(Mandatory=$true)][string]$username,
[Parameter(Mandatory=$true)][string]$password,
[Parameter(Mandatory=$true)][string]$proxy
)
$end_date = Get-Date -UFormat "%Y-%m-%d"
$end_date_for_file_name = Get-Date -UFormat "%Y%m%d-%H%m%S"
$out_file_name = "your-report-$($end_date)-report_$($end_date_for_file_name).csv"
$encoded_creds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("$($username):$($password)"))
$basic_auth = "Basic $($encoded_creds)"
$uri = ‘https://api.beaconama.net/v1/eds/consumption’
$headers = @{‘Authorization’=$basic_auth;’Content-Type’=’application/x-www-form-urlencoded’}
$params = @{startDate=$start_date;endDate=$end_date;unit=’cubicmeter’;outputFormat=’CSV’;headerColumns=’Flow_Time,Flow’;resolution=’daily’}
$r = Invoke-RestMethod -Uri $uri -Proxy $proxy -ProxyUseDefaultCredentials -Headers $headers -Method POST -Body $params
$headers = @{‘Authorization’=$basic_auth;’Content-Type’=’application/x-www-form-urlencoded’}
$status_url = ‘https://api.beaconama.net’+$r.statusUrl
While ($true) {
$r2 = Invoke-RestMethod -Uri $status_url -Proxy $proxy -ProxyUseDefaultCredentials -Headers $headers
if ($r2.state -eq ‘done’) {
$report_url = ‘https://api.beaconama.net’ + $r2.reportUrl
$out_file = "report.csv"
$result = Invoke-WebRequest -Uri $report_url -Proxy $proxy -ProxyUseDefaultCredentials -Headers $headers -MaximumRedirection 0 -ErrorAction SilentlyContinue
if ($result.StatusCode -eq 302) {
Invoke-WebRequest -Uri $result.Headers.Location -OutFile $out_file
Write-Output "Report created in file: $($out_file)"
}
Break
} else {
Write-Output $r2.state
Start-Sleep -Seconds 1
}
[/sourcecode]
Making API Calls with Shell Scripts
Shell scripts are another way to make automated calls to the Web Service APIs. Run these sample scripts on a UNIX-based OS such as Linux or Mac OS X or use Windows with an Open Source toolset such as Cygwin.
Be sure you are running CURL version 6.58.0 or later. Doing so ensures that the authorization header is not passed during the redirect to fetch the REPORT_URL.
TIP: When passing export parameters, e.g. Meter_ID=1234, either as query parameters or in the body of an HTTP POST, please be sure to URL encode them.
Before running any of these Shell scripts:
Linux and MacOS:
- Download and install the brew package manager from https://brew.sh.
- Once installed, type brew install jq into a command-line. This installs a JSON processor called jq.
Windows
- See GitHub for information on installing jq on Windows.
Using Range.sh
Get interval meter reads across a range of dates using this API call.- Copy and paste the script below into a file and name it Range.sh.
- Replace username:password on Line 2 with your AquaCUE user credentials separated by a colon (:). Be sure to include the quote (“) marks. For example, CREDS=”yourname:yourpassword”.
- Enter a start date and, if desired, time in ISO format on Line 4 after SDATE=“YYYY-MM-DD” or SDATE=“YYYY-MM-DDThh:mm:ssZ”
- Enter an end date and, if desired, time in ISO format on Line 5 after EDATE=“YYYY-MM-DD” or EDATE=“YYYY-MM-DDThh:mm:ssZ”
- Enter the desired parameter values on Line 9. For example,
• after resolution= enter any of daily, hourly or monthly
• after unit= enter any of acrefeet, ccf, cubicfeet, cubicmeter, gallons or liter - Locate the script on your hard drive and type the following into a command-line tool:./Range.sh
- Press ENTER to run the script. When it completes, copy and paste the results into a new file. Save the file and review it in a spreadsheet or other application that can read CSV files.
[sourcecode lang=”bash”]
#!/bin/bash
CREDS=’username:password’SDATE=’2017-07-31′
EDATE=’2017-07-31′
COLS=’Account_ID,Meter_ID,Endpoint_SN,Flow,Flow_Unit,Flow_Time’
URL=’https://api.beaconama.net/v1/eds/range’REQ=`curl -s -X POST -u$CREDS -d "resolution=daily&startDate=$SDATE&endDate=$EDATE&unit=cubicmeter&headerColumns=$COLS" $URL`
EDS_UUID=`echo $REQ | jq -r .edsUUID`# if status returns EDS_UUID, then every 10 seconds checks
# to see if the report is ready. When the export has been completed
# you can fetch the export report. Note the curl option -L is used to
# allow curl to automatically follow redirects.if [ ‘$EDS_UUID’ != ‘null’ ]
then
while true
do
sleep 10 # check status every 10 seconds.
REQ=`curl -s -X GET -u$CREDS https://api.beaconama.net/v1/eds/status/$EDS_UUID`
echo REQ=$REQ
REPORT_URL=`echo $REQ | jq -r .reportUrl`if [[ $REPORT_URL != ‘null’ ]]
thencurl -L -s -X GET -u$CREDS https://api.beaconama.net/$REPORT_URL -o $OUTFILE
break
fi
done
else
echo ‘ERROR: $REQ’
fi
#
#
#
[/sourcecode]
Using Flow.sh
Before running the script:-
- Copy and paste the script below into a file and name it Flow.sh.
- Replace username:password on Line 2 with your AquaCUE user credentials separated by a colan (:). Be sure to include the quote marks. For example, CREDS=”yourname:yourpassword”.
- Enter a start date and, if desired, time in ISO format on Line 4 after SDATE=“YYYY-MM-DD” or SDATE=”YYYY-MM-DDThh:mm:ssZ”
- Enter an end date and, if desired, time in ISO format on Line 5 after EDATE=“YYYY-MM-DD” or EDATE=“YYYY-MM-DDThh:mm:ssZ”
- Enter the desired parameter values on Line 9. For example,
• after resolution= enter any of enter any of daily, hourly or monthly
• after unit= enter any of acrefeet, ccf, cubicfeet, cubicmeter, gallons or liter - Save and name the file Flow.sh.
- Locate the script on your hard drive, direct your command-line tool to it and type the following into the command-line:./Flow.sh
- Press ENTER to run the script. When it completes, copy and paste the results into a new file. Save the file and review it in a spreadsheet or other application that can read CSV files.
[sourcecode lang=”bash”]
#!/bin/bash
CREDS="username:password"SDATE="2017-07-31"
EDATE="2017-07-31"
COLS="Account_ID,Meter_ID,Endpoint_SN,Flow,Flow_Unit,Point_1_Read,Point_1_Read_Time,Point_2_Read,Point_2_Read_Time"
URL="https://api.beaconama.net/v1/eds/flow"REQ=`curl -s -X POST -u$CREDS -d "resolution=daily&startDate=$SDATE&endDate=$EDATE&unit=cubicmeter&headerColumns=$COLS" $URL`
EDS_UUID=`echo $REQ | jq -r .edsUUID`# if status returns EDS_UUID, then every 10 seconds checks
# to see if the report is ready. When the export has been completed
# you can fetch the export report. Note the curl option -L is used to
# allow curl to automatically follow redirects.if [ ‘$EDS_UUID’ != ‘null’ ]
then
while true
do
sleep 10 # check status every 10 seconds.
REQ=`curl -s -X GET -u$CREDS https://api.beaconama.net/v1/eds/status/$EDS_UUID`
echo REQ=$REQ
REPORT_URL=`echo $REQ | jq -r .reportUrl`if [[ $REPORT_URL != ‘null’ ]]
thencurl -L -s -X GET -u$CREDS https://api.beaconama.net/$REPORT_URL -o $OUTFILE
break
fi
done
else
echo ‘ERROR: $REQ’
fi
#
#
#
[/sourcecode]
Using Leak.sh
Use this sample script to call the /v2/eds/leak endpoint and retrieve a list of meters with leaks, along with the leak start date and the current leak rate.
- Copy and paste the text below into a text file and name it Leak.sh.
- Replace “username:password” with your AquaCUE user credentials.
- Save the file.
- Locate the script on your hard drive, direct your command-line tool to it and type the following into the command-line:./Leak.sh
- Press ENTER to run the script. When it completes, copy and paste the results into a new file. Save the file and review it in a spreadsheet or other application that can read CSV files.
-
[sourcecode lang=”bash”]
#!/bin/bash
CREDS=’username:password’COLS=’Account_ID,Meter_ID,Current_Leak_Rate,Current_Leak_Start_Date’
URL=’https://api.beaconama.net/v2/eds/leak’REQ=`curl -s -X POST -u$CREDS -d ‘Header_Columns=$COLS’ $URL`
EDS_UUID=`echo $REQ | jq -r .edsUUID`# if status returns EDS_UUID, then every 10 seconds checks
# to see if the report is ready. When the export has been completed
# you can fetch the export report. Note the curl option -L is used to
# allow curl to automatically follow redirects.if [ ‘$EDS_UUID’ != ‘null’ ]
then
while true
do
sleep 10 # check status every 10 seconds.
REQ=`curl -s -X GET -u$CREDS https://api.beaconama.net/v1/eds/status/$EDS_UUID`
echo REQ=$REQ
REPORT_URL=`echo $REQ | jq -r .reportUrl`if [[ $REPORT_URL != ‘null’ ]]
thencurl -L -s -X GET -u$CREDS https://api.beaconama.net/$REPORT_URL -o $OUTFILE
break
fi
done
else
echo ‘ERROR: $REQ’
fi
#
#
#
[/sourcecode]Using Read.sh
Get billing read data for a selection of meters or all meters in your facility using this sample script.- Copy and past this script into a file and name it Read.sh.
- Replace username:password on Line 2 with your AquaCUE user credentials separated by a colon (:). Be sure to include the quote marks. For example, CREDS=’yourname:yourpassword’.
- Enter a start date and, if desired, time in ISO format on Line 4 after SDATE=’YYYY-MM-DD’ or SDATE=’YYYY-MM-DDThh:mm:ssZ’
- Replace the sample Meter_IDs on Line 6 with real Meter_IDs for any number of meters. Use ampersands (&) to separate Meter_IDs.Retrieve reads for all of the meters in your facility by deleting Line 6 of this script.
- Save and name the file Read.sh.
- Locate the script on your hard drive, direct your command-line tool to it and type the following into the command-line:./Read.sh
- Press ENTER to run the script. When it completes, copy and paste the results into a new file. Save the file and review it in a spreadsheet or other application that can read CSV files.
[sourcecode lang=”bash”]
#!/bin/bash
CREDS=’username:password’
DATE=’2019-11-07T00:00:00Z’
URL=’https://api.beaconama.net/v2/eds/read’
COLUMNS=’Location_ID,Service_Point_ID,Meter_ID,Meter_SN,Register_Number,Endpoint_SN,Billing_Read,Read_Unit,Read_Time’
METERS=’Meter_ID=11111111&Meter_ID=22222222&Meter_ID=33333333&Meter_ID=44444444′
# —————————————————————————————
REQ=`curl -s -u$CREDS -H "Content-Type: application/x-www-form-urlencoded" -d "Date=${DATE}&${METERS}&Header_Columns=${COLUMNS}" -X POST $URL`# echo ${REQ}
EDS_UUID=`echo $REQ | jq -r .edsUUID`
# if status returns EDS_UUID, then every 10 seconds checks
# to see if the report is ready. When the export has been completed
# you can fetch the export report. Note the curl option -L is used to
# allow curl to automatically follow redirects.
if [ ‘$EDS_UUID’ != ‘null’ ]
then
while true
do
sleep 10 # check status every 10 seconds.
REQ=`curl -s -X GET -u$CREDS https://api.beaconama.net/v1/eds/status/$EDS_UUID`
echo REQ=$REQ
REPORT_URL=`echo $REQ | jq -r .reportUrl`
if [[ $REPORT_URL != ‘null’ ]]
then
curl -L -s -X GET -u$CREDS https://api.beaconama.net/$REPORT_URL [-0] $OUTFILE
break
fi
done
else
echo ‘ERROR: $REQ’
fi
#
#
#
[/sourcecode]
