Month: April 2019

Three New Sewing Projects — Coat and Dresses

I want to try making a coat for Anya — the Polartec fleece jacket turned out incredibly well, even if the thick fabric was incredibly difficult to sew. I intentionally chose a larger size — 8 — so she’d be able to wear the jacket for a few years. For this winter, I thought I’d make a dressy wool coat. Totally basic black wool, but a silky purple lining, purple silk twist for the button holes — still deciding between the darker and lighter purple, and some cool buttons I’ve not yet found.

            Bemberg Ambiance - Irus

And as usually happens when ordering fabric, I can avoid paying ten or fifteen bucks for shipping if I buy for a second project. A few years ago, I found a free pattern for a peasant dress. I love the dress, but the “border” part would have been the entire dress when Anya was younger. I bookmarked the pattern to use when she got a little older. Well … she’s older. And certainly taller! Tall enough that I think a dress with border print fabric will work. Since Anya is loving unicorns right now, I thought I’d make her a unicorn peasant dress.

The pattern reminded me of one of my favorite things I’ve ever bought for Anya — this top that she wore as a dress in 2014/2015, a tunic in 2016/2017, and a shirt in 2018. The arms are small now, or she’d still be wearing it as a shirt.

It’s corduroy, but a really awesome light-weight one. In browsing around checking out fabrics, I realized it is 21 wale corduroy — so I added two different fabrics. I figure I can make a blue dress with a cream stripe and a cream shirt with a blue stripe. And what’s a dress today will be a tunic in a few years and a shirt for another year or two after that.

Did you know … you can copy files from OneDrive to SharePoint Online (thus Microsoft Teams too)?

Sometimes I work on a document privately before putting it out for my group to review, but it takes time to upload a copy of the document to our Teams space! You can move and copy documents from OneDrive directly to SharePoint Online. Since “Files” in Teams spaces are just SharePoint Online document repositories, this means you can move/copy documents to a Teams space too.

Open “OneDrive” from https://portal.office.com or the side-bar of any web-based Office 365 product.

Select the document(s) that you want to move/copy and select either “Move to” or “Copy to” from the menu bar.

A new pane will fly out from the right-hand side of the browser window. You’ll see some of the SharePoint Online sites and Teams spaces to which you have access listed. If the one you want isn’t listed, click “Browse sites” to see a full list.

Click “Show more” to browse through the list of sites.

If you are unable to locate the site you want to use, open it in SharePoint online and follow it. Click on the site into which you want to move/copy your documents.

For SharePoint online sites, you’ll see the site contents and can navigate through the hierarchy to the location you want the file stored. For Teams spaces, click “Documents”.

You’ll then see folders for each Channel. Click the channel into which you want to store the documents.

Navigate through the Channel’s file structure to the location you want the document stored. Click the blue button to copy/move the document to that location.

Voila, my documents are moved within Microsoft’s cloud from OneDrive to SharePoint Online.

 

Microsoft Flow Usage Tracking Process Proposal

Assumptions: This proposal is predicated on having some reasonable way to download the Flow usage report that currently needs to be generated from the Flow admin page. Otherwise usage tracking is a manual process kicked off by logging into Flow admin site and downloading the CSV usage detail report.

This proposal is also predicated on the idea that our group is not-for-profit — we are not seeking to recoup O365 licensing cost through Flow usage billback.

Issue: Flow execution is “throttled” when usage exceeds the tenant limits. Per a response from a “Flow Staff” individual to ‘what actually happens when you exceed your allocated cycle limit’, “To clarify what is meant by throttling, flow run usage exceeding your tenant limits may be suspended at Flow’s discretion until the following billing cycle.”. While “at Flow’s discretion” is still vague, a tenant risks having Flow runs suspended when they exceed their allocated cycle limit.

Additional cycles can be purchased to continue Flow execution. As of 26 April 2019, an additional 50,000 runs costs 40$. We should ask Microsoft to clarify “Flow’s discretion”. Are enterprise customers able to have Flow execution continued and be billed for their overage? Do enterprise customers receive a short grace period during which time they can purchase additional execution cycles for upcoming months?

Farther complicating the issue, it is difficult for a manager to assess how many flow cycles are being executed by their subordinates. While Microsoft has added per-workflow usage statistics, even when workflows are maintained under shared spaces, individually checking each Team flow to determine how many times it has executed is cumbersome.

Solution: If we cannot purchase execution cycles after our limit has been reached, we will need to routinely track the number of cycles executed in our tenant to avoid both execution disruption and purchasing additional runs in months where we do not exceed our “free” cycle quota. To determine an appropriate alerting interval, we need to determine the time between identifying that our tenant is approaching its threshold and the activation of newly purchased cycles.

To create a financial incentive to maintain efficient Flow jobs, cycles should be billed back to the business units that are using Flow. Since each Office 365 license includes 2,000 Flow execution cycles, a BU is not charged when they are under their “free” allocation (i.e. # of O365 licensed users in the BU times 2,000). BUs may be charged at a rate of 0.0008$ per cycle over their “free” allocation (question: does this need to be rounded up to avoid fractional pennies? i.e. one cent per 1,250 cycles). In months were the Flow execution cycle limit in the tenant is exceeded, the BUs are charged. Many individuals do not use Flow so there are unused execution cycles. If we are under our tenant limit but a BU has exceeded their allocation, no additional money has been spent so no billback occurs. The BU owner would still be notified with a message that includes “due to low company-wide Flow usage, your overage does not incur any charge this month. This overage could result in a charge of up to {calculatedOverageCost} charged to your BU.”. The message will include the web link to help them understand who is using Flow in their organization.

We would develop a script to update a data table. This table will record each Flow user, the number of cycles used month to date, the number of cycles used during the previous month, the number of cycles used year to date, and the number of cycles used during the previous year. A web interface like https://msteams.windstream.com/usageReporting/index.php (which allows managers to view Teams usage of their sub-organization) would be created to provide visibility into the Flow usage data. We would also develop a script, run at the beginning of each month, that determines the BU for each Flow user, aggregates “previous month” Flow usage data per BU, calculates the total number of O365 licensed accounts per BU, calculates the BU’s cycle overage, notifies BU owners per the previous paragraph, and either initiates the billback process or produces the data which is needed to initiate the billback process.

I additionally suggest we create a “best practices” guide for using Flow. As an example, creating a workflow ‘perform some process every time our shared mailbox receives a message from BOB’ incurs a cycle execution every time the shared mailbox receives a message, regardless of the message source. When creating workflows triggered by e-mail messages, using a dedicated mailbox (research: can an O365 group mailbox be used as a trigger??) avoids non-trigger messages executing a Flow.

Did you know … Power Query can extract a unique list of records?

Remove duplicates is a quick way to obtain a unique list of records; every time the source data is updated, though, you’ve got to copy and ‘remove duplicates’ again. There’s a better way! Use Power Query to create a unique list that can be updated with a single click.

To use Power Query, first highlight the column containing the information for which you want a list of unique values.

On the “Data” ribbon bar, select “From Table/Range”

You’ll be asked to confirm where the source data is located – the highlighted selection should appear. Click “OK” to continue.

A new window will open – the Power Query Editor. On the “Home” ribbon bar, click on “Remove Rows” and select “Remove Duplicates”

A unique list of values has been extracted in the Power Query editor – but you want to insert that data into your spreadsheet. Click the drop-down by “Close & Load” then select “Close & Load To …”

Now you can select where you want your list of unique values to appear – I am creating a table in an existing worksheet. Click “OK” to insert the unique list.

Voila, I now have a unique list.

What happens when new records are added to my source data? The Power Query table does not automatically update as values are added to the source data. On the “Data” ribbon bar, click “Refresh All” to update the unique value list.

 

Did you know … you can redirect custom URLs to Microsoft Forms?

Microsoft Forms provides a simple framework for creating surveys and polls which can be distributed either to internal users or made publicly available. Including your Form URL in an a href tag is one way to avoid trying to communicate the super-long Form URL … but if you’re looking for a ‘pretty’ URL, something that can be included in print media or provided to someone verbally, you can redirect custom URLs to your Form URL.

Generally, hosting a forwarding URL requires a web server; but URL-shortening services should work. To use a web server, you’ll need to configure a site (or path from a site) to serve an HTTP redirect. I am using 302 (temporary) redirection instead of 301 (permanent redirection) in case I want to forward my custom URL to a different Form.

Apache config

Use the “Redirect” directive in your virtual host config:

<VirtualHost 10.1.2.3:80>
     ServerName customsite.example.com
     ServerAlias customsite.example.com customsite
     Redirect / https://forms.office.com/Pages/DesignPage.aspx#FormID=wbRnJe2w9UCu41....
</VirtualHost>

IIS Config

In the IIS management utility, navigate to your website and select “HTTP Redirect”.

Check the box to redirect requests and paste in your Forms URL. Check the box to redirect all requests to the exact destination.

When your site is accessed, the browser receives the HTTP redirect and displays your Form.

 

Did you know … you can add manager approvals in Microsoft Flow?

In my previous post, I demonstrated how the Office 365 User search could be used to record a Form responder’s email address instead of their logon ID. But other information about the user is available too – including their manager. This means you can use Flow to build workflows that require manager approval where “manager” is dynamically determined. How?

Select the “Get manager (V2)” action from the “Office 365 User” connector.

In this example, I am extending the sample workflow that creates a SPO list item from a Forms submission. I have inserted a step after getting the responder’s profile information. Click in the “User (UPN)” and select the input that contains the responder’s account (which, again, Forms calls an e-mail address)

Insert a new action

Search for and select “Approvals” and then select the “Start and wait for an approval (V2)” action.

Click the drop-down for “Approval type” – since I am only adding a single approver, the first two options are essentially the same. I can, however, use “Everyone must approve” to have more than one level of manager required for approval, or a manager plus static application owner.

To assign the approval, find the dynamic content from the manager lookup and select “Mail” from that section. Add details that will appear in the approval notification – something to clearly explain what the person is approving.

Save your workflow. Now when a Form response is submitted, the responder’s manager will receive an e-mail which allows them to approve or reject the request.

If they use the Flow site, they will see the approval in their Approvals section.

 

Did you know … you can store Microsoft Forms responses in a SharePoint Online list with Flow?

Microsoft Forms is a great way to compose simple forms and gather responses but analyzing the data in Excel … well I generally prefer processes to be automated. To use Forms data in automated processes, I store the form response in a SharePoint Online (SPO) list. How do you get the form response into a SPO list? Microsoft Flow.

Note – Microsoft Flow cycles are not free. We receive a number of free cycles each month for each Office 365 license purchased. The company-wide number of free flow cycles is quite large, but not infinite. Be cognizant of what constitutes a cycle. In this case, a cycle is each new form submission … if I expect 20,000 people to submit the form this month, I am using 20,000 flow cycles. Not all trigger cycles are as straightforward as this. As an example, Flow could send me an alert when I receive an e-mail from my manager. A cycle is not triggered on “e-mail from my manager received in my mailbox” but rather “new e-mail message received in my mailbox”. For a mailbox dedicated to a specific purpose, the number of messages received per month may be exactly what you want to process. In my mailbox, system alerts, ITSM notifications, script results and such would quickly use up a significant portion of Windstream’s free cycle allotment.

First you need a Form. When editing your form, grab the URL and find the “FormID” section. Save that string – we’ll need that in the workflow.

Before you start – if you plan to convert your personal form into a group form, do that before creating the Flow workflow. The form ID changes when a form is moved between group and personal forms, and you will need to adjust the workflow accordingly.

To store information in a SharePoint Online list, you’ll also need a SPO list. You can create one in a dedicated SPO site or the SPO site behind a Teams space.

Record the SPO site address and list name – we’ll need that in the workflow.

Now that we’ve got all of the components ready, we can build a workflow to connect them. Log into https://flow.microsoft.com

On the left-hand navigation bar, click “My Flows”.

Click the drop-down next to “New” and select “Create from blank”

Click “Create from blank” to confirm your choice.

Before you do anything else, give your Flow a descriptive name. Click on “Untitled”, type something else, and hit enter. If you don’t name your flow, it will get a default name based on the actions taken within the workflow … which makes it really hard to differentiate between five different workflows that take Forms responses and store them in SPO lists.

The trigger for the workflow will be that a new Forms response is submitted.

Select the “When a new response is submitted” trigger.

Select “Enter custom value” and paste in the form ID we recorded earlier.

Click “+ New step” to add a new step.

To use the data from the Form submission, we need to get the response data. Enter “Forms” again and select “Microsoft Forms”

Select the “Get response details” action.

In “Form Id”, enter a custom value and paste in the Form ID. Click in the “Response Id” field. In the right-hand navigation menu, click “See more”.

Select “List of response notifications Response Id”

You’ll see that the response ID is added to “Select an output from the previous step”. Click “Add an action”

Type “SharePoint” and select “SharePoint”

Select “Create item” from the “Actions” tab

Select “Enter custom value” for the Site Address and enter the site address we recorded earlier. Then click the drop-down for “List Name” – select the list into which you want to insert Forms responses.

Below “List Name”, additional fields will appear – one for each column of your list. Click into a column. In the right-hand bar, you will see the Form attributes that can be placed into the List column. For each List column, select the Form component you want stored in that field.

Flow is aware of column types – if you click into a date column, it will try to present Forms information that holds dates. If you find an option missing, click “See more” to see an unfiltered list of Forms attributes.

When you are selecting values to use in an action, the possible selections are grouped by the action. For a simple form/list combination like this, using the default step names is not too confusing, but if you build more complex workflows with more data elements … figuring out which “Get response details” is which becomes difficult.

It can be clearer if you provide a unique name for each trigger and action – the values from the LJRDemo form are clearly part of the “Get LJRDemo Form response details” section.

To rename a workflow element, click the ellipses and select “Rename”

Type a new name and hit enter

Click “Save” to save and activate your workflow.

Submit answers using your form, then check your SPO list.

Notice that what Forms calls my e-mail address is actually my logon ID. This is very useful for IT folks who can easily correlate logon IDs to actual individuals. Maybe not so useful for you. Flow provides directory lookup capabilities if you want to turn the logon ID field into something … more readable. Edit your Flow. After the get response details step, click the little plus to insert a new step.

Select “Add an action”

Search for “Office 365 Users”, select it, and select “Get user profile (V2)”.

Click in the “User (UPN)” box and select the Form respondent’s “e-mail”

Edit the action which creates the SPO list item. You will now have dynamic content both from the Form response and the Office 365 user lookup. In this case, I will use “Mail” in the Submitter column.

Save you changes. Since we’ve already submitted a Form response, we can test the Flow without submitting a new response. Click “Test” in the upper right-hand corner of your screen.

Select one of the previous runs (there may only be one!) and click “Save & Test” to re-run the Form response through your workflow.

Now the submitter is recorded as an e-mail address.

IIS Failed Request Tracing

I set up a PHP website with a really strange issue: site/path/index.php worked fine, but site/path threw a generic ‘something failed’ error. I added the suggested lines to web.config and got … another completely generic error.

You can enable Failed Request Tracing in the site to get a clearer picture of what is going on.

You’ll need to create a tracing rule that includes the return code you are seeing. Click “Add …” and create a rule that will capture the exception you are seeing. Once the rule is in place, reproduce the error in your browser. Click “View Trace Logs …” to open the location where the trace logs are stored.

On the local disk, you’ll get a web page with the exception information. Here, I’m seeing an error in ScriptModule-4.0. The ‘Request Details” tab contains more information.

My oddity seems to be related to .NET somehow … I don’t need CLR to manage code execution for my site, so the simple solution was to turn it off – edit the application pool and select “No Managed Code”.

Voila! My site loads using the default document.

 

SPO Guest Access Stops Working

I ran across an interesting issue today — Windstream’s got a really awesome SPO site for SD Project Management – tracking orders, equipment orders, 3rd party cabling installations, etc. The cool part about the site being hosted in SharePoint Online is that a customer can get set up as a federated partner and be granted access to see equipment readiness and installation scheduling within our system.

Guest access is an interesting concept – while I have an account in our tenant that is linked to my Active Directory account in our domain, you can also create links to accounts in other company’s directories. The guest account can then be set up to access our Azure resources – added to Azure groups, added to SharePoint Online groups, invited to join Teams.

A guest user had her computer replaced and could no longer access the site – SPO insisted that she was not a valid user. Looking in Azure AD, the account existed; the audit log even showed successful authentication events. I’m not sure if the computer replacement was a coincidence, the new computer had a different configuration, or if your browser stashes some information that allowed her to avoid authentication failures, but her guest account in our tenant was no longer working.

For companies that don’t have Azure AD, when an individual accepts guest account access … the guest account link in our tenant lists “Microsoft Account” as the source.

But when the company sets up Azure, the auth framework seems to get confused by the Azure AD account. Easy enough solution – we’ve got to delete the guest account that’s linked to their MS Account from Azure AD. Bonus step specific to SPO, a site administrator needs to use <site>/_layouts/15/people.aspx?MembershipGroupId=0 to delete the guest account from the SPO site.

 

Once the “Microsoft Account” guest account has been removed, the guest can be re-invited. They’ll step through the registration process again but the guest account will be linked up to their Azure AD account.

 Re-add the new guest account to whatever they were using & their access will be restored.