How to get a file on the MID Server and attach it to a RITM using Flow?

[gjz]

I have a requirement to find inactive users in Active Directory and put them in a .csv file to email to specific people.

The manual process is to run a PowerShell script to find all inactive users, then create email to specific users with the file as an attachment for them to act on the list.  Then, the person responsible for doing something with the inactive accounts creates a request to document the activities completed.

We want the entire process automated - except for any activity that may need to be done manually, which will be recorded in the request.

I thought I would do it this way in a flow:

1. Get the inactive users and create the file on the MID Server through a PowerShell script

2. Retrieve the file from the MID Server and send it as an attachment in a notification

3. Submit a request and attach the file to the request. 

I got #1 working, I'm able to use a PowerShell script and place the file on the MID Server.  I'm stuck on #2, retrieving the file from the MID Server so I can attach it to email.

I assume I need to create a custom action to retrieve the file, but what "type" of action should it be?  Should I use OpenAPI/Postman, PowerShell, REST, Script??  I've never done anything with API or REST, so I'm a little lost and need some help or ideas.

[Dinesh Reddy By]

Below articles may solve your issue.

https://www.servicenow.com/community/itom-articles/automated-data-import-using-mid-server-and-powers...

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0817437

In my case, we enabled the SFTP server on the MID Server host to pull files using the SFTP step in Flow Designer, in compliance with security policies.

Thanks,

Dinesh
Please thumbs up if this is helpful

[Daniel Madsen]

Hi gjz

For step #2 (retrieving the file), you have a few options, but I'd recommend using a PowerShell Custom Action since you're already comfortable with PowerShell and have it working for the first part. You'll define an input variable like FilePath, which becomes accessible as an environment variable ($env:FilePath) in your script.

Here's how you can approach this:

1. Create a new Flow Action using PowerShell script type
2. In this action, write a script that reads the file from the MID Server and returns its content as base64 encoded string
3. The script would look something like:

$filePath = $env:FilePath
$fileContent = [System.IO.File]::ReadAllBytes($filePath)
$base64Content = [System.Convert]::ToBase64String($fileContent)
$fileName = [System.IO.Path]::GetFileName($filePath)

$result = @{
"fileName" = $fileName
"fileContent" = $base64Content
}

return $result | ConvertTo-Json -Compress

4. Define input variables for your action (like FilePath)
5. Define output variables (fileName and fileContent)

Once you have this action, in your Flow you can:
1. Call this action to retrieve the file as base64
2. Use the "Send Email" action with attachment
3. For the attachment, you'll use the base64 content returned from your custom action

For the email notification with attachment, you'll need to use the "Email with attachment" action rather than the standard notification. You can set the attachment using the base64 content and filename returned from your custom action.

For step #3, after sending the email, you can create a record (like an incident or request) and attach the same file using the Attachment API.

If you're not comfortable with PowerShell for this part, another approach is to use a MID Server Data Transfer action, which is specifically designed for transferring files between the MID Server and ServiceNow instance.

If this helps, please give it a helpful vote. And if it’s what you were looking for, go ahead and accept the solution. Thanks,
Daniel Madsen