[Music]
good
evening you've made it to 7 o'clock on
the first day
impressive my name is Michael kfka we're
going to be talking about Defender
Advanced threat hunting it is a section
inside of the defender portal that is
very powerful and there's some neat
things that you could do with it that
are security related that you might not
think you would need
normally so the agenda is this I'll give
you a brief summary of who I am we're
going to go over what Advanced hunting
and kql are we're going to go through
basic kql structure and setup then we'll
get into
uh Missing software network
connections and the future being
you so who is shecky that would be me
for those of you that don't know I'm a
senior security
engineer I've had 25 over 25 years
experience in the it field including the
last
uh seven eight years working
specifically as security engineer for
different companies
I'm a trainiac what is a trainiac I'm
into model trains and toy trains I've
got almost every single scale of toy
train available out there right now in
my house I think I'm missing two feel
free to come up and ask me about them at
some point in time some of them are over
a hundred years
old I'm a family man I have a wife I
have a son and they come before just
about everything else in this world for
me contact info for
me email
I'm on the Twitter still I'm on the
infos exchange
Mastadon there's my website which I blog
on rather
infrequently and my GitHub which some of
the kql queries that I've written are
posted up there for other people to use
and abuse to their
likings let's get into the meat of it
what is Defender Advanced hunting in kql
we all know about Microsoft Defender for
endpoint we've all heard of the defender
schemes and and all Defender for this
and Defender for that Defender Advanced
hunting is basically a way to go ahead
and um query the database back end of it
all it's gets its Telemetry in there
from all the different Defender opt
options that you have subscribed to so
if you're just Defender for endpoint
you're only going to get the stuff that
Defender for endpoint would would see if
you've got Defender for office you'll
have that data in there also you can
have endpoint in there you can even have
Network and identity information in
there and it's all kept inside of a
beautiful little database that you're
able to
query the amount of data in there Rivals
sisman it is not a replacement for
cismon but it Rivals it because it's
working at that sort of level
um Olaf went hardung I can never
pronounce his last name went ahead and
did an beautiful little article that
compares the two and shows where Sison
is lacking and Defender picks up the
slack and vice
versa and I highly recommend it I will
be making these slides available through
my GitHub page uh probably later this
late next
week everything stored in a back-end
database kql stands for custo query
language it's basically a database query
language like you would have with SQL
Etc where does the name custo come from
is the big question that I have had for
the last few
years custo actually wound up being the
internal database name that Microsoft
uses for all that
data and knowing that it goes okay well
what does kusto
mean
damned if I know I I I searched I
couldn't find a real reason for the name
custo and
anything but
kql and don't get confuse this with
kibana query language which is also
kql cuz we need to go ahead and use
acronyms that are already used by 30
other different
things um that is the query language it
is structur as you will as you will soon
see in such a way that it is
different than some of the others but if
you understand like SQL languages or
Splunk queries
Etc you can go ahead and figure out
where your changes need to be and how to
structure it
properly what you have up here on the
screen
and I don't know if you can see that the
big section there that's your actual kql
new qu completely
blank as you see it inside of the
defender
portal this goes ahead and allows you
different things on the um as you're
looking at it well I'm looking at it on
the left hand side you'll see a section
and we'll blow that up for you in a bit
but that's a schema section that'll tell
you what the top end table is for each
individual item that you can do and
you'll be able to expand those out on
the bottom part of the screen is a very
basic kql query that I wrote to detect
who am I and we'll go over that
shortly here you could see the advanced
hunting I expanded out and it might be T
because of the size it might be tough to
see but I expanded out um was it device
file events which includes Tim stamp
device idea all the different pieces of
data when creating a kql query
the first thing that I usually do is
I'll take the top table and I'll just
run it for a 24-hour period or a very
short period just to see how it lays out
what it returns and the reasoning behind
that is you can go ahead and find out
what you need to go ahead and focus on
and what sort of data comes back and
what sort of data comes back in a number
of cases is true
false or you might be getting an actual
Json back is an additional Fields so in
in yellow here we've got the basic
query first thing that you see is you
see your table name device process
events in this case there's also things
like device events identity uh just
about everything has a table name in it
then you get into your qualifiers which
are preceded by the
pipes where is the most common thing
that you will use throughout a query
especially a basic
query the wear statement is followed by
something by the data name and then
equals contains or you could put an
exclamation mark in front of it and go
ahead and say does not contain which
comes in very handy the contains I find
to be one of the most useful systems
ever because you don't want to type
everything out or you're searching for
something specifically that you know
that these three unique letters that
aren't going to show up in just about
anything else are going to be in there
and whatever is in front and back such
as computer names depending upon your
computer naming
scheme you have your Andor so I could go
ahead with the where file name contains
who am I I could amend that with and
file name contains or or file name does
not contain and string them together to
make things tightened up a bit
more there's multiple Wares to go ahead
and tighten things up you'll see I've
got a wear file name and then I've got a
wear initiating process file
name Skype uses who am I to go ahead and
decide if it can upgrade
itself there are a lot of programs that
do scary stuff like
that your output most of the time I just
use a straight
project you can go ahead and select
whatever pieces of data that you want
once you know what you're looking for so
if I don't want to have this table or
this because that's not relevant to what
I'm searching for I can go ahead and cut
it out completely and just put down
device name and file name and
username and just have that come out and
a table for
me and you don't see it here but there
is a way to make comments uh they could
go just about anywhere usually you will
see them either in their own line or
right after a wear statement to go ahead
and explain everything out and it's done
with the double uh is that the forward
slash I always get forward and back
slash mixed
up Advanced items inside of it you can
use variables and we'll see an example
of that coming up
you can use if
statements you can go ahead and create a
distinct output based on the piece of
data that you're looking for I tend to
do it quite often with device name so
that way I'm looking at individual
computers that it's very distinct data
on there so that way I'm not getting
10,000 rows and 10,000 rows is the
ultimate output that you can get it will
not give you more than 10,000 rows of
output you can go ahead and create a su
suiz
set and we'll see what that is I'll
explain it more once we get to that and
you can go ahead and put output project
output based on whether something is or
is not inside of that summarized set and
we're going to see that very shortly
here so there's a lot of power behind it
and a lot of customization that you can
do with a kql
query finally you can join multiple
tables so if you've got device process
events device Network events and
identity all together you can go ahead
and start off with Device process events
is this and device events is this and
joined this and we'll get into the joint
but you can do that multiple ways so
that way you could really narrow your
focus down and knowing tactics
techniques and
procedures if you know those you could
really start getting into some uh Kathy
who's sitting up here in front was
talking about this during her talk you
can really get into some real cool stuff
that'll frustrate red teams when they're
working
in so software queries why would I want
a query for a piece of
software true story in the last year I
went ahead and was asked because our
infra team could not figure it out what
machines were missing our qualis
agent that's rather important to keep
yourself going with your vulnerability
system if your vulnerability agent isn't
on there well sure it's going to go
ahead and scan anyways but you're not
going to get the extreme details so I
sat there and I said let's see what I
can do and the threat the TVM table
which is their threatened vulnerability
management table allows me to look at
software so what we've got here is the
first line you've got device TVM
software inventory so I'm taking a look
at the inventory of the software on all
the
machines then I'm going ahead and I'm
looking where the device name contains
in this case we have some vdis out there
and we don't want to include the vdis
because that's a whole separate thing
that's all brought off of Master
images I'm not looking for anything that
has a server operating system so I've
got the OS platform line there not
containing server inside of it that way
I'm not having to type out server 2010
to server 2019 server 2022 server bite
my butt
I'm not looking for Linux we're not
putting the agent on Linux machines and
we don't have a lot of Linux machines
anyways so I backed that out that way I
started getting more and more down to
the desktop laptop
level then I went ahead and said
summarize which summarizes summarize if
you don't know what that word is well
there's a thing called Google we can
look that up all software installed is a
variable
and it equals making a set of the
software names that are inside of the
TVM software
inventory and it's being done per device
name so it'll give me the device name
and a in a grouping of all the software
that's on that
machine the final line is where the set
has the element all software
installed qualis Cloud security AG agent
equals equals z now this is remember
when I said you go ahead and you do your
initial table and just do a quick output
on it this will show you in a case like
this
qualis it would give you a zero or a
one depending or nothing depending upon
whether it was true or false that it had
it in this case I'm looking for
everything that has a hard zero on it
which means that it does not have this
piece of software that's inside of this
set of software that's on this machine
and then it's going to Output all the
machine
names that are missing this piece of
software into a nice little table when I
ran this I came up with actual six
machines inside of my network that
didn't have this software and mind you
we've got things like um manage engine
software and stuff like that that's
supposed to do all this inventory and
they should be able to get this stuff
out this query took me about an hour and
a half to write
not a lot of time if you really think
about it now going forward I can change
out that quala security agent over
to fortnet VPN Cisco
VPN whatever piece of software ask
toolbar there's one you haven't heard
for a while do I want to see if anybody
has ask toolbar installed onto it change
it over to ask toolbar put it equals
equals one and I'd get a listing of
machines that have that old ask toolbar
sitting on it
so for security that is rather important
knowing what is and what is not on a
machine because that gives you an a
better idea of what your threat area is
what your threat surface is and if all
the proper security software is in
place now Just for kicks just so you
understand kql is not limited to
Defender it is used in graph graph
Microsoft's graph
it is used in log analytics inside of
azure it is used inside a
sentinel tables are
different for instance an
Azure that's the exact same query minus
some of the uh tightening on it because
all that it was looking at was Azure
joined laptops so I wouldn't see any on
Prem but that's the exact same query
you'll notice that the initial table is
app inventory uncore
CL you'll notice that the um Mak set is
app name undor s so you would think that
Microsoft would have a way for you to
know what is
equivalent no you get to figure it out
yourself just to make things more
interesting so here we have a simple
query very possibly one of the neatest
ones I've got outside of that who am I
query I've caught almost every single
red team that's gone against us on that
who am I
query the next one I want to go over
with you as we're winding down here is
the remote desktop connection query Port
3389 who here loves Port
3389 you know it's has to be open for
some for some odd reason because of
active directory
right what we don't want want to see is
people going ahead and remoting into
machines we don't want to see that RDP
itself going you know you can figure
that out through advanced Defender
Advanced
query and there's the actual query and
this I do believe I have this one up in
my uh git GitHub right now this one has
a joint in it it is a much
more advanced type of query so what I
started off doing was I only wanted to
look at the past week so the the first
line is timestamp ago 7
days then I went ahead and I did for the
network events where the connection is a
success so it actually
connects make sure that it's connecting
only on Port
3389 make sure that the process is
mstsc.exe anybody know what that
is remote desktop
connection you'll see a commented
outline where local admin not equals
one that is a line there for me to
remember in case I want to look for
local admins only or what have
not yes local admins no local admins in
this case I didn't care if it was a
local admin or not I wanted it but I put
that in there for future
consideration then you'll see also join
device login events on device name so
what that's doing is that's saying okay
we've got the these device Network
events now we're going to take these
login events and look at device name as
the same thing on both of them and we
can go forward from there so now I'm
locking in the network events device the
devices in the network events table over
to the login events
table then there's a couple of contains
or not contains I killed off our service
account names because they're going to
do their own thing and they're not going
to be an Interactive connection properly
I took off our secm server because that
one will go ahead and hit 3389 and get
shown up and everything I got rid of
computer account
names because you're not going to be
look you're not looking for people that
have you're not looking for a computer
you're looking for an actual individual
and computer account names still
actually use
3389 then I said where it's not equal to
system
active
directory finally I said where it
contains an interactive login thanks to
active directory for not doing an
interactive
login so that way I could go ahead and
say yes this is a person that's actually
connecting and logging in and physically
doing something onto that
machine then you'll see two separate
things down here at the bottom there's a
summarize by device name and I picked
out what table what data points I wanted
out there including the account name
that's doing
it what remote Port they're actually
hitting so that way I could show that
they're hitting
3389 what the initiating process file
name is are they doing it from command
line are they using are they just using
the graphic
interface devs use the command
line below that you'll see just a
straight
project that gave me like four times as
much data
but there is instances where you might
want to do that I did the S I have the
summary there as my main but when I'm
trying to go through and make sure that
I'm not missing anything I'll uncomment
out the project and I'll comment out the
summarize so that way I can just flip
between them and now I don't have to
have two queries sing there I've got one
query where I just make a slight
adjustment to the query itself for my
own sanity sake because I'm
old
potential troubles that you can run into
with this now I'd mentioned about
Sentinel and stuff like that you go well
why' hint you just dump all this stuff
into your sim
well pulling data from Defender into a
Sim is
hard most of the apis don't give you the
full amount of data that you're going to
get when you actually punch in a kql
query into Defender kql SE section
itself even well you go well what about
Sentinel I go injesting the data that
all that extra data into Sentinel costs
money you get free ingestion of the
alerts but you want the actual backend
data to look to be able to query on nope
sorry you got to pay for
that queries in Defender itself only go
back 30 days worth of
data that's the biggest catch there are
a lot of people people out there that's
why you can't use it as a true Sim
because you a lot of places you need to
have that data going back 120 days or
further in a lot of instances some cases
seven years even depending upon your
industry and regulation
requirements but it does work in as far
as a quick thing goes and then you can
go ahead and prove that yeah we need to
get this data into our Sim or into
Sentinel where we can have it for a year
two years to go back through
data from the endpoint can take 20
minutes to see in Defender this is by
Design Microsoft went ahead and decided
I was talking with an engineer last year
they made a decision that they didn't
want to slow down machines so for
performance issues it only makes a call
back once every 20 minutes and I've yet
to figure out how to tell when that 20
minute time period is up so when I've
run it I've run it 1 minute and then 2
minutes later all of a sudden boom I've
hit that 20 minute time period which I
didn't even
realize so it's not instantaneous it's
not live data it's literally it can be
up to 20 minutes
late for real Advanced
items you'd have to go through
Json there's an advanced field most of
that winds up looking like a Json table
parsing through that can get very
complex not everything that Defender
agencies is is available I had had a
great idea of going ahead and being able
to search for specific things um that
would be uh specific API calls Windows
API calls that are used to go ahead and
unhook Defender itself Etc and when I
was talking with Microsoft they were
like yeah most people don't want that
data and we need to keep our our stuff
compact so not everything that is that
the agent sees is actually transmitted
on up
API usage can get goofy you're dealing
with Json you have to work into specific
tables you have to know exactly how your
query is called inside of everything
like that luckily they've got an API
Explorer which should allow you to
figure that stuff
out finally many new features for
Defender that they are coming out with
are now
alart E5 is
everything no
more
most of the newest stuff they're doing
even if you've got E5 you have to pay
alloc cart prices on on a bunch of it
which means of course that you're going
to have some blind
spots I've included for those of you
that want to go ahead and download a
couple of links one of them is a thing
called must learn kql it's been put out
by uh Rod Trent fantastic Microsoft guy
it is a fantastic course on how to use
kql
properly uh Defender ninja training will
go into that and all the Deep dive of
what you can do with Defender again
that's put out by Microsoft and
Microsoft actually has a GitHub of a kql
repository uh some of the stuff there is
old some of it you're going to have to
figure out how to recreate a little bit
and what's not working what it's changed
to but it is a very solid uh
repository finally to recap Advanced
hunting query for kql can be very
powerful the data inside of Defender is
very fruitful there's a lot more in
there than what you would see out of the
box looking for things like RDP and
software can enhance your security and
finally you can create custom detections
from the queries
provided um and by that you have to
include three tables when you project
three items a device ID a report ID and
a Tim stamp without those three it will
say sorry you're missing one of these
three we will not create this detection
for you to alert on but if you put those
into the project output you can go ahead
and create any sort of alert that you
want talk about making hell for the red
team thank you very much for coming I
hope that you learned something and
we'll see you around feel free to come
up to me during the course of the
conference h