A new Flexera Community experience is coming on November 18th, click here for more information.
Good afternoon, community.
I am having some trouble with finding a meaningful way to export data from all of the fields we have setup in our Workflow Manager template. I am looking for a way to export all of the metadata report information for each workflow item we have.
So, for example, I have a template we use for Application Packaging work where App Broker collects information from the end-users (such as Application Name, Version, Vendor, Ticket Number, Project Manager, etc.) and also has information from the App Packaging team pertaining to how the application was packaged, where the content used in Configuration Manager resides and other notes.
From what I can tell, it looks like everything exists between these tables - AMS_Application, AMS_ApplicationItem and AMS_AuditTrail. However, the contents of AMS_AuditTrail is basically unintelligible because every row of the table is an individual change to an individual field within the workflow.
I'm not really a SQL guy and I'm struggling through trying to write a query that will export all of our data. Has anyone ever had a situation where they needed to bulk export all of their Workflow Metadata? If so, how did you discover where it was all stored in the database and export it in a readable format? I'm open to any suggestions, advice or sample SQL code that might help accomplish this.
Thanks,
Zack Prieto.
Jul 27, 2022 04:19 PM
Flexera support had previously provided me with a Stored Procedure that allowed to manually sift through our database and locate where different bits of data were stored. I eventually got a query written that extracted the pertinent information from our database.
Aug 10, 2022 08:51 AM
I suggest you report a support case by navigating to Support -> Open New Case. Our support team should be able to provide you with a SQL script to export the kind of report you are looking for from the Workflow Manager.
Thanks
Jul 29, 2022 04:56 AM
Thanks for getting back to me @kmantagi. I originally reached out to support and they simply provided me with a Stored Proc I could use to manually search through the tables for specific data. I ended up cobbling together a query based off of that and pulled out the pertinent information.
Aug 10, 2022 08:49 AM
Hey Zack,
I run a company called TLUX Technologies (www.tlux-group.com) and we are Flexera's only certified global partner delivering all their AdminStudio and Workflow Manager consulting and training needs.
I'm very sure one of our team can help you out on a day-rate basis. Please feel free to reach out at crispin.luxton@tlux-group.com or on (872) 281 4069 to set up a chat.
Thanks a lot,
Crispin.
Aug 10, 2022 08:44 AM
Thanks, but no thanks. I managed to cobble together a query that got me the information I needed to export. @Cluxton
Aug 10, 2022 08:50 AM
No worries, thanks for circling back. Have a great day!
Aug 10, 2022 08:53 AM
Flexera support had previously provided me with a Stored Procedure that allowed to manually sift through our database and locate where different bits of data were stored. I eventually got a query written that extracted the pertinent information from our database.
Aug 10, 2022 08:51 AM
This is the query I used, for anyone else that might be tasked with exporting this data. I took the results from this and created a pivot table in MS Excel.
I'm not a SQL guy, so I'm sure there's opportunity to optimize this in some way - but it worked for me and that was my goal in creating this.
=================================================================
SELECT [DATABASE_NAME_HERE].[dbo].[AMS_Application].[ApplicationID]
,[DATABASE_NAME_HERE].[dbo].[AMS_Application].[ApplicationLName]
,[DATABASE_NAME_HERE].[dbo].[AMS_Application].[UploadDate]
,[DATABASE_NAME_HERE].[dbo].[AMS_Application].[AppStatusID]
,[DATABASE_NAME_HERE].[dbo].[AMS_ApplicationStatus].[Status_Text]
,[DATABASE_NAME_HERE].[dbo].[AMS_ApplicationItem].[CreateDate]
,[DATABASE_NAME_HERE].[dbo].[AMS_ApplicationItem].[DataText]
,[DATABASE_NAME_HERE].[dbo].[AMS_Person].[UserName]
,[DATABASE_NAME_HERE].[dbo].[AMS_WFMajorItemTpl].[WFMajorItemName]
,[DATABASE_NAME_HERE].[dbo].[AMS_WFMinorItemTpl].[WFMinorItemDesc]
,[DATABASE_NAME_HERE].[dbo].[AMS_DataMinorItemTextTpl].[MinorItemText]
,[DATABASE_NAME_HERE].[dbo].[AMS_ApplicationItem].[SortOrder]
,[DATABASE_NAME_HERE].[dbo].[AMS_DataMinorItemTpl].[DataMinorItemDesc]
,[DATABASE_NAME_HERE].[dbo].[AMS_DataMinorItemTpl].[DataMinorItemInternalDesc]
FROM [DATABASE_NAME_HERE].[dbo].[AMS_Application]
FULL OUTER JOIN [DATABASE_NAME_HERE].[dbo].[AMS_ApplicationItem]
ON [DATABASE_NAME_HERE].[dbo].[AMS_Application].[ApplicationID] = [DATABASE_NAME_HERE].[dbo].[AMS_ApplicationItem].[ApplicationID]
FULL OUTER JOIN [DATABASE_NAME_HERE].[dbo].[AMS_Person]
ON [DATABASE_NAME_HERE].[dbo].[AMS_ApplicationItem].[UpdatedBySubmit] = [DATABASE_NAME_HERE].[dbo].[AMS_Person].[PersonID]
FULL OUTER JOIN [DATABASE_NAME_HERE].[dbo].[AMS_WFMajorItemTpl]
ON [DATABASE_NAME_HERE].[dbo].[AMS_Application].[CurrentWFMajorItemID] = [DATABASE_NAME_HERE].[dbo].[AMS_WFMajorItemTpl].[WFMajorItemID]
FULL OUTER JOIN [DATABASE_NAME_HERE].[dbo].[AMS_WFMinorItemTpl]
ON [DATABASE_NAME_HERE].[dbo].[AMS_Application].[CurrentWFMinorItemID] = [DATABASE_NAME_HERE].[dbo].[AMS_WFMinorItemTpl].[WFMinorItemID]
FULL OUTER JOIN [DATABASE_NAME_HERE].[dbo].[AMS_DataMinorItemTpl]
ON [DATABASE_NAME_HERE].[dbo].[AMS_ApplicationItem].[ItemID] = [DATABASE_NAME_HERE].[dbo].[AMS_DataMinorItemTpl].[DataMinorItemID]
FULL OUTER JOIN [DATABASE_NAME_HERE].[dbo].[AMS_ApplicationStatus]
ON [DATABASE_NAME_HERE].[dbo].[AMS_Application].[AppStatusID] = [DATABASE_NAME_HERE].[dbo].[AMS_ApplicationStatus].[StatusID]
WHERE [DATABASE_NAME_HERE].[dbo].[AMS_Application].[ApplicationLName] IS NOT NULL
ORDER BY UploadDate,ApplicationLName
=================================================================
From the data this output, I created my pivot off of these rows, in order:
Status_Text,
ApplicationLName,
DataMinorItemDesc,
UserName,
DataText
This organizes the data by status of the workflow item first, then by the workflow's name (which for was the app name + version), then the step of the workflow, followed by who modified it and finally, the data they entered in the field in the workflow.
Aug 11, 2022 08:10 AM