Convert BIRT to SQL with Powershell

This script reads through all the "BIRT" OpCon reports and and strips out the root SQL queries. The query information can optionally be send to an OpCon job for immediate execution.

Prerequisites

• OpCon Release 18.3 (should work in others but this was the version tested against)
• PowerShell 5.1

Instructions

This script should be run on the SAM OpCon server and has a number of parameters that match to the corresponding parameters when automating BIRT reports through the traditional method (see OpCon documentation for "BIRT Report Generator").

Parameters:

• skddate - Same as BIRT
• histarc_skddate - Same as BIRT
• history_skddate - Same as BIRT
• audithst_sqldate - Same as BIRT
• machs_machid - Same as BIRT
• machgrps_machgrpid - Same as BIRT
• skdid - Same as BIRT
• deptid - Same as BIRT
• tagname - Same as BIRT
• birtpath - Path to the BIRT report directory, default is: C:\Program Files\OpConxps\SAM\BIRT\ReportEngine\OpConXPS_Reports
• o - Same as BIRT
• runas - User permissions to simulate when building the query
• opconmodule - Path to the OpCon API module (available in the Innovation Lab here)
• r - Same as BIRT
• report - Name of report to run
• msgin - Path to MSGIN directory
• extuser - External event user (MSGIN only)
• extpword - External event password (MSGIN only)
• email - Email address to send the report results
• run - Yes/No whether to submit an OpCon event to run the report
• url - OpCon API url (instead of MSGIN)
• token - OpCon API token (instead of MSGIN)
• outputpath - Path to output the resulting SQL queries too

OpCon setup

The below information can be altered in the script or added to the parameters.

-The job name is "RUN REPORT" in the "Adhoc" schedule with a frequency of "OnRequest" (the frequency definition is something in the past so it will never build unless an event calls it).
-The job type is "SQL" with predefined user/sql server/sql db that you will need to customize based on your environment.
-The "SQL Script" tab should have the "Script File" option selected with a "Script File Path" value of: "[[JI.FILE]]". -There should be a "NOTIFY:EMAIL" event on the job with an attachment pointing to "[[JI.OUTPUT]][[JI.REPORT]].csv"

Disclaimer

No Support and No Warranty are provided by SMA Technologies for this project and related material. The use of this project's files is on your own risk.

SMA Technologies assumes no liability for damage caused by the usage of any of the files offered here via this Github repository.

License

Copyright 2020 SMA Technologies

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Contributing

We love contributions, please read our Contribution Guide to get started!

Code of Conduct

SMA Technologies has adopted the Contributor Covenant as its Code of Conduct, and we expect project participants to adhere to it. Please read the full text so that you can understand what actions will and will not be tolerated.