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=./tmp/environments.json
TMP_FLOWS=./tmp/flows.json
TMP_OWNERS=./tmp/owners.json
TMP_MAPPEDFLOWS=./tmp/mappedFlows.json
TMP_FLOWSCSV=flows-frombash.csv
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
fi
#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 https://github.com/pnp/office365-cli/issues/1266
# 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
i=0
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
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 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\ Excel.app $DIR/$TMP_FLOWSCSV
else
echo "Open $DIR/file.csv to review report."
fi
fi
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.