Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Bash script using Office 365 CLI and jq to create a csv report of Flow with mapped Owner details
#!/usr/bin/env bash
set -e
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" >/dev/null && pwd )"
function cleanup {
#clean up the tmp files
rm -rf tmp
echo "Cleaned tmp folder..."
# Configure trap to call finsih whenever EXIT is called. Ensures cleanup of tmp
trap cleanup EXIT
CURRENT_USER=$(o365 status --output json | jq '.connectedAs')
echo "Logged in as $CURRENT_USER"
if [[ ! -z tmp ]]; then
echo "Creating temporary folder for file manipulation..."
mkdir tmp
#Step 1 - Get the default environment
echo "Querying for default Flow environment..."
DEFAULT_ENVIRONMENT=$(o365 flow environment list --output json | jq -r '.[] | select(.name | contains("'"Default"'")) | .name')
echo "Found default environment $DEFAULT_ENVIRONMENT, querying Flows..."
#Step 2 - Get all of the flows using the cli and write flows json to a tmp file
#Use a JMESPath query to filter the size of the file. See
# OR output to a temp file and read temp file
o365 flow list --environment $DEFAULT_ENVIRONMENT --asAdmin --output json > $TMP_FLOWS
#Step 3 - Get a unique list of the flow owners from the tmp file created in Step 1
echo "Flows found, searching for owner values..."
uniqueOwners=$(cat $TMP_FLOWS | jq -r 'map({userId: .properties.creator.userId}) | unique | .[] | .userId')
#Get the owner count and loop to call Microsoft Graph and build owner mappping file
ownerCount=$(cat $TMP_FLOWS | jq -r 'map({userId: .properties.creator.userId}) | unique | length')
echo "There are $ownerCount unique Flows owners."
echo "Building owner information json mapping file..."
echo "[" > $TMP_OWNERS
for ownerId in $uniqueOwners; do
echo "Querying graph for userid $ownerId..."
echo $(o365 aad user get --id $ownerId --output json) >> $TMP_OWNERS
if [[ $i -lt $ownerCount-1 ]]; then
echo "," >> $TMP_OWNERS
i=$(expr $i + 1)
echo "]" >> $TMP_OWNERS
#Step 4 - Use a jq module file to create a map of the creator.usedId's to {name, email}
echo "Mapping owners information..."
jq -n --argfile flows $TMP_FLOWS --argfile owners $TMP_OWNERS -f merge-flows.jq >> $TMP_MAPPEDFLOWS
#Step 5 - Create a CSV file with header row, flow information and owner email
echo "Building CSV file..."
jq -r '["FlowID", "DisplayName", "State", "Created", "LastModified", "Owner", "OwnerName", "OwnerMail", "Upn", "Trigger", "TriggerType"], (.[] | [.name, .properties.displayName, .properties.state, .properties.createdTime, .properties.lastModifiedTime, .properties.creator.userId, .properties.creator.displayName, .properties.creator.mail, .properties.creator.userPrincipalName, .properties.definitionSummary.triggers[0].swaggerOperationId, .properties.definitionSummary.triggers[0].type]) | @csv' $TMP_MAPPEDFLOWS > $TMP_FLOWSCSV
# if we are on macOS try opening the file with Excel
if [[ "$OSTYPE" == "darwin"* ]]; then
echo "Open CSV file in Excel? (y/n)?"
read answer
if [ "$answer" != "${answer#[Yy]}" ] ;then
open -a /Applications/Microsoft\ $DIR/$TMP_FLOWSCSV
echo "Open $DIR/file.csv to review report."
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.