- Welcome to episode 361 of the Microsoft Cloud IT Pro podcast recorded live on November 6th, 2023. This is a show about Microsoft 365 and Azure from the perspective of it pros and end users where we discuss a topic or recent news and how it relates to you. We are back this week with some more KQL goodness as Ben discusses how he used Azure Data Explorer with some exchange email analysis. And Scott shares some additional tips and tricks for custo.
They also discuss some upcoming changes to various Azure services when it comes to TLS 1.0, 1.1, 1.2 and 1.3. Okay, so we don't need to talk about lights on my face anymore. Do you wanna talk about your suggestion that I followed because you gave yourself a bit of a pat on the back for this one and I have
to admit you had a really good idea. Yeah, I - Think this is a first, so folks need to know that well, that you actually listened to me that Ben and I chat back and forth throughout the week and generally break things and make fun of each other with the different things we're breaking.
So you were having a little bit of a conniption, you were dealing with some Excel files and you were kind of talking about how they were getting into the hundreds of thousands, the millions of rows and you needed to analyze some data in there and I threw out why not just use Custo. Yes. So tell me about your CSV journey and custo and how it did or did not work out for you. - Custo itself worked out great.
My log files hit or miss a little bit, but essentially what I was doing, I have a client that we are working on an exchange migration for from on-premises to Office 365. And we were going down this whole debate of do we do hybrid? Do we do a cutover, bunch of details around it we don't need to get into.
But essentially it was they have a bunch of devices, scanners, printers, services and they are in an unfortunate situation where they don't have a complete grasp on everything that's connecting to their on-prem exchange server. So it was how can we figure out all of the different services, devices, et cetera, that are relaying through this on-prem exchange server that we need to account for when we go to Exchange online. So we used, there's a handy commandlet, it is only exchange on premises.
It is get mess, get message tracking log that essentially gets like a log of every email that goes through the server. So I ran this against a server and this is the text message that I sent you was it came back with a 350 meg ish file, I believe 350 megs, 434 rows in my CSV file of emails, 331 Megs, 450,000 rows . I was like, what could possibly go wrong?
Trying to open the CSV file on a computer, - easy peasy and then let's complicate things by you're on a Mac and it behaves a little weird with Excel anyway, so, - But I have 128 gigs of RAM and a whole bunch of cores because why not? But needless to say, it still does not perform super great. And then after the fact I realized that wait a minute, this was just one exchange server that they told me about.
They have two other ones and this get message tracking log only pulls it from the server you're on unless you pass a parameter to get it from a server at a time. So I went through the other three servers and this was like the middle of the road CSV. There was another one I got from another server that was like 500 megs and another one that was uh, 250 megs or something all combined.
It was 1.6 million rows of data of exchange tracking logs and you're, you were like, to your point, throw it in KQL and huh you know what, Scott's actually a smart guy. Maybe I should pay attention to him and listen to him every once in a while. So that's what I did. I actually used Azure Data Explorer and the handy little data ingestion, which makes it super quick. I mean I had the advantage, all three files are from Exchange exact same format.
So I just went and did a little Azure Data Explorer configuration, said go upload these three CSV files into this one table and I can add all three of 'em at once and hit go. And it took a little bit because I had to upload like 1.2 gigs of CSVs to Azure Data Explorer.
But let me tell you, going through and starting to write queries to find distinct IP addresses and to find IP addresses that messages were coming from that only had a single sender which indicated like some other service or something that wasn't just here's the bulk email from everybody. It actually worked out really well Scott, to sort through it.
There was not some data, there was some data I would've liked to see in there to better differentiate from something relaying through exchange versus Outlook, like to be able to see the actual client connection versus IP address and sender. And I'm still digging through some data but that had nothing to do with KQL and CSV. It's purely what get message tracking log contains and just not quite the level of data detail I wanted in it.
But in terms of being able to analyze it and sort through it and all of that, Azure Data Explorer KQL was kind of fun. It actually, it works really well for doing this type of thing. It's - Super easy to do, especially if you only have a couple million, even a couple billion records. Like it's not huge or massive. You can stand up a a party cluster basically really quick and get going with it. So we did this on the other YouTube channel or or to the cloud channel.
We kind of did a one click ingestion and looked at how easy some of this stuff is. But I love that you were able to take that and turn it around into something practical. Kusto is near and dear to my heart unfortunately. Well I, I dunno, fortunately, unfortunately I spend all my time in Custo Friday last week I was trying, we collect some telemetry, very similar kind of thing about clients like what's your user agent that's coming through? What are oss, are you on? Things like that.
And being in Azure storage, we get billions upon billions of requests. And so I was trying to wrangle this query for one day of data where it was returning something like 5 billion records in it. It's kind of a fun thing 'cause it's actually able to kind of wrangle it and put it together. Especially if you can think about doing your queries the right way. I'm gonna give you a quick kind of pro tip.
- Okay. - For custo specifically, so this is one that I just learned on Friday and it's super helpful when you're dealing with text in custo like a very long uh, a very long string record. So think like maybe something spits out a string where it's actually comma separated like in the string. So maybe it says like resource equals X comma client IP equals blah blah blah. And it's not broken out into different columns. So there's this operator in custo which is called parse ware.
So you can do all sorts of parsing in custo. You can parse JSON and you can parse version numbers and parse text and but use this parse ware operator and you can actually point it at that column. That's the big string and you can throw RegX at it and it extracts the data out of that string as you need by RegX and it turns it into calculated columns on the fly and it's super performance. So the way you would use this is, like I said, you have this big long string that you need to decompose.
Like maybe you're gonna, we're gonna extend it into multiple columns and do an extend extract, extend extract, extend extract. Yep. Which is typically how I go about it, which is super resource intensive, but this parse wear thing, you just throw a RegX at it or multiple RegX is even 'cause you can do like RegX per extracted column kind of thing. Super duper cool. I was having a lot of fun with that. Wonder - If this would work. I was just looking at my data because I have one column in here.
Oh see it didn't pull that out. I don't see it in there but I have seen that even in my CSV where it's, and I've seen it in other CSVs that you get from Microsoft where it's absolutely that it's like ACSV. But then the last column is like the context or the data and it's, you have your curly brackets that are wrapping like a monster Jason string or an embedded, it's like ACSV embedded in the CSV. So - Another way I, I'll give you a practical use case.
So I have a table that tracks storage account names. Yep. And as part of, so there's a column called account name and in that column the account name is not just the account name like the string like my account name, it's my account name semicolon. And then a Unix date time which is the date time that the account was created.
Uh, so somebody made the conscientious decision when they stood up this table that they were going to include the create time of the account as part of the account name just so we could have cardinality there and maintain uniqueness. 'cause you know I could create an account today and then 30 days later and then I delete it the same day and then 30 days later you go account, create an account with the same name over in your subscription. Technically two different accounts.
But I'm always going through this table and I'm doing this thing where I say, okay, take account name, split it on semicolon and then grab like the first part of the array and then grab the second part of the array 'cause I want account name and create time as two separate columns.
And now with this parse wear thing, I just go in and I say pars wear account name and then I can just throw a regex in and automatically split on the semicolon all in one step without having to extend out into multiple additional calculated columns on the fly. Which it turns out is like a nicety that you just kind of have to type less but it's also more performant because you're basically relying on a wear C clause rather than calculating after the wear.
And your wearers tend to kind of be compute heavy anyway. So why not do both at once and have your cake and eat it too - Nifty. I just looked through my data, my source context one is a mess in this exchange.
So this would not really work for source context because I don't, you know what the data you get out is nice but let me tell you, some of the data is so inconsistent in some of these cases, like this source context, - I shall pretend I'm surprised - One of it's one long string and then another email has like semicolon delimited stuff in it. And then another one is just completely blank.
And I mean I get it, your source context changes from email to email but it makes it a little bit challenging sometimes to parse through it when there's no uniformity to the data format in that column or none that I've can decipher super easily. - It's a little rough if you gotta wrangle it that way. So maybe you wanna know, like my other pro tip that I do in cases like that is custo has a case statement selector, I don't know if you've ever used this.
So sometimes I'll extend columns and then based on case, because when you do a case statement, you're basically doing if this then that, if this, then that and all the way down to having a default. But the cool thing is your predicate, like your if statement can be different for each and every case within that case statement. So you could have in that case where the, like the data's a little bit different between the two. You could say okay, case A when it meets this predicate then do this.
But then when it meets this other predicate then format it like this. But if it does this other predicate format it like this and then maybe do yeah and then maybe do unknown or dump something out the other side doesn't work with billions and billions of records. I've found like it is a little more than a little compute heavy but couple billion, couple hundred million like not a problem, doesn't even break a sweat. - So if I can figure out a way to define my cases, I could use that.
- Yes. If you have a predicate and you can figure out like basically what that statement is, like that wearer statement and split it out, you could technically put it all in the same column. - Nifty more stuff to play with, more data to go dig through. Do you feel overwhelmed by trying to manage your Office 365 environment? Are you facing unexpected issues that disrupt your company's productivity?
Intelligent is here to help much like you take your car to the mechanic that has specialized knowledge on how to best keep your car running Intelligent helps you with your Microsoft cloud environment because that's their expertise. Intelligent keeps up with the latest updates on the Microsoft cloud to help keep your business running smoothly and ahead of the curve.
Whether you are a small organization with just a few users up to an organization of several thousand employees they want to partner with you to implement and administer your Microsoft Cloud technology, visit them at intelligent.com/podcast. That's I-N-T-E-L-L-I-G-I-N k.com/podcast. For more information or to schedule a 30 minute call to get started with them today, remember intelligent focuses on the Microsoft cloud so you can focus on your business. Oh, so where do we go from here Scott?
We could go one or two directions, we could go talk about some changes to TLS or we could continue down client stories from Ben - . It's your show. Take your - Pick. No you, I picked the first topic you get to pick if you want to hear more client cases from Ben or if you wanna talk about TLS. - Why don't we talk about TLS? Okay. I've had some fun at work with TLS the last couple weeks. So there's a bunch of services in Azure. There - Are a bunch of services in Azure - .
Yes, there are a bunch of services in Azure. I was gonna say, let's take a step back. So we have this kind of operating principle of being secure by default. So maybe at some point we should come back and talk about the memo that just came out that was published about Brad Smith and our security org that kind of talks about our principles for how we approach secure by default security first for our customers.
So you're gonna start to see more and more of this I think across Azure, certainly Azure, like I'm close to it, you'll see it across like M 365, 0 365, the the entire stack. Things are gonna start to get more, not locked down but they're gonna get uh, put into a better default position, which could mean more work for you as a customer. So, so I think customers are gonna need to pay attention to this.
So one of the first ones to kind of hit is that TLS is changing in a whole bunch of places with regard to minimum supported TLS versions first of all. So there's services today that support like T LSS one T LS 1.0 1.1, 1.2 and then you're also gonna be see kind of forward looking changes in what's coming down the pipe as far as having access to newer TLS versions. Well newer in that they've been out a while and we're kind of catching up on them right now. So particularly with TLS 1.3.
So the first one that's near and dear to my heart is in storage land we have announced that TLS 1.2 is going to become the minimum TLS version for all Azure storage accounts moving forward after November 1st, 2024. And it's not just moving forward, it's also we're deprecating support for 1.0 and 1.1 for existing accounts as well. So that's something that folks need to potentially take a lookout for.
So if you have pinned ATLS version like you've pinned to TLS 1.0 or 1.1 and you're doing that today, you have a year, you have until November 1st, 2024 to go ahead and flip the switch and get over to at least TLS 1.2 as a minimum supported version. - Yeah and I sent you a text on this one because I'm curious if somebody's listening and has this information.
I have not been able to find it but there were some Microsoft Cloud workshops around high availability that I worked on and that are still out there and to the best of my knowledge, okay, backwards a step now I'm going a step backwards SQL server, - It's our thing today. Yeah - It is SQL Server. If you're doing a high availability cluster and you want a cloud witness for your SQL high availability, you can set it up with a storage count. It's all built right into SQL Server.
However, if you do not do TLS 1.0, if you actually do 1.1 or 1.2 a SQL server does not use T LSS 1.2 or will not work with T LSS 1.1 or 1.0 No it won't work with 1.2 or 1.1 when using an Azure storage account for your cloud witness. I have not seen anywhere across the intro webs where the SQL team has updated SQL server so that it will work with T LSS 1.2 in a storage account for that cloud witness.
Yet what that means is if there's somebody from the SQL Server team that's listening to this, I would recommend making some changes to SQL Server to support T LSS 1.2. Are you gonna have a whole bunch of high availability SQL instances in Azure using an Azure storage account for your cloud witness that are going to break come October 31st, 2024?
And then along with that, I don't know which versions of SQL they would go back and add this into SQL's expensive depending on how you license it and how you set it up in Azure. I know a lot of people still running old versions of SQL Server, people tend to put SQL server up and as long as it's working they don't tend to touch it until it's out of support.
I'm curious to see how one, I don't know how many people use a cloud witness for high availability SQL servers and Azure, but I'm curious to see what effect this has in that particular scenario. Just 'cause it's one in particular that I was aware of and have come across regularly where TLS 1.1 and 1.2 aren't supported yet, particularly around cloud storage. - I would've assumed this one would've been fixed a couple years ago.
So there was a SQL server has lagged in some areas in the past, so, so I, it's been a while since I've done a cloud witness so I know for backups, so doing like backpack files over to SQL Server. Yep. That used to be problematic as well because that from a SQL to a storage account connection did not support TLS 1.2 but they've basically patched and cumulative updated and hot fixed their way out of that one.
I would have to go play with a cloud witness and see, - Maybe we should do this, maybe we should go do a, - It's been a hot minute since I've tried one but maybe - We should go do a YouTube video. It's - A good call out. And fortuitous timing, I think we're going to talk to the sequel team in a week or two here to do our big powwow. So , I should bring it up with 'em, - Ask them about this one because again, I couldn't find anything and I have the same assumption as you.
I'm like, I would think with sql, whatever we're on now 20, are we on 2022? 2019 SQL subscription edition because that's what we're doing with everything now , I would've doubt they would've fixed this given to your point how much Microsoft has been trying to get rid of 1.0 1.1 and really use 1.2 for everything. But I am not a hundred percent sure 'cause I can't find anything documented nor have I necessarily gone and tried this with the most recent up-to-date patched version of sql.
And I would be also be curious like how far back you can go before it would break. So let me know, go talk to the SQL team, ask them the hard questions. - I would tend to think most of this is patched most of the way out. So I'll throw a link over in the chat just so you have it and you can maybe take a look through and let me know if I'm off there. But it looks like all the current supported major versions have been patched up to TLS 1.2.
Now that being said, no place in that article of known issues that it fixes. Does it call out cloud witnesses? So mileage might vary with that. May vary with that one. - Alright, we're gonna go do some testing sometime, but yeah I've, if you - Have the ability to spin up a cloud witness real quick, I wanna try it out. - I'm not gonna do it right now. We'll have to do it on a YouTube channel.
We can go through the one of those MCWs or something or part of the way through it 'cause that one's a long one. All right. Okay, other TLS stuff. So - We've got TLS 1.2 for storage is going to become the minimum supported, per supported version. That is November 1st, 2024. The other TLS thing that is out there is you're gonna start to see services also flip over to TLS 1.3 and the first one that I saw announced so far is Azure App Service.
So app service for web apps, which includes your apps service plans, which also run for things like functions and logic apps as well. So this is kind of rolling out slowly TLS 1.3 for app service, like your underlying kind of compute and hosting engine is starting to roll out now. It's going to be rolling out over the course of the remainder of what we have here in 2023 and 2024 and rollouts worldwide.
I'll put a link in in the show notes for everybody to go and take a look and see what's going on there. But basically kind of it goes through canary staging and the end user acceptance, all that stuff. And it'll hit us clients first, which is an interesting one given the presence of some really big retailers and things and coming into the holiday season, who knows who runs preview and who runs on supported GA stuff. So we shall see.
But I have seen customers here get into trouble like when they do pinning for things like TLS versions, when they do cert pinning, all that kind of stuff. Like I understand why it needs to be done, but it's a call out that your runway is potentially limited with some of these things. - Yeah, well it looks like they already started rolling it out the end of October. October 23rd.
It began rolling out and like you said, kind of through the end of the year and that's the preview, we will continue rolling out TLS sometime early 2024. Yeah, but that's all under the preview. Will it just be in preview then yet through January, 2024 is the way I would read this. - That's the way I, I read it for now. So I think it's a smart move to do it that way. So one of the things that happens is now you're going to have access not just to TLS 1.3 but to new cipher suites within T LSS 1.3.
So you might also have a dependency within your stack on a certain cipher suite that you've been waiting for. So now it gives you the chance to kind of potentially play around with those and see what's what and what's gonna work and what's gonna break. - So yes and I would expect, I mean app service for web apps and then functions and logic apps going at the same time kind of makes sense since they all run on the same service. - Funny enough, it's just web apps though.
It it, so it's the kind of public compute side of web apps. It is not A-S-E-A-S-E-V one is still kicking around there. V two is out and about as well. And surprisingly TLS 1.3 is not there yet. So again, your mileage may vary huh? In the ever landing cha ev ever changing land of Azure previews. - Yes. And I would imagine we'll start seeing more TLS 1.3 stuff coming. We have ignite. Well by the time people hear this episode, unless we switch orders, Scott Ignite may have already happened.
Well by the time they hear this one Ignite will have happened. Whether we have an Ignite episode before this one or not, we'll have to play with schedules because Ignite is happening a week from when we're recording this essentially. Yes, - Rapidly approaching. - We shall see. Maybe there will be some TLS 1.3 stuff at Ignite. I think there will be some interesting announcements coming out there. Any other TLS stuff you wanna talk about? Those - Are the big ones that I'm aware of so far.
We'll see where the rest come in. So - We're gonna talk about one more deprecation that has kind of Ben's client woes. We don't need to get into all my client woes around this particular announcement. This is almost sort of an I told you so not to this particular client but to other people because I have been saying this for years, but there was an article today, Microsoft Deprecates three features in Windows 11 version 23 H two. And guess which one of those features is?
Scott, you wouldn't guess if I wouldn't have told you this. - I've been waiting for this one to drop for a long. Like it was inevitable that this one comes like - It's totally, and I told you so it's going to happen. Yes, - The web client web dev is finally going away. Woe be to those who continue to use SharePoint and file explore views.
- Yes. And I have been, there is one client I've been having this discussion with and they're like, but it works to open and file explore on one computer and not on another computer. And I'm like, why are we even trying this? Microsoft should have taken, I mean web dev is going away.
To be fair, Microsoft should have taken this out of the SharePoint UI like the day after they came out with it because it's been giving me woes and heartburn and all of the bad things since the day it came out because of all the weirdness, particularly with SharePoint. There's always been weirdness around file modified dates on functionality because it's not really doing a sync then it's opening it like a file server and there are still people that try to use it and people that try to use it.
I say stop. It's going to go away. Microsoft has at least labeled it Legacy up until today, November 6th. But you should not be using web dev to open files and SharePoint. And now I finally have something to point to and say you really should not be using web dev to open files and SharePoint because it has now been deprecated in Windows. There have been issues with it now for a while, ever since Edge came out because it is a feature, at least it's in SharePoint.
I don't know. I would assume it would affect other places too because it's part of the HTTP, the hypertext transfer protocol for those of you that have always heard it and never actually known what HTP stood for, where you had to use Internet Explorer to even use web dev. So as Edge has come out on chromium and they've started pulling out some of the IE. Supportability stuff and IE has started getting deprecated and support for IE has gone away.
Web dev in Evolve itself has already started getting a bit unpredictable because of losing browser support. - Just a little unpredictable. - Yeah. Yeah. A little more unpredictable than it's been since 2003. But yes, now it is gone. We can wave goodbye to it. And if you're still using it, please stop once and for all , even if it still works on Windows 10 and you have no plans to upgrade to Windows 11, just - Stop. That's the plea, huh? Please, - That's - The plea. Just stop.
- Please just stop before I start crying on the podcast. - But won't you think of the files come on. - I do not care for the files or the pain that goes with dealing with files that are opened over web dev or mapped network drives that are SharePoint because we've had, and we can go find other episodes.
We have talked at length about this so we don't need to get into that of why SharePoint is not a network file share, but it is not a network file share, therefore WebDAV should not be a thing nor should interacting with SharePoint files in that way be a thing. End of soap box off the soap box. - End of soap box. Well so the interesting thing that happens here, , I always kind of cringe a little bit. So this is a change that's coming in Windows 11.
It's not something that's like being back ported. 'cause we're so far gone from Windows 10 and unfortunately there's still lots of people that run Windows 10 as well. So they're in the clear I guess - Is Windows seven still kicking around out there? - I imagine it is. I probably have one or two former employers who are still running fleets of desktops on Windows seven because they can, I mean Windows CE is still kicking around, right?
Like I don't know how it is in other countries, but over here in the US it powers your favorite uh ATM. - Tim, what about Windows Me? We can get on the Windows. - That was the one with the good startup sound. - Yeah, Sean said in Discord. Oh it is unfortunately about Windows xp. So yes, windows XP is still alive and kicking as well. , that's a whole other topic of conversation. That one's been outta support for how many years? - For one or two hot minutes now.
- Yeah, so that is I think our news, oh, one last bullet points Scott. We don't need to talk about this either. We mentioned in a previous episode, and I'm not gonna say last in case these get out of order with Ignite the automatic conditional access policies and Entra being created by Microsoft. And I didn't have a good reference article for it today on November 6th. Microsoft did come out with a blog post about it.
So we'll throw a link to the, that blog post in the show notes if anybody wanted to see the Microsoft official announcement today rather than the stuff that I found on the socials when we recorded that other episode. Perfect. - I've been looking out for that one. Good catch.
- You got it. And finally, girls Who Code Scott or Fundraiser Girls - Who code tis the season of giving help a young woman in your life or contribute to some other young woman's career to enter the wonderful field of information technology and give to Girls Who Code. So if you go to give dot girls who code.com/ms cloud IT Pro, you can contribute to our campaign or go ahead and contribute to another campaign for Girls Who Code if you so desire, if you get like a match at work or things like that.
But yeah, go ahead and make it happen. I would love to see us raise some money for Girls Who code through the podcast once again this year. - Yes. And try to beat last year's goal. So by December 31 we're gonna see if we can better what we did last year with those donations to Girls Who Code. So see what we can do. And with that Scott, we can sign off for the day. - Excellent. . Well I don't get to sign off for the day. I'm going to Redmond for a work trip.
So I've worked for Microsoft for three coming on three years now and I've never met anybody I work with and I'm going on my first work trip. So that kind of came in hot and I had to book tickets the last minute. But uh, I am going to Redmond tomorrow and I'll be there for a week. So that'll be fun. It - Just clicked. Scott, you're leaving right before Ignite then. - Totally. So I think Ignite like the sessions start on the 14th through the 15th.
I'll be flying back in a red eye on the 14th. . Yeah, - You should have just stayed two extra days. - Holidays are coming up. I I got another vacation. I gotta get to you gotta All right. My wife would be mad at me. Sounds good. My dogs will be mad at me. My kids will be mad at me. Hey - Scott, I won't be mad at you. - Okay, that's good. I appreciate it. - I'm here for you. Alright, well enjoy your trip.
Safe travels. Say hello to all the folks in Redmond for us Bug the sequel team about cloud witnesses and travel safe and we will see you next week for Ignite when we talk about all the fun stuff from that. All - Right, sounds good. Thanks Ben. - If you enjoyed the podcast, go leave us a five star rating in iTunes. It helps to get the word out so more IT pros can learn about Office 365 and Azure.
If you have any questions you want us to address on the show or feedback about the show, feel free to reach out via our website, Twitter, or Facebook. Thanks again for listening and have a great day.