Skip to content

Instantly share code, notes, and snippets.

Embed
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 )"
TMP_ENVIRONMENTS=environments.json
TMP_FLOWS=flows.json
TMP_OWNERS=owners.json
TMP_MAPPEDFLOWS=mappedFlows.json
TMP_FLOWSCSV=flows.csv
if [ -f $TMP_FLOWSCSV ]; then
echo "Removing file $TMP_FLOWSCSV ..."
rm $TMP_FLOWSCSV
fi
#Step 1 - Get the default environment, could be made a selection if desired
echo "Querying for default Flow environment..."
o365 azmgmt flow environment list --output json > $TMP_ENVIRONMENTS
FLOW_ENVIRONMENT=$(cat $TMP_ENVIRONMENTS | jq -r '.[] | select(.name | contains("'"Default"'")) | .name')
echo "Found default Flow environment, querying Flows..."
#Step 2 - Get all of the flows using the cli and write flows json to a tmp file
#VorpalJS seems to mangle large json outputs so we use tmp files to address this issue
o365 azmgmt flow list --environment $FLOW_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 creator. userId 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
i=0
for ownerId in $uniqueOwners; do
echo "Querying graph for userid $ownerId..."
echo $(o365 graph user get --id $ownerId --output json) >> $TMP_OWNERS
if [[ $i -lt $ownerCount-1 ]]; then
echo "," >> $TMP_OWNERS
fi
i=$(expr $i + 1)
done
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 creator.userId values..."
jq -n --argfile flows $TMP_FLOWS --argfile owners $TMP_OWNERS -f merge.jq >> $TMP_MAPPEDFLOWS
#Step 5 - Create a CSV file with header row, flow information and owner email
echo "Building CSV file..."
jq -r '["FlowID", "Name", "State", "Owner", "Email"], (.[] | [.name, .properties.displayName, .properties.state, .properties.creator.displayName, .properties.creator.mail]) | @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\ Excel.app $DIR/flows.csv
else
echo "Open $DIR/file.csv to review report."
fi
fi
#clean up the tmp files
rm $TMP_ENVIRONMENTS
rm $TMP_FLOWS
rm $TMP_OWNERS
rm $TMP_MAPPEDFLOWS
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.