Prerequisites
- Alteryx Server
- Version 2021.4+
- Alteryx Server API Key/Secret
- API Permissions
- Curator Role
Procedure
This workflow downloads all workflows from a Server to a local machine using the V1 API endpoint /webapi/admin/v1/workflows and the V3 API Pack, then parses through the XML to search for a specified term (such as tools used, database connections, DSNs, queries, versions, etc.).
Please note: while this workflow was developed to assist Administrators as a free, open-source example, this workflow is not officially supported by Alteryx Support. See the Customer Support Guidelines for more information on supported products and use cases.
Procedure
This workflow expands on a community post, "ServerRipper - Using the Alteryx Gallery API to download workflows en masse". With this workflow, Server admins can download all workflows from the Gallery (backing up all workflows at once), and search through the XML of each workflow for any term. This is useful when an admin would like to see which workflows are using a particular DSN connection, connector, workflow version, SMTP server, query, database, and more.
How to Configure and Run:
Be sure to install the V3 API Pack, if you haven't already.
Download here: https://community.alteryx.com/t5/Engine-Works/Introducing-the-Alteryx-Server-v3-API/ba-p/899228
1. Once the V3 API Pack is installed, download the attached file, SearchServerWorkflowXml_OAuth2.yxzp (or SearchServerWorkflowXml_OAuth1.yxzp). OAuth 1.0 is for pre-2021.4 Servers, and OAuth2.0 is for Server version 2021.4+. For Server versions 2022.3+, the SearchServerXML-V3-OAuth2.yxzp workflow can be used.
2. Ignore the import errors on the .bat files, as they will not affect the ability to run.
3. Click into the workflow Canvas.
4. In the Workflow- Configuration pane, click the "Workflow" tab.
5. In the "Constants" section, edit the value of the GalleryBaseURL to your Server URL (NOTE: must not include "/gallery" at the end).
6. Also in the "Constants" section, edit the value of the SaveTo constant to a location where all Server workflows can be saved. Ensure there is enough space in this location.
7. Configure the first tool (API Authentication Macro with #5 comment) to include your Web API address, API Key, and API Secret. This is found in the "My Profile" section of the Server UI.
8. Input a term to search for within the XML of the workflows in the Text Input Tool (replace the word "Query").
9. Run the workflow.
10. Check the boxes of "subscriptionName" and "metaInfo_name" if any errors occur on the Data Cleansing Tool, or if there are any Windows file errors.
How to Know What to Search
Look at the XML of a similar workflow to one you're looking for. Is there any pattern specific to what you're searching for? Here are a few examples:
- "Query" - Returns all workflows with database queries.
- "<AlteryxDocument yxmdVer="2022.1">" - Returns all workflows with version 2022.1.
- "DSN=SQL Test" - Returns all workflows using the data source named SQL Test.
- "Plugin="SharePointInput"" - Returns all workflows that contain the SharePoint Input Tool.
- "FileName.xlsx" - Returns all workflows that reference FileName.xlsx, either on Input or Output.
- FileFormat="23" - Returns database connections.
Common Issues
"401 Unauthorized"
- Was the API Key and Secret input into the first tool? Is the API user an admin? Do they have curator rights?
"Error: Server API Authenticate (79): Tool #47: Parse Error at char(1): Type mismatch. Number provided where a string is required. (Expression #1)"
- Ensure the Web API URL is input in the first tool.
"Formula (60): Parse Error at char(62): Unknown variable "id" (Expression #1)"
- Ensure the Web API URL in the first tool has the right base address, matching the workflow constant "GalleryBaseURL". Also remove "/gallery" from the GalleryBaseURL Workflow Constant, if present.
"Error transferring data: [URL]: Failure when receiving data from the peer"
- The root cause of this error is currently investigated (TCPE-539), but may be related to proxies using a PAC script. A few potential workarounds are:
- Throttle the records in the Download Tool.
- Use Fiddler while decrypting HTTPS traffic (Tools > Options > HTTPS > Check "Decrypt HTTPS traffic" in Fiddler).
- Configure the proxy to send http requests to 127.0.0.1 on port 8888.
- This defect TCPE-539/TGAL-8049 is fixed in the following Alteryx Server versions:
- 2023.2-LTS
- 2023.1.1_Patch 2
- 2022.3.1_Patch 9
- 2022.1.1_Patch 9
- 2021.4.2_Patch 11
Fewer workflows downloaded than expected
- The Subscription API Key may have been used instead of the Curator's API Key. This will return only the workflows in the single Subscription.
V5- SearchServerWorkflowXML-V5.yxzp Updates
- Owner information added to the workflows
- Failed API calls are reported to an XLSX and don't stop the workflow execution
V4- SearchServerWorkflowXML-V4-OAuth2.yxzp Updates
- Changed capitalization in Select tool (87) from fileName to FileName.
- Changed Append Fields (117) to Warn on more than 16 appends (rather than error).
- Added Filter tool (191) and Message Tool (192) to exclude workflows from the search where file names are empty, as it causes a "You must pick a file from the zip archive" error in the ContainsQueries macro.
- Added a formula in the Formula tool (66) to account for multi-byte characters in workflow names with "DecomposeUnicodeForMatch([name])" formula.
- Added a Record ID tool (188) and formula in the Formula tool (66) to add an ID to the end of workflow names to differentiate in the case of duplicate names. This way, the files are not overwritten by any duplicates.
V3 API- SearchServerWorkflowsXML-V3-OAuth2.yxzp Updates
- Main SearchServerWorkflowsXML_V3_OAuth2.yxmd file changes:
- Changed endpoints to webapi/v3/workflows (Formula (81)) and webapi/v3/workflows/[id]/package (Formula (60)) to avoid "Failure when receiving data from the peer" error.
- Increased the number of connections from 4 to 6, increased timeout to 1200 seconds, selected "Throttle Records", and set the number of records per minute to 120 in the "Connections" tab of the Download tool (84) to avoid "Failure when receiving data from the peer" error.
- Added "?view=Full" to the endpoint for retrieving workflow information to include the field for the underlying file name in the .yxzp package, versions_0_details_fileName (v3/workflows endpoint in the Formula tool (81)).
- Removed Subscription folders- All workflows are now added to a single folder specified in the value of the "SaveTo" Workflow Constant.
- Removed Data Cleansing tool in favor of a formula tool with RegExReplace formulas and filtered out files with brackets ([ or ]) prior to the ContainsQueries macro. This helps account for "Unable to open file for archive" and "FileName not found" errors due to special characters, non-English letters in file names, and mis-matching names, causing conflicts when writing or opening files.
- Warning message is shown when workflows have been removed prior to searching as part of the previously mentioned change.
- Workflows that were searched unsuccessfully (search term not found) are now output in the searching macro (ContainsQueries) "F" output anchor, displaying in the main workflow.
- Added additional Documentation tools to describe steps.
- ContainsQueries.yxmc Macro Changes:
- Changed all Append tools to warn or allow all appends on more than 16 records.
- Removed DataCleansing (47) from the ContainsQueries macro to reduce warnings.
- Simplified ContainsQueries macro input- removed fileName and metaInfo. Now, only FilePath and searchFor are needed.
- Created additional "F" output of the ContainsQueries macro to show which files were searched but did not return a result (no line of XML in the workflow contained the search term).
- Replaced Summarize tools in the ContainsQueries macro with Unique tools to avoid errors when there are no results.
- Implemented flagging for search to increase performance and avoid errors when there is no result.
- Added filtering when the FileName or LineNumber is null in the ContainsQueries macro.
Additional Information
Version Guide:
- Server Version 22.3+: V3 or later
- Server Version 21.4+: OAuth2 workflow
- Server Version Below 21.4: use OAuth1 workflow
Attachments