Month: December 2018

Did you know … you can save documents directly to Microsoft Teams?

You can create a document in a Teams file space (from “New”, select the document type); but, if you want to use a custom template (or if you just didn’t think of it and started the document on your computer), you can also save an Office 365 document to Microsoft Teams.

For the Teams file space to appear in the save dialogue, you’ll need to be following the SharePoint repository that underpins the file space. From the Files, select “Open in SharePoint”.

In the upper right-hand corner, click “Not following” to follow the site.

The change may not be reflected immediately on your computer – if your Teams space does not show up yet, wait an hour or two. Select “Save as” from the Files ribbon bar.

Click on “Sites – Windstream Communication”. The Teams space you followed will show up in the SharePoint sites list. Click on the team name.

Then select “Documents”

From there, you will see the name of each channel. Select the appropriate one, then navigate to the location you want to store your document. Give the document a name and click “Save”

The document will be saved directly to your Teams space.

Did you know … there are ways to split data in Excel?

Applications can generate data in formats that aren’t quite useful – glomming multiple fields together to make something unusable. And asking people to type information can yield inconsistent results – is my name Lisa Rushworth, Lisa J Rushworth, or just Lisa? Excel has several functions that allow you to produce consistent, usable data (without copy/pasting or deleting things!)

Flash Fill

Flash Fill will try to figure it out for you. Add an empty column (or more) and manually type one or two values. On the “Data” ribbon bar, select “Flash Fill” and Excel will use the data you’ve entered into the row to figure out what should go in the rest of the row.

The guesses aren’t 100% accurate – especially if your information is not consistent – but it’s a lot easier to delete the handful of things that are obviously not zip codes …

Than to work out a formula that extracts the same information

Text to columns

Text to columns uses the fixed-length file and delimited file import wizard on a column of data – essentially treating that column as a file to be imported. In this example, a DateTime value is provided in a way that Excel only sees it as a string. And, frankly, I am not interested on the exact hundredth of a second the event occurred. What I really want to do is group these creation dates by day, so all I need is the date component.

If you want to retain all the data, you’ll need to insert empty columns to the right – otherwise the data being split out can overwrite existing data. In my case, I only want to keep one of the new columns.

Highlight the column that holds your data. On the “Data” ribbon, select “Text to columns”

Select if the column should be split based on a fixed width definition or a delimiter and click ‘Next’

Indicate the proper delimiter – in this case, I need to use ‘Other’ and enter the letter T. A preview of the split data will appear below – make sure it looks reasonable. Click “Next”.

For each new row, you can specify a data type. Or leave the type set to “General” and Excel will try to figure it out.

If you do not need to retain the data, select “Do not import this column (skip)”. Click “Finish” to split your column.

Voilà – I’ve got a usable date value.

Notice, though, I have lost my original data. If you want to retain the original data, create a copy of the column. In this example, I want to know how many e-mail addresses use each domain, but I want to have the e-mail addresses in a recognizable and usable format too.

Text to columns will still replace the values from the selected column. But the copy will contain the original text.

You can even use Text to columns to sort out odd data that doesn’t actually get split into multiple columns. In this example, negative values have the minus sign after the number … which isn’t actually a negative number and isn’t usable in calculations.

Pick a delimiter that doesn’t appear in your data, and you’ll only have one column. When selecting the data format, click “Advanced”

Make sure the “Trailing minus for negative numbers” checkbox is checked and click OK.

And we’ve got negative numbers

Right, Left, Mid, and Search Functions:

You can also use the Search function in conjunction with Right, Left, and Mid to extract components of column data. In this example, we have first and last names. Since there are a few middle initials in there, we cannot just split on the space character.

These formulae aren’t perfect – Mary Ann will have ‘Mary’ as a first name – but

Working out where to start the text extraction and the number of characters to extract can get complex. I’ll usually include the Substitute function to simplify things a little – the zip code, in this case, is whatever is left over after we find the city and state.

Producing columns with the city, state, and zip code from the ‘Location’ column.

Making Laminated Fabric

I got lucky when I got the laminated bird fabric for Anya’s book bag — it wasn’t super expensive, it is really cute, and it has a thick lamination. Since then, I’ve not been able to find much in the way of laminated fabric. I don’t want all of her stuff to have the same fabric. So I’ve been experimenting with the laminate-your-fabric iron-on stuff. I’ve used ThermoWeb’s Heat’n Bond and Pellon 100 Vinyl Fuse. Neither are as thick as the lamination on the Robert Kaufman Slicker line, which is a bummer since that’s really what matters. TL;DR: I like the ThermoWeb better, but not enough that I’d pay extra for it or go out of my way to find it.

I bought these as yardage, so the precut and boxed lengths may be different. The Pellon paper has no print on the back, whereas the ThermoWeb has a grid print. Didn’t think I would care either way, but since *most* of my pattern pieces were integer inch rectangles, I was able to cut the ThermoWeb without trying to clip the fabric to the laminate. Since the point of laminating fabric is to make it waterproof, poking holes in it seems like a bad idea. The melting process seems to have eliminated the pin holes, too.

Both products work the same way — it’s paper backed vinyl. They both claim to be sticky to help with placement on the fabric, but beyond being slightly rough and plastic (hence a higher coefficient of friction than polished plastic) they’re not like sticky adhesive sticky. Cut your shape, peel it off the paper, overlay your fabric, place the paper on top of the vinyl, and apply heat with an iron (no steam!). Voilà, laminated fabric. Since my pattern has two of every piece, I placed one paper backing (smooth side up) on the ironing board. Then the fabric, right side up. Then the laminate, again right side up, and smoothed it out with my hand to minimize wrinkles and bubbles. Topped it all with the other paper backing, smooth side down. Doing this, the laminate could be slightly bigger than the fabric piece without fusing to the ironing board 🙂

When ironing, the Pellon smelled like melty petro-chemicals. Didn’t smell anything with the ThermoWeb, but my sense of smell is really terrible so it’s possible both smell when heated. One other thing I don’t care for with the Pellon vinyl — the laminated fabric curls. Kind of a lot — I’m putting it all under my cutting mat for the night to see if it straightens out. I’m sure it will be fine once I start sewing it, but it’s certainly not stacking nicely on my desk!

Tomorrow, I’ll see how they sew!

Usage tip: when you’ve finished sewing your project and are ready to turn it, take a hair dryer to it. Warm it up a bit, then turn it the right way about. This gives you nicer corners and makes it easier to turn.

Maths: The Wall

I think the wall is a completely stupid idea — based on emotion rather than statistics about the source of immigration violations, not an effective solution even if the problem were people sneaking across the border. But I am seeing a way to get this whole debacle sorted within the Executive branch (which may not be legal, because government budgeting isn’t quite the same as corporate budgeting). When we’ve got projects that are under-budget, the extra money can get moved over to some other purpose. Well, if we can shut down the government for no good reason during budget negotiations … what if the Executive branch shut down all those “non-essential” services for a while to free up money that can be transferred over to DHS? How long would the government need to be shut down?

 

For a five billion dollar wall (again, HA!) … since the FY2019 budget is like 4.4 trillion dollars, we spend 12 billion a day. Say 90% of that is essential. Five days of shutdown would fund the wall. Which doesn’t make the wall a good idea. Or mean Congress should just approve it to get the whole debacle over with. But it certainly says something about government spending that the wall is half of a day worth of spending. And it certainly says something about our government that it gets shut down over half a day worth of spending.

 

 $         5,000,000,000.00 The wall
 $ 4,407,000,000,000.00 FY2019 budget
 $       12,073,972,602.74 Daily spending
 $         1,207,397,260.27 10%
4.141139097 shutdown days

Using LDP To Browse Active Directory

One of the RSAT tools, ldp.exe, can be quite useful if you are trying to interact with Active Directory via LDAP but don’t know anything about the domain.

From “Connection”, chose “Connect”. Most domain controllers have A records registered for the domain name, so you can connect to the domain name.

Active Directory generally prohibits anonymous read, so you’ll need to bind to the directory. From “Connection”, chose “Bind”. If your computer is logged into the domain, you can select “Bind as currently logged on user”. If not, select “Bind with credentials” — in addition to the fully qualified DN of an account, AD allows you to bind with both userPrincipalName and sAMAccountName. userPrincipalName is userid@<domain.name> and sAMAccountName is domain\userid.

Now that you’ve logged into the domain, you can select “View” and “Tree”. If you leave the BaseDN blank, LDP will find the root of the directory partition.


Voila, you’ll see your domain. You can click around, or right-click the root of the domain and select “Search”. Look for something generic like “(&(objectClass=person))” to find user accounts. You’ll be able to see what attributes are used for what data.

Additionally, at the top of the window, you’ll see the hostname of the domain controller you are using and the root base DN for the domain.

The Military Industrial Complex

Will withdrawing from Syria and Afghanistan be move that swings Senate Republicans against Trump? Matt Taibbi speculates exactly this. I’ve wondered why Republicans stand by Trump so relentlessly — it’s not like Pence *wouldn’t* deregulate environmental and financial industries, create refugee crises at the border, and cut taxes without a care to deficit spending. Figure it’s got to be the 30 percent (or whatever) of the voters who actually think Trump is doing the right thing. Say the country is split pretty evenly between the two parties — and that the 50% on the Democratic side aren’t likely to be talked into voting Republican … that means Trump’s deplorables *are* the majority of the Republican voters. Now a historically successful (not to mention reasonable) ploy is to adjust your platform to appeal to more voters … but evidently no one wants to walk that path. Motivate your voters or put the other guy’s voters off works too — but the circus act that is the Trump campaign is about the pinnacle of motivating voters, and no one is sure who is running next cycle to dissuade people from fully supporting the individual. So they’re sticking by Trump … unless. Could the military industrial complex — and all of that money — be the thing that turns them?

The question makes me think of Trent Lott. Who had all sorts of faults, but public opinion turned on him when he said “I want to say this about my state: When Strom Thurmond ran for president, we voted for him. We’re proud of it. And if the rest of the country had followed our lead, we wouldn’t have had all these problems over the years, either.” Which … anyone who bothered to find out could have known Lott liked Thurmond. It’s like Capone going down on tax evasion — yeah, I’m glad the dude got put in prison (or resigned from office, or thrown out) but over that?!?

The Secret Plan!

Good news, we know know Trump’s secret plan to defeat ISIS — have Erdogan do it! Now I don’t think we’ve got much business in the Middle East (or Afghanistan). And “have countries in the region – Syria, Turkey, Iran, Iraq, Lebanon, Jordan – sort it” isn’t exactly a novel approach. But it is especially ironic for someone who touted their secret plan to subsequently say anyone else told him they’d take care of it.

All and all, it’s just another brick in the wall

Good news — at this rate, they’ll have collected enough money for “the wall” sometime before the fourth millennium.


The wall costs 5 bill (ha!). They raised 6 mil between 16 Dec and 3PM today — which I’m calling three and a half days because today isn’t over & maybe he created it at 23:58 on the 16th. A five billion dollar wall would be fully funded in a mere 8 years — or the year 2026. But there’s also the half billion a year to maintain the wall — failing to maintain the thing creates a dilapidated monument to Trump’s … oh, wait, that’s fitting. Their daily contribution rate exceeds the estimated operating run rate, so they’ll need to keep raising funds until they’ve got a trust to cover the wall’s expenses. 

And the whole thing is pointless since there’s a GoFundMe to buy ladders too 🙂 

In all seriousness, *this* is the only way I’d be OK building the wall. In fact, I think government services should move into a new non-profit category where donations are exempted from AMT and offset tax dollars owed. I could give 10k to the EPA (provided they’re actually enforcing regulations to, ya know, protect the environment) and owe nothing else in taxes. Otherwise I’ll fund arts, broadcasting, WIC, civil rights … I’ll find some agency Trump doesn’t know exists to ruin.

Did you know … you can lock “Format Painter” on?

How many times have you clicked a second time expecting to “paint” your format only to realize the format painter is a one-click deal-e-o. Well, it’s not — you just have to know the trick to ‘locking’ it on. Double click the format painter button — now you can paint as many things with the format as you like.

Microsoft Teams: Creating A Bot – No Code Method

If you want to create a simple bot that uses natural language processing training to allow users to ask questions that aren’t exactly what has been attached to the answer (i.e. I could say “I forgot my password”, “I don’t remember my password”, or “what’s my password” and be directed to the password portal URL without anyone having to come up with every possible iteration of the query),you can create one without actually programming a bot. You’ll need to enter some questions and answers – and it’s best to have a few questions associated with each answer so the bot can ‘learn’ what a real user might ask.

Before we start, understand how the billing works for Microsoft’s cloud services. There are generally free tiers for selections, but they are resource limited. When you first start with the Azure magic cloudy stuff, you get a 200$ credit. A message indicating your remaining credit is shown when you log into the Azure portal. Pay attention to that message – if you think you are using free tiers for everything but see your credit decreasing … you’ll need to investigate. Some features, like usage analytics,cost extra too.

Instead of programming a bot, you can use a pre-built bot and a question/answer knowledgebase maintained at https://www.qnamaker.ai/ Sign in to your company account

Select “Create a knowledge base” to start.

Click the button to “Create a QnA service”

A tab will open to the Azure portal, and you’ll see a wizard to create a QnA Maker resource. Provide a name and select your subscription – you may be walked through creating a new trial subscription. For the management pricing tier and search pricing tier, select the free plans unless you expect high usage or need to store a lot of documents. The selections below are the free tiers.

Disable app insights unless you want to study usage of the q&a – app insights is a paid add-on. Click create to create.

Return to the Azure Portal dashboard and click “Refresh” in the “All resources” section. Eventually, you will see your knowledge base appear.

Return to the QnA Maker tab. In “Step 2”, click the drop-down arrows to select the Azure Directory (you should just see our company), the subscription (again, probably just the one), and the knowledge base we just created.

In “Step 3”, provide a name for your knowledge base. If you have an existing Q&A file, or a URL with Q&A pairs, you can import them here. I will enter question/answer pairs manually later.

Optionally, add ‘chit chat’ – this allows your bot to respond to common things users type like “do you know Siri” or “how are you”. It isn’t needed, but it saves adding answers to “are you there” manually. This imports question sets to your KB, so you can remove any you don’t feel are appropriate for your implementation.

Click “Create your KB”.

And wait a few minutes.

Once the KB has been created, click “Add QnA pair” to begin adding questions. You can also scroll through the imported “chit chat”questions and modify/remove any you don’t like.

Answers use markdown formatting, so you can add hyperlinks, italics, bold, numbered lists.

Once you have added your question/answer pairs, click “Save and train” – training allows the bot to respond with an appropriate answer when the question isn’t exactly one of the questions provided.

Wait again … when you are returned to the q&a pair screen, click “Publish” – this implements your changes in production. You can stage changes, allowing others to review them, by waiting to click Publish.

Read the warning – assuming you actually want your changes to be in production, click “Publish”.

Wait some more, and you have an endpoint!

Copy the ‘stuff’ from the box into a text file somewhere. Find your QnAEndpointHostName, QnAKnowledgebaseId, and QnAAuthKey

Unless you are going to be the *ONLY* person editing your KB (and never go on holiday, never ring up sick), you’ll want to share access with others. Log into https://portal.azure.com and locate your “cognitive service”. Click on its name to edit it.

Click on “Access control (IAM)”

In the “Add a role assignment” section, click “Add”.

Select either “Cognitive Services Contributor” or “Owner” as the role (owners are allowed to manage all settings, including permissions whereas service contributors can only manage data within the knowledge base). Enter an Active Directory group name – you can enter individual people, but then someone will need to manually edit the permissions as people join and leave your group.

If you didn’t select owner, make sure you add some other owners (either a group or a few people)

Now we’re ready to set up a bot …

 Microsoft Teams uses Azure bots – so you’ll need to create an Azure bot. From https://portal.azure.com, click on ‘Create a resource’.

Search for “bot” and find the bots you are looking for. Either “Functions Bot’ and “Web App Bot” can be selected – I am using a WebApp bot because that’s what I’ve used for other bots I’ve coded. Functions bots use Azure functions, which are C# scripts, for logic processing; WebApp bots use WebAPIApp Service for logic processing (C# or NodeJS). If you’re not planning on tweaking anything … doesn’t much matter. Click “Create” to create the bot.

Give your bot a name – this needs to be unique. In the pricing tier, click on “View full pricing details” to see the options. Again, I am selecting the free tier. Don’t forget to hit ‘select’, or your selection will not be updated.

When you get to the bot template section, you can pick either C# or NodeJS – again, if you don’t plan on tweaking anything … doesn’t much matter. I am using C# because I know C#. Select the SDK v3, then find the “Question and Answer” bot template. Again, don’t forget to click “Select” or your selection won’t be saved.

Create a new storage location, disable application insights (it’s a for-fee add-on), and click “Create”.

Wait for it …. You’ll see a blue bar running under the bell icon in the upper right-hand corner of the site.

Soon, you’ll see a success message in the upper right-hand corner.

Refresh the resources on the dashboard and select the “WebApp Bot”. Under ‘App Service Settings’, go into “Application Settings”. Here’s where you enter the QnA information we saved after your knowledge base was published. Click in the “Value” column to paste in the values for QnAAuthKey, QnAEndpointHostname, and QnAKnowledgebaseId. Don’t have them? No big – go back to QnA Maker, open your KB, and select the “Settings” tab. There it is again!

Click “Save” to commit your changes.

Return to the Dashboard and select your “Web App Bot”

In the left-hand navigation column, select “Channels”. Click on the Teams logo.

The “Calling” tab will enable IVR or real-time media interaction with your bot – I have never done this, it may not be possible with our product set. “Publish” is to publish your Bot in the Microsoft store – not something I want to do with a bot designed to answer employee-specific questions, but I could see creating a Enterprise Customer Q&A bot that enterprise customers can add to their Teams spaces … in which case, making the bot available in the Microsoft Store would be convenient. Click “Save” to create a Teams channel for communication with your bot.

Read the publication terms and privacy statement. If you agree, check the box and click “Agree”.

In the Channel section, click the “Microsoft Teams” hyperlink.

This will open a new browser tab. Click “Cancel” so you can copy the URL.

Once you’ve copied the URL, click “Open in Teams web app” to interact with your bot using the Teams web client (or ‘launch it now’ for Teams desktop client)

It’s alive! Ask some questions and verify that your answers are being delivered. Check for formatting problems or bad hyperlinks.

I don’t want a bunch of people attaching to my test data, so the resources created above have been deleted. I’ve created a new bot with different details. Open https://teams.microsoft.com/dl/launcher/launcher.html?url=%2f_%23%2fl%2fchat%2f0%2f0%3fusers%3d28%3aab23fc5a-3151-495c-a02c-592b38148599&type=chat&deeplinkId=64b01c1e-5a2a-4456-a673-c02c3e04b532&directDl=true&msLaunch=true&enableMobilePage=true in Teams, you’ll be able to interact with my bot.

Q&A Maintenance

Anyone you have set as “Cognitive Service Maintainer” or“Owner” should be able to use QnA Maker to maintain the question and answer set. Log in to https://www.qnamaker.ai, select the “My knowledge bases” tab, and click on the knowledge base name. Modify/add question/answer pairs, then click “Save  and train”, then “Publish”.