BSidesCharm 2022 - KQL and Azure AD Workbooks - Corissa Koopmans and Tosin Lufadeju

foreign

hello I am Carissa and this is my colleague tosin and we are going to be presenting on kql and Azure ad workbooks for the blue team and so I'm assuming that all of you guys are part of a blue team but even if you're not you might like this session so also if you guys get hungry since it's noon feel free to go get something to eat and come back happy to do that um also everything that we're going to present today all of the resources everything we're going to give you a GitHub repo and everything will be in there so if you missed something don't worry all right let's get started so I am

Carissa copeltman's and I am a product manager for Microsoft I've been with Microsoft about seven years and I've been in the identity space for about three years and as you know identity has become very important especially when it comes to cyber attacks it is always the focus of Bad actors so hand in hand with security and that's why we're here today I'm going to introduce toson but he's going to speak here in a little bit um tosin is my colleague he's also with Microsoft and we work on a team together it's called get to production um and that basically means that we sit in engineering but we closely work with a lot of our Enterprise customers and we

help them deploy Azure active directory and we try to keep them as secure and safe as possible all right so today's session our learning objective is really to share some threat hunting techniques and give you guys some extra tools that maybe you haven't used before maybe a raise of hands has anybody does anybody use kql today fabulous okay so I don't know what level everyone is at but we're gonna just assume you guys have heard of it before um but I have provided a lot of extra resources if you are a very new beginner but at least today you'll get to see like what you can do with it and then you can go and either become more

advanced or just you know get the lay of the land so we're gonna just walk through a quick overview of what workbooks are um some business cases that can really put it into perspective on how the blue team can use it and then we are going to dive into some threat hunting with kql and some tips and tricks that you can learn and then I have a last section which is really just an ask from you guys because um we would love your contribution all right so I'm assuming that probably everybody has heard the term zero trust and and actually maybe even seen this slide before but this is this is something we show a lot in our

presentations um at Microsoft but it is very it's supposed to be very product agnostic basically every company needs to consider these pillars when they're considering um securing their entire environment so that includes identities it includes data applications their infrastructure and their Network so they have to consider oh and endpoints so they have to consider all of those things and then across the bottom here is the visibility and analytics where we're really diving in today so there's tons of tools to use and we know this but in terms of visibility and analytics we're going to show you what we can offer inside Azure active directory all right I'm gonna pass off to toast

thank you everyone um like she said my name is tosin I also work on the GCB team and she really just introduced most of what I was about to kind of lead up with so I'll just repeat it again um we work on a team that interacts with customers the my main job is to help deploy Azure 80 features and then to do this one of the things I do is sit down and understand like use cases so what exactly are you trying to solve what problems do you have how can the products we build help in your productivity and increase um The Experience you have using Azure media as a feature right but we're mainly on the

identity side of things so users devices locations policies that's where we sit that's where um our mate is but then there's this in-between part where it's like where identity but then we're also working with security and so you would have a bunch of security questions that come our way and this is where workbooks actually fall in right so one of the questions I get a lot is there's so many portals now we have so any data sources we have so many reports but then do I need to go into every single one or is there like a way I can put together this unified Visual and get insights on the data I have and that's where workbooks

come in so generally the way I explain workbooks is think about this like a collection of all your data sources and one reports that gives you all that data and so whether it's coming from the logs you've captured whether it's coming from your custom endpoints for some external sources or um even just your Azure ad graph and graph resources the workbook is your way of presenting that now um with Azure 80 you have some things right so uh with Azure ad you you get your overview you get your logs so you get the sign-in logs you get the audit logs um we have conditional access we have identity scores and the way I explain identity scores is think about it like a

benchmark if you follow all the best practices you can get 100 for example if you do some of it you might be on 80 60 and that way you got to see what the difference is and you can actually feel those the differentials and follow recommendations to get up to our part excuse me but then if you go beyond just doing the the basics you get into what the middle part there which I call the advanced capabilities where then you can start to take some of these logs and then start to send them out to external resources right so um if you process your data in Azure ad you might be using log analytics but

then I have customers who say yeah that's nice but then we do everything in Splunk and so it's like yeah you can actually take that data as well send it to Splunk and you'll be fine as well and then the next one or the next iteration to that is how do you take away the manual interaction right so how do you introduce some automation to things and that's where you go into using your logic apps and then um that experience gets even better than um it is right now now um getting data in the first one is excuse me my book yeah so getting there okay that's better getting data in the first one is

log analytics right so how do you get the data to even show up in the workbooks the first one is setting up your workspace so if you go into diagnostic settings well by before I even say that is anyone familiar with the Azure ID portal here great all right good so under monitoring you have um a bunch of things you would have local genetics you would have the workspace and then diagnostic settings if you go in there to the left you have all the logs that are being captured right and then you can send them in so you at this point You're Building reports based off those logs have been captured so for the top one is when you want to

do this using the Azure ID workbook and then like I said it's not just Microsoft Centric so if you're sending elsewhere you want to go and also make sure you're streaming to event Hub and we'll talk about why that's important later on but so much about workbooks what do they look like uh you have them in here this is what the overview page looks like um on the top part that you have new which just indicates that you don't have to use some of this pre-canned workbooks we've made you can actually go treat this like a canvas start a fresh build everything you want but then also in here based off feedback we've gotten based off situations that

have happened in the past we have some default reports that we advise customers to go look into if you want to get started go check this once it would show you simple things that we think are important for customers to see and then you can start to make customizations on those as you please now you don't have to worry about the changes you make here um effects in the global repository that's not that's not the way it works you could make your customizations in here for your scenarios and it just saves a different version for you um contributions are welcome in here and Carissa will talk about that in a bit but then this is what you would see

today if you went into the puzzle so um business cases right so how do you even get started or why do you want to use this um if you're a security folks you're probably thinking well you want to identify threats you want to do it faster some things I've heard is well we know how to pull this data but then we don't have a means of showing it to people who are not super technical right we need some visualizations that we could use workbooks coming really add in here we also want to do well we know what we want to be able to show what is normal and what's abnormal really quickly right so with the trend graph you see what the

Baseline is and one day you see a spike it rings the bell in everyone's head you understand what exactly you're looking for and from there you just um go on so these are some common use cases and I'll just briefly show some reports that we have that address a few of those so really quickly what happens when um you're having multiple changes to a service principle an application right uh if you have more certificates being being um updated if you have passwords being changed just modifications like that right you want it to ring a bell right you want an alarm you want someone to actually pay attention to this go investigate make sure this is done

correctly and there's a report in there that is pre-cad that you can actually get started with that's one example there another example is what if you have service principles and I think life's quickly but what if you have service principles for instance and then um someday you start to see new permissions being added to them uh the common ones we call out the customers would be the read and write permissions because those are the most powerful ones but then sometimes read all alone is worrying a Troublesome right and if you um have so I just have some examples in here so things around Microsoft graph things around um exchange with another example right where you want to pay attention to that

right so you won't be able to go in there in this example I have a seven day range you can go as far as your data um login mechanism goes we advise to get richer reports that you store data for a little longer and then you start to see well who made that change so you have the initiating user or apps name there so you can actually verify you have a little explanation of what that permission does and then you get to see the rules that were added okay similarly what if um a service experience boiler application gets added to a role or a group uh one day you wake up and somebody is now a global admin or

somebody is now a company admin you want to be able to catch that real quick because that might be a sign that something has gone wrong and again same logic in this case I use 60 days just to show what that range is and then you can see what the operation is as well as who did that um so let's move a little bit away from that and let's think about like Risk in this sense so risk events risky users what you're looking here at here is a map that shows you your risk detections right so what's a quick way to see where risky events are going on or where risky users um are this report would show you

that right now and the bigger circles just mean you have a higher counts while the colors would indicate whether it's a high risk event medium risk events or low risk um event as well as well as a breakdown on the therefore the time frame now we talked about what's normal and then uh finding anomalies right so the first thing that stands out to you looking at this report is the spike right so February 6 you have a big spike and um it just kind of flattens out that brings a bell to me because I'm like I wonder what happened that day that's something I want to go investigate but then also we've gone further ahead to even break down what we

think these risky events are by types and so you start to see a large portion of that is a new country so toasting usually would sign in from the US and then all of a sudden it was inside from France like well it's something uh going on that my risk score probably goes up and it's something to pay attention to or Microsoft has gone ahead and um identified some IP addresses as malicious and then you start to see login attempts from those in your tenants so you want to pay attention to like very simple indications that there might be trouble moving really quickly what about in situations where you collaborate right so you have your

um tenants speaking with users from another tenant a lot of times you do that because you trust the other organization but then do you want to know how many attempts are going in both ways so from you to them from them to you do you want to have a good idea of like what applications are being used on the other end and then do you want to know which of your users are doing that interaction as well um reports like this would do the same thing right so you can go in go by time frame you could look up users and you can look up specific applications if you're worried about those right and then um the last one from the

pre-can reports would be this one right so what about the gaps what are the obvious gaps that you might be missing in your environment right now do you have um applications that don't have any policies around them again we're from Identity right so I talk conditional access I talk guidance protection and so it's like um you have this high impact application but then there's no policy guiding it for some reason you won't be able to catch that real quick now that might have been missed but then your workbook shows you and it's something you can actually go back and try to remediate real quickly another one is do you have locations where you have no

conditional access policy blocking and that's what you see on the map to the left as well so this one really obvious Legacy authentication we talk about this quite a bit because most of what we do at this point we're preaching MFA make sure that's at least that's the basic at this point right so that's the Baseline we require most applications to have MFA if they can the only reason why you probably would not be able to prompt that now would be a legacy auth and as much as we're trying to phase that out is actually a deprecation in October I believe for legacy Earth but for now we give away for admins to identify that

really quickly so just run the reports you can see interactive sign-ins and non-interactive sign-ins and then you can start to look at which ones which applications people are still signed into using Legacy Earth and then you could gradually start to cut that down or even use this right so which users are still using it and then you gradually take those down so um this is well these are like a couple of reports that we have right like I said you have a bunch of them in there multiple categories you can go in and play with the idea is this is the visualization right so what's running behind this is kql right so that's how this is all built and that's what I'm

curious I'll spend some time now showing you some of the code and some of the ways to manipulate that so you can take what we've built as the starter packages and then start to make changes and come up with creative ways that actually solve your own issues and hopefully you can also come contribute to the community and we get this quite even bigger and bigger so at this point I'll hand over to Carissa and she'll uh get into the code thank you all right are you guys ready for now like seeing the nitty-gritty all right so I'm gonna go in basically everything that was in this workbook is um built on kql that looks similar to

this right and it is a very small screen so I'm gonna try to zoom in if I can there we go I don't know if that helps can you guys see it in the back okay um perfect so I'm gonna I have slides that are gonna really go through um the code but I'm gonna highlight something else before we go get into that so as I mentioned you know everything is built on this kql and in log analytics no matter what you put in here you can create a new alert based on this right on that on that code very easily and it's basically just this button here new alert so the idea is is that in this situation

we were saying okay how can we know that our administrators are possible like going through MFA are there any administrators that are failing MFA and that's something we want to alert on we want um our security team to investigate these things immediately so in this instance this that's what that code is doing um I will walk it through when we go into the other slide that's a little bit more clear but you can see the results that like the global administrator had what 330 failed attempts in 24 hours to me that would be concerning especially if I knew that in my environment I only had you know seven Global administrators however you know there could be cases where these

companies are huge right so this is something that you need to cater towards your environment it can't be something that is um all the same so I'm actually gonna go um into the portal just because I like to so one second makes it much easier for me to show you all right

oh yeah you're not I'm not sharing it darn it okay I don't want to change it I'll go back I'll go back oh okay I gotta yeah I can't see my arrow so I have to there we go

okay I'm back all right let me make this bigger so I can see okay so I luckily I took screenshots because then I wouldn't be able to explain to you so this is of just abuse of the of the actual kql I had just clicked the button new alert and then it brings up this page and so what this page is going to allow me to do is to really specify the type of alerts I want to set up so you have this kql which is finding um it's identifying where any Global administrator is hitting um MFA and failing having a result of fail and then counting it so there's a few different ways I can

aggregate my the count of the information that I want to track and you know in this sense I can also I can look at the table of rows or I can look at the count um but I'm deciding that I want to just see how many rows of information or how many fails I have every five minutes that's my aggregation granularity and then go on to the next slide I also can decide my alert logic and this is going to say okay it's greater than say 20 like if I have 20 failed mfas in five minutes I should be very concerned or maybe someone will like want to see it if it's three or four you can decide

that you can set up your security policies that way it also will tell you how much like alerts how much this is going to cost um which is it's not really pertaining you know in a to everyone but if you're using Azure active directory and you if you have customers that are using Office 365 they already have Azure ad so in this sense this is something that you can do really specifically for your Office 365 if nothing else I mean you can bring all your Enterprise apps into this but just know that if you have somebody who has Office 365 in their environment these are tools that you can help to keep them safe all right so I did an aggregation

granularity of five minutes and then the frequency of evaluation has to be that or less than or sorry that or greater than and so in this sense I'm just saying that I want to check oops I want to check the the count every five minutes and then send an alert if it's over 20. and then I can also name this alert Rule and I can change the severity which is great and then here is where we're going to go into the kql of actually what's being said so in in this sense I just you know I'm calling this as administrators fail MFA code and this is where um you may have you know a lot of you

have already used kql and so you may know this already but I kind of want to just tell you like the main things that have helped me to create these workbooks that has helped me to become better at this and also like do some cool stuff so I always use the let function the where function the MV expand so that's why I like this query because it shows you a lot of those for example the identity information is where we're going to see the role information so we're going to be combining two different data sets entirely we're getting log information from Identity info and then we're also getting log information from sign in logs and

actually I like there's a third from aad non-interactive user sign-in so those are going to be our service principal sign-ins all right so we start up here and we're really just saying like we're going to create kind of this variable this table that's going to be terrible table name String so that whatever we create later on it can be applied um to the to whatever table we put we we have created and this is going to be later on in the query and it's basically going to say okay we're going to have identity information where and this assigned roles part is is a column in in the in the log data and we we're only

going to look and we only care about that contains admin so there's many many roles right there's many roles that you can be but we care about the administrator roles and in in the mvx band has a couple different things that I think are super useful so when you're creating new columns sometimes you need to change the data a little bit um also there's also going to be times where maybe the the data is also in a dynamic um like Json format and that's a way for us to save our customers money because we you know having all this data you know we can't create a separate column that takes up more space so we sometimes

we put it all into one in Json format and so you might need to parse it out but I'm going to show you that later but in this instance of MB expand we're looking at assigned roles and then we're we're taking and we're going to make new columns out of it we're going to extend the roles to um we're gonna make a column called roles and that's going to contain the string version of assigned roles and then we're going to also take the account UPN and we're going to lowercase it and this is the reason that we have to do this line is because we are planning to merge and to unite and do a join

so in this sense we say okay where the roles contain admin and then we're just looking at distinct like I don't want to know um every single one I just want to know the distinct roles in account UPN so that I'm tracking um I'm not counting double counting right and then we're doing a join and if you guys are familiar with SQL at all this is going to seem very familiar a lot of the times it's um it's like kql is very similar to SQL but there's these slight nuances so inside our documentation and I'll share this with you they have a lot of SQL to keyquel I could call it equals not that uh SQL to

kql translation and also I think there's uh kql to there's a few other languages that it can be that it can we have it transferred to as well as well as like how you use it in Splunk so I'll definitely link to those all right um okay so in this instance now we're doing a join and we're bringing in where the result description this is where we're deciding what type of authentication method what type of MFA did they do so we're only going to care about MFA second factor multi-factor and then we're looking at these result types and these result types we know and I know but you guys don't if you're not familiar with these logs that these are

all failed attempts so we're just listing the different types of results and then we're doing a join and then we're going to create a couple new columns again and in the bottom part we're kind of referring back up to this add Funk to the table and we're saying okay we want to do this for sign in logs and we're also going to do it for Azure ad non-interactive sign-in logs and once we join those and bring those together we're able to have this great column that is going to combine this sign in information with the role information and then we're going to summarize it and we're summarizing at the end like every query in order to have this result needs

some type of scalar function like summarize and you'll use summarize all of the time uh so definitely like project and summarize are also very very common to use so project can also be a function and it can kind of serve as summarize sometimes but it's also a way to break down your query and like say I only want I have all of these all this data but I'm only going to focus on like five to six roles that's what the project can do it can take away some stuff which makes the kql lighter and it's going to process faster and okay so we're summarizing the count of failed attempts attempts by rolls and then we are going to do um an order by

which is very similar so this is exactly you know an example it's just one example of things you can do to set an alert for and then you know in terms of automation like what uh Tolson was talking about you could say okay set an alert and also send this to servicenow create a ticket have somebody look into it these are the idea like we want you to um be able to start to find and track these things and we know not everything is going to be an actual attack but it could be something that needs additional investigation and if we can automate some of this that's the dream right we don't want to have to do this manually

all right are any of you falling asleep not yet okay okay that's fine or I mean if you're hungry like I said go get food all right so we're going to get into some tips and tricks and this is just to help you not bang your head against the table like I did numerous times when I was learning and I'm assuming that all of you guys are way smarter than me and you probably already know um way more advanced coding than this but I'm gonna but then I'm going to decide that that's okay I'm gonna still explain it and if you if you get nothing out of it I'm sorry but I hope you get

something out of it so laying out a workbook or laying out something that I know well let me I should ask do you guys ever have to create reports to show your leadership at all on the stuff that you're doing and protecting your environment from okay good that makes me happy so then this could become useful to you um we want these these visualizations to be not necessarily easy for you to understand but easy for your manager and your leadership to understand and to explain so when I lay out a workbook I am not trying to go crazy and create the newest coolest workbook I don't care and actually mine are sometimes very very basic is not the word I like to use but

that's what it is um okay so in terms of how I like to lay it out I'm always going to put a donut chart and I'm going to put a timeline chart and I'm going to show this information side by side and I'm going to do that for every single way that I'm going to break out this data and in the end if I have and the reason I do this is because there are you know 29 workbooks alone that we have in our Arsenal and that's just the stuff that is like templated so if I am constantly seeing different visualizations and different ways to look at things my eye isn't going to catch anomalies as

quickly but if I'm used to the way that I'm looking at data I'm going to probably catch things that look a little weird all right so here's an example of laying out the workbook but as one of my customers said to me he's like uh I don't want to see the same data like I don't want to see it as counts in both of them I want to see percentages over here in the donut chart and one amazing thing about workbooks it's not actually amazing but it's just the way that it works is that if you try to just like how you would think to do it go into like chart settings and change it to percentage it

doesn't work it's going to be like 2 908 percent so it it doesn't actually calculate the percentage for you so that is where you guys have to actually create it in the in the query itself so that's what I'm going to show you how to do so how do we convert all of this information to percentages that are actually useful all right this looks way harder than it is it's a lot of words yeah so the the query itself is in the black and blue and red and then I tried to put in the in the green I tried to put in the explanation of each line just to try to make it a little bit more

um consumable so in my in my mind it's a good idea to always start thinking of like what is your base query in your workbook because your workbook most likely is going to have a query that's pretty consistent throughout the workbook and so this is my base query that I built out for when I was building a authentication method analysis workbook because we were trying to figure out who's getting over prompted in their environment and so okay this is my base query that's pretty much going to be consistent through all my visuals let's start at the beginning because it might be helpful all right sign in logs this is going to be sign-in logs like is so much information in

there um and people can say yes I can just look at the raw data I don't know if they can actually look at the raw data and get too much out of it so this is a way to like really parse it out make make something of it and this is just one option so we're going to do an MV expand right away because the a lot of the important data are in these Dynamic columns or they're Dynamic columns that have also been made into Strings which make them even more complicated to pull out so in this instance I really want to pull out information from authentication details because like I told you I'm building this workbook that is for

authentication methods so I need something out of there and if I had access to my portal I could show you what that data looked like just trust me it's in Json okay um and all right so we're going to expand that field out and I'm going to call it parse fields and I'm going to do a parse underscore Json this parse underscore Json can also be parse underscore CSV so depending on you know how the format of that column is you know you have options to parse it out so I'm parsing out the authentication details column and then I'm going to make a new column because I'm going to take that data that I just grabbed and I'm going to create

the authentication method column but I still have to call call back to the parsed information and the way and then I do a parse Fields dot authentication method that means inside the Json format in a key value pair the authentication method was the value no the label and then I'm going to pull out each of those values so that's all that's doing then it tells me it doesn't like that the way that it is and I need to make it into a string as well and so you don't always have to do this step and you may not have to do any of these steps but just depending on the column and the way it's put together in

this instance I did an auth method and I put it back into a string and um I pulled that information to make it a string and I gave it a new label all right then I basically did the same thing for device detail because I wanted to put in you know information about not just authentication method I want to see authentication method by the devices that I have I want to see it by the operating system that it's coming from I want to see it by user by application I want it broken down in every which way and so in this sense I'm like building all of these columns because I'm preparing my base query

so I'm doing the same thing I just explained to you but for different columns and I here I'm all these extend is basically saying give me this new column all right so then we get into the where Clauses like the where statements and this is where I'm going to put in my barriers of the type of data I want and in this instance I'm going to say I don't want to count previously previously satisfied okay so I say where off method does not equal previously satisfied or it doesn't equal blank I want all that information out of my data set and I'm gonna this is going to be consistent throughout my whole entire workbook so it's important

that I know what each step is doing and that when I also like send this data to someone or if I explain this data to someone I let them know the data that I took out and like how I'm calculating it because otherwise they're gonna be they're going to be expecting to see a lot more sign-ins and I just removed every single one that was previously satisfied which is probably 90 of the authentication sign ins oh all right so now we go in and we're doing okay where our username is we take out some users that we don't care about and then we set some filters so everything from here this where authmet in authmed or

asterisk all of these things here these one one two three four five six I can't count but you know five or six in there that all pertains to the parameters that I set at the beginning of the workbook which are filters so when I first started building out these workbooks I had no idea what that code meant or like what it pertained to but it's all relating back to a perimeter so if I set a perimeter um set a filter up in my workbook to say hey I want to be able to filter by this username the only way that the visual has any idea to listen to that filter is by putting it in in your query

all right so that's what all those are and then I did another new column for status because someone decided after I'd kind of built out this query that they also wanted to see failures or successes and so that's why it's at the bottom because I added it later in this instance it doesn't impact my data um in a negative way like but you do sometimes need to be cautious of the order uh in this instant incident it didn't matter but do know that sometimes in kql it does matter of what order you're putting these um lines all right now we have that's the last one okay now at the end we're doing our summary so we're saying

we're going to summarize the count by auth Method all right great we used to have the summary but we still don't have percentage so great that we have this base query now what do we do to get it to be a percentage all right so this is where you're going to create a new variable and it's going to be called Hey base query that sounds easy and we're going to materialize it and it creates basically the materialized function creates a sub query that other queries can reference later so that's what that is all that that that's doing and it's materializing the base query that I just walked you through so this should all look familiar the only thing that's

different here is I took out the summarize because we're not going to summarize it like we did on discount and if you guys want to ask questions like during this if I'm going too fast just you know raise your hand and toasten will catch it because I can go over anything in more detail it's totally your guys's discretion all right so I'm going to close that Loop of materialize and then I'm going to create the total count variable and I have to do a two scalar function here because it's going to return a constant value and this is super important for when I'm calculating percentages because if it's not going to be a constant value there's

going to be slight differences in the number depending on when I ran it so you got to think about that as well all right so then we're saying the total count is that all right great just the base query count that's all it is and then we're we're going to reference base query because remember I told you the reason we're building this up here is because we want to reference it later and so we're going to reference base query and then we're going to do our summarize of the auth methods and we're going to in this instance take it times 100 and divided by the total count by auth Method this is actually going to

aggregate the count and divide by total count that fixed count method and that's actually going to give us correct percentages truth be told there's been times where I haven't had to take it times 100 or maybe had to do slightly different so do look at the numbers see if it actually makes sense um because sometimes it can be a little wonky but normally it's just like basic math take a look does that make sense okay maybe tweak it a little bit but it's overall it's a percentage function all right so that's that tip I have 16 more minutes to tell you more tips or you can ask questions does anybody have a question okay no

okay yes

yes well exactly so if you spend End Mill you know like n dollars a year a lot of millions of dollars you probably get someone like toast in her eye to help you and to do workshops and to sit with your identity and security team and say let me walk you through all of these but as we mentioned before like there's a ton of already these templates that are built that are that we give them and that's what this is for those templates are to say yeah we get this is probably not everybody's um you know Jam to do kql so we're going to do it for you and hopefully they're useful but a lot of times they need to

be customized so getting people to care about kql is kind of like maybe what they used to do about Powershell like yes care about it and now everybody knows it right and we're assuming that maybe eventually well I don't either uh you know but someone on the team I know does and I can always go ask that person so that's what we're getting that's why that's why we're even here to like let you know it's available yeah sure yep

so I can basically do whatever is in the logs and so in this instance there's nothing calling out hey this is a flag or something like that but we have a different data set that's it's all based on risk and risk detections and so in that one yes I could because the data actually in the logs is calling out high risk versus low risk so I could tag those but in this one I'm basically creating alert and the best I can do is create um create the data set that actually could be a scenario that seems risky right or seems like something's not right and then I'm gonna have to investigate it oh but go ahead

right we look at like the collective data and they say yeah this looks suspicious um well this is not a problem yeah

yep every like as long as whatever you learn to do with kql you can it can be as customized as you wish like you can as long as you can pull the data in there it's up to you

you know what that would be what I'm going to ask you at the end is the community contribution tell us what you want to have tell us what you need or you know if you want to take a stab at creating it in a different language and you want us to try to translate it into kql so it's also like we're up for anything so anything that can help analysts because I've learned that in at Sans uh I took a course and I remember I think this is the only thing I actually remembered was that the most important time and the most valuable thing is your analyst time right so yep I know you can Define like high-risk

activity yeah you can Harvest login you can you find those high risks can I define those servers you can you can Define them in the sense that you can set alerts on what you define as high risk but in terms of what's actually labeled in the sign in logs that is determined by Machine learning and the stuff that Microsoft has gathered right from billions and billions of logins like this is what we think unfortunately some you know some people have much you know stricter guidelines and so there our medium risk might be your high risk and that those are the things that I think um we constantly get feedback from our customers about that and we're trying to

also make that something that can be more customizable um but it's right now it's just set by how Microsoft you'd probably need to create an alert and say and and mark it as a high alert Market is high risk yep or technically you wouldn't have to start from scratch you could take a query that's already built in and then just make small changes and and set it as an alert so that's also like you don't you never really like honestly even I don't necessarily start from scratch sometimes I just take a piece that I know I'm going to need and build on it yeah

like threat protection or is it like threat analytics or something is that what you're saying okay I don't know UV I don't know I just I don't know okay yes okay yep I think it's it is it Sentinel or ATP okay yeah to pull Sentinel Sentinel data here or the uba data no I know I'm just trying to think because normally what we would say is like this is the intermediate step before you would get your data in Sentinel so a lot of times we're pulling this data into Sentinel or into a seam solution where you can also do the uba stuff yes yes

yep yes

exactly like we are definitely not trying to take place of a steam solution here it's really just quick analysis because I do feel like there's sometimes separation of Duties right people who completely have are you know trying to do these attack you know like attack threats and Analysis versus who has access to the same solution who so it's a way to kind of do some quick and dirty work in the middle yeah uh okay yes any other questions before question yeah I guess any other workbooks some of the two will be more for like that so it if you as a blue team do not need to automate anything sure yeah but I think what we're trying to

say is that if you are a blue team and you're you are looking for anomalies you're looking for ways to do uh queering lots of log data because do you guys look at log data as blue team yeah so then this is a way to like query that log data and and make sense of it in an easier way and also you don't you wouldn't even have to do this in workbook so there's also something and I don't know what the like I forget always the name of it but it's called custom Explorer where you're bringing in where it's just more of a platform to do huge queries it's not trying to do all the beautiful

visualizations for you which may be more in your in your neighborhood of what you would like to see maybe yeah anybody else okay all right how much time okay eight minutes well I'm gonna keep on going but if you have questions just raise your hand all right so we covered that already all right the other cool thing that I like to point out and that was very tricky for me was making these graphs interactive so I may want to like this looks great and all but I may want to like click into authenticator app and see the data move right that's usually what probably right away someone is going to do is like try to like punch on

the on the you know on the donut chart and see things move and so they expect that so the way that you create these interactive graphs is um for example you just open up your advanced settings in in the workbook and every single visualization that you're creating every single query that you're creating is going to have these options above it and you're going to say when certain items are selected on this graph I want to pull out some parameters that's basically what this is doing and so in this sense I'm going to say I'm going to export parameters and I'm going to call it selected auth method I made that name up I just put it in there that's that's

my name for it however the this default series that's important that's saying that if if it's not going to be um clicked on what do you want me to show and I want you to show all of the data that's basically what this is saying so if I want the pie chart and the or the donut chart and the line graph to be interactive this is what I have to do to the pie chart and then I have to add kql to this interactive um to the to the graph that is going to show the results based on what I selected and what you're going to put what you're going to add the query that

you're going to add is this new mbx band which is is building out it's going to do some parsing for you so I'm doing a parse method I'm parsing the Json and I'm putting it all into a string in one line and this is if you remember is is this uh parameter name I made up so it's basically creating this column for me and then I'm going to call it the parse I'm going to call it selected auth method that column and it's basically going to take this information that I put into the parsed method variable and pull out just the series information the series is the the values and labels that I that I want

and this where selected me uh selected auth method is all it's either going to be that or it's going to be whatever I selected so in the end this code I insert it into my base query that we've all went through and then now when I select on say authenticator app this is going to show up um that's really small yeah then that's going to show up just with the auth method information and just so you can see what actually happened when I entered that kql it it built this column right it built the selected auth method column and added it to the data set and so whenever I select something this gets adjusted in this in

this example I selected the mobile app notification value and so now it it only shows that information and counts it out they're clapping I hope you guys do that for us all right last thing um because I feel like we kind of did questions throughout the thing so I don't huh for questions well I'm just letting you guys oh okay yeah yes sorry oh I think he was just telling me I had five minutes I think yeah yeah yes now let me see oh you guys can see my dashboard what how did it do not do that before oh I could have been doing this the whole time okay anyways all right so I'm going to show you what

I just talked about really quick in these last few minutes what all right so basically I'm in the Azure active directory portal are you familiar with the portal at all the the Azure actor directory portal what whatsoever okay so I am in this monitoring section here I'm scared to click on anything because it might take time to load but let me go oh maybe not this is how the workbooks look like when I'm editing them just FYI um so like I'm editing the query here I can also bring it into log analytics which is better but I'm going to just quick go to the workbooks blade and see if it is friendly with me today

yep yeah oops what no yeah

if it doesn't load up I will share it

all right this is what all these purple ones are what we have created as templates and so I get a lot of um a lot of my kql from ones that are already pre-made and then customize it and these ones that are saying recently modified in terms of my team this is the ones that we've created like I have a b-sides charm one so I've created it and saved it and then it shows up to me because I have access to this specific Azure subscription so not everybody's going in your company is going to have access to this I have to be able to have access to use this Azure subscription is that kind of what you wanted to see

or okay perfect all right are there any other questions like I guess oh this is where I can show you what the data looks like before you parse it out this is really what I wanted to show you so this is how authentication details is all put into one column and it's in Json but it's also then in Brackets which requires an extra step to pull it out and to parse it it is annoying and you know honestly like even when I explain it it sounds silly but that's what it is and I think they must put it into a string to also just save space and time I don't know but anyways there's a lot of information

there that you wouldn't know unless you go and look at it and pull it out so a lot of good stuff are in those Dynamic fields okay like a minute and 50 seconds left so any other questions um I will oh yes

uh awesome Azure data Explorer is the word I was looking for for custom Explorer so thank you because Custer Explorer is like internal Azure data Explorer is what it is um for you guys the other thing which is really cool and I will put this in our resources but if you go to aka.msladen La demos what oh la demo Longo just kidding just want to um you can just play around it's like it's basically a portal and Azure ad space for you just to play around and experiment with it's it's not attached to any subscription or anything it's just a demo environment so you guys can play around um with all of these different types of

logs they'll just be same sample data so yeah now you guys can clap where as I'm just kidding yay we finished with four seconds oh yeah he just remembered I guess we're not done last thing if you guys want and if you can your contribution would like really help to the community it's just like an open source community and it I I know I'm showing you slides in this way but you'll get over it very quickly so um the Azure ID workbooks GitHub is something anybody can join and then you can submit your ideas you can create your own workbook and submit it and it could be even turned into one of the templates you can provide feedback and

like say hey this is wrong or this is way outdated like you guys need to update this or you can also share your tips and tricks like what you just said like I mean you obviously know kql very well so please like tell us all those tips and tricks and then um in this AKA dot msk kql blue team that's going to take you to the repository where we're going to put these slides we're also putting a ton of resources that are really good we're gonna We also recommend a few different um kql tutorials that are done in pluralsight so it'll be a lot of stuff there for you and yeah thank you thank you thank you for coming

to our little session and have a rest of a great rest of your besides foreign [Applause]