We are on Octopus Octopus 3.12.4.
In Documentation for Reporting (https://octopus.com/docs/administration/reporting) is example Excel file available for download.
This file contains these columns: DeploymentId, DeploymentName, ProjectId, ProjectName, ProjectSlug, EnvironmentId, EnvironmentName, ReleaseId, ReleaseVersion, TaskId, TaskState, Created, QueueTime, StartTime, CompletedTime, DurationSeconds, DeployedBy
However when following instructions to generate my own XML file using octo.exe (https://octopus.com/docs/how-to/report-on-deployments-using-excel) generated file has only these columns: Created, Environment, Id, Name, Project, ProjectGroup.
Why is it missing columns like QueueTime, StartTime, CompletedTime, DurationSeconds, DeployedBy etc?
Does SQL DB contain these columns and is it proffered way for reporting than?
Is there way how to limit number of deployments (last 10, only since specific date…) in XML export?
I’ll appreciate your help.
Thanks for getting in touch.
The dump-deployments command doesn’t output all the fields as you noted however, the fields that you see in the example on the documentation are available via the API.
The URL http://octopusserver/api/reporting/deployments/xml?apikey=API-123 will return all the xml data you require. Modify this url for your server and API key.
You can download this xml data in PowerShell easily: (New-Object System.Net.WebClient).DownloadFile("http://octopusserver/api/reporting/deployments/xml?apikey=API-123", ".\deployments.xml")
Yes this command works, Also it is possible to run in cURL: curl.exe http://octopusserver/api/reporting/deployments/xml?apikey=API-123 -o -C:\PROJECT\OCTOPUS\DATA\report.xml
Is there a way how to format output in JSON format instead of XML?
How about limiting output? For example if I want to see only last 10 deployments or I want to see only deployments since specific time stamp (Created>=2017-09-20 00:00:00 -05:00)
I am looking for opportunity to pull last day/hour deployments via Python script.
Currently the reporting endpoints are quite limited and don’t give the consumer a lot of options. The /api/reporting/deployments/xml endpoint is hardcoded to return XML only, and there is no option for JSON.
We are looking at extending these reporting options in the near future, but there is no current ETA on when that will be available.
For the time being, to do anything more specific, you would need to use the deployments API directly. In your situation, perhaps a better option would be to look at the Events API (all events that happen within Octopus are stored in the Events table, for auditing purposes). The Events API lets you filter by various criteria more relevant to reporting (eg. from/to and documentTypes=Deployments), so you could use the Events API to find deployments in a given timeframe, then combine that with the Deployments API to get any additional details you need about that deployment.
To see what’s available in terms of Event filtering, you could open up Configuration > Audit screen in the Octopus UI, and watch the Developer Tools in Chrome (ie. the Network tab) to see the API requests that are being sent as you apply certain filters. This will then give you an idea of what you can query from your python script.
I’ll explore possibilities with API.
In meanwhile, how about direct access to Direct table access on SQL Server? https://octopus.com/docs/key-concepts/projects/reporting#Reporting-Directtableaccess I understand potential risk that you might add additional columns in future, however will current columns change? Will query execution impact Octopus performance/availability?
Accessing DB and running query hourly to extract data will be much easier solution as I am going to load this data into DB anyway.
Sure, that’s always an option The API is just calling through to query that exact same table behind the scenes and applying “DeploymentView” permissions to the query. As long as you understand the potential risks as mentioned in that documentation:
Eg
We may add additional columns in the future
We’ll try not to change existing columns, but just in case, you may wish to set up your own View in SQL server to provide an abstraction layer.
Since you’re accessing the data directly, be aware that Octopus team permissions won’t apply
Don’t join with any other tables - these are much more likely to change in future, so you’re on your own if you do!
The API is just calling through to query that exact same table behind the scenes and applying “DeploymentView” permissions to the query. As long as you understand the potential risks as mentioned in that documentation: