Log File Automation

[sqldba504]

Hi All,

I am trying to move the log files for various executions to different directories automatically.  It seems like the best I can do is do it after the workflow runs.  To do that, I was going to write the RunID out to a file, and have powershell take the RunIDs, find them in the log directory and either copy or move them with the other run artifacts based on other identifiers in the log file.  

I have a few misc. questions in all of this.

1) How do I determine the RunID before the workflow ends?  

2) Is there a way to pass formula output formatted as "string" to the "Command Arguments" field in the Run Command tool?  It seems you can do that by using [parameter], but I haven't been able to make that work. I have a powershell script that takes 2 parameters, and I have tried to just pass each parameter as well as the entire string for the arguments to "CommandArguments".  If I put put the resolved text in "CommandArguments" it works fine, so I know it isn't the script, or how I'm using each field in the run command.  

3) Is there an easier way to capture logs?  Perhaps by pointing Alteryx at a SQL database and just letting it log there?  I have seen people talk about emailing them, but that's not the best solution for me.  

If there is anyone doing any archival of the logs, I would be interested to see your workflows.  

Thanks

[LindonB]

Unfortunately, Alteryx doesn't have a pre-built way to get the RunID before the workflow completes. However, you can use a workaround by generating a unique identifier at the start of your workflow and using it throughout. For example, you can use a formula tool to create a unique ID based on the current date and time.

You can pass dynamic values to the “Command Arguments” field by using the [parameter] syntax. Ensure that your formula output is correctly formatted as a string.

[sqldba504]

Thanks for taking the time to respond!  Great idea with creating your ownID within the workflow.  

Something else is definitely going on there with the CommandArguments.  To troubleshoot, I am writing the formula output to an Excel file to see what its resolving to.  I copied the formula output, and opened a command prompt and ran that in the format powershell <Parameter's Output from Excel file> and it executed fine.  To review, In the formula I am adding a new column called "ArchiveCommand" and defining it as a string, and putting [ArchiveCommand] in the "Command Arguments" field.  Surely I'm doing something wrong, but I can't figure out what.  

[ChrisTX]

There are many posts in the Community if you just search for "log".  None of the options were the best fit for me.  I was always searching for an option to write out a log file on a per-workflow basis.

Would you want to run a Powershell script under Events: Run Command, configured in each workflow?

Here are the options I'm aware of:

Option 1: Easiest option: change User Settings
Inside Alteryx Designer choose the menu option for Options > User Settings > Edit User Settings

Disadvantage: every single time you run a workflow (during development, while testing, *anytime* we run *any* workflow): a new log file is created in the specified folder.
What if we only want to write our Production workflow logs to a specific folder?

Option 2: if you have access to System Settings:
Inside Alteryx Designer choose the menu option for Options > Advanced Options > System Settings > Engine > General
Change the Logging Directory

Disadvantage: same as Option 1 above

Option 3: send a log file using email, using Events: Send Email: Run Event When: After Run
In each workflow, under Workflow Configuration > Events:
• Check the box for Enable Events
• Add an Event for Send Email, by configuring this screen:

Disadvantage:

   must configure Run Event inside each workflow

   you will need to save the attachment (the log file) from each email

Option 4: save log file to a text file, on a per-workflow basis, using Events: Run Command

Disadvantage: must configure Run Event inside each workflow

Chris

[sqldba504]

Hi,

Thanks for your response.  How are you thinking of doing option 4?   While the workflow is running, it doesn't seem like you can do anything with the logfile b/c it doesn't appear until after the end of the run. 

You ask about running a powershell script in each workflow ... yes, I'm okay with that!  I don't have too many workflows to deal with so its ok.  

[ChrisTX]

You're right, you can't do anything with the log file until the end of the run.

if you need to write out bookmarks for a long-running process: you could use output tools and Control Containers to write to one or more "progress files" (a TXT or CSV file).

But if you're just trying to capture the log file:

I have notes on a few other posts that were using Powershell, but all of those posts seem to be Archived now.  Unfortunate.

I've use Option 4 above with a Powershell script to capture the log.  It was a lot of trial and error.  I'll need to document the process, because I tried many things before I finally got it to work.

I'll need to put my notes in a good How To document to use Powershell.  The biggest challenge was spaces inside the full path, but I'm guessing most people can avoid that.  And I was getting errors trying to use the Temp (working) folder.   First get your Powershell script working when called from a CMD window.  Then work it into the Alteryx Run Command tool.

Chris

[sqldba504]

It would be nice to send the log contents to an output file!

Chris, if you can't remember how you wrote it out to a file, I think what I'm going to do is use the message tool to write something in the log file.  This way, the logs I need can easily be identified by looking at the contents of each file with powershell.  Then, those files identified can be copied to a designated log location.  This is a process that will run on a schedule. 

On a another note, a tool like Alteryx should be able to write the log files to a SQL DB as an option.  

[ChrisTX]

Here's a related Idea, if you'd care to add a Like:  

Improve Log files for simpler parsing

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Improve-Log-files-for-simpler-parsing/idi-p/...

I'll try to send the Powershell instructions today.

Chris

[ChrisTX · Accepted solution]

@sqldba504 see below and attached

How to Save an Alteryx workflow’s log file to a text file using Powershell on a per-workflow basis, using Events: Run Command

After much trial and error...

There were many challenges with avoiding the Temp folder and Working Directory, spaces in the file name or folder path, etc.

Hope this works for you.

The solution below runs a Windows Powershell (.ps1) script, which may require security permissions in your environment.

Note: You cannot create the Powershell script in the folder specified by Alteryx constant [Engline.TempFilePath], because the Run Event will generate an error if you try to run the Powershell script from the [Engline.TempFilePath] folder.

Use a text editor to create a Powershell script with a .ps1 file extension
• Avoid spaces in your .ps1 file name. Use only letters, numbers, underscores.
• Save your .ps1 file in a permanent folder, not a temporary folder.
• Ensure your network id has security access to the folder
• If you are using the Alteryx Server, ensure your “run as” network id (possibly a service account) has security access to the folder

Here is the text for the Powershell script:

param(
[Parameter(Mandatory=$True, Position=0, ValueFromPipeline=$false)]
[System.String]
$myLogFileFolder,
[Parameter(Mandatory=$True, Position=1, ValueFromPipeline=$false)]
[System.String]
$myWorkflowName,
[Parameter(Mandatory=$True, Position=2, ValueFromPipeline=$false)]
[System.String]
$myLogFileContents
)
[string] $myDateTime = Get-Date -Format "yyyy-MM-dd HH-mm-ss"
[string] $myLogFileFullPath = $myLogFileFolder + "\Log for user [" + $Env:UserName + "] workflow [" + $myWorkflowName + "] date [" + $myDateTime.Substring(0,10) + "] time [" + $myDateTime.Substring(11) + "].txt"

Set-Content -LiteralPath $myLogFileFullPath -Value $myLogFileContents

In each Alteryx workflow, under Workflow Configuration > Events:

• Check the box for Enable Events
• Add an Event for Run Command with these values:

Run Event When: After Run  (or any value you want)
Command: powershell

Command Arguments [Optional]:
-NoLogo -NonInteractive -WindowStyle Hidden -File "\\?\MyPowershellFolder\MySubFolder\Save_Alteryx_Log_File.ps1" "C:\MyLogFileFolder" "%Module%" "%OutputLog%"

• Use your values to specify the full path to your .ps1 file
• Replace “C:\MyLogFileFolder” with the full path to your log file folder. Ensure this value does not have a backslash at the end.

Working Directory [Optional]: <leave this value blank>, to avoid an error
Timeout (in seconds): 30

Notes If you are using one or more UNC paths (beginning with \\), you do not need to use “\\?\” instead of “\\”

Example: screenshot

If you want to test your Powershell script from a CMD window:
• use a value for the full folder path to the .ps1 file (folder name and file name) that doesn’t have any spaces
• use a folder name for your log file folder that doesn’t have any spaces
• use double quotes for the full path to the .ps1 file, and use single quotes for the three parameter values
• use this syntax:
powershell "\\UNCPath\MySubFolder\Save_Alteryx_Log_File.ps1" '\\UNCPath\MyLogFileFolder' 'My Workflow Name' 'My Workflow Log file contents'

Sample workflow attached.

Chris

[sqldba504]

Chris, 

This worked great!  One question though.  Where does %OutputLog% get set?  I didn't see you set this, so it must be an internal thing to Alteryx??  Will this work for Alteryx server also if the user executing is given the proper access for the powershell script and underlying directories?