top of page
  • Writer's pictureAniket RT

KQL to find Geo Details and Threat Score

Hi All, continuing on my previous post it is very important to enrich IP Address entities with Threat Intelligence data.


What exactly is Threat Intelligence and how can I enable it?


Threat Intel helps with additional insights, context, and additional strategies about threat actors and adversary threat infrastructure. One such solution is Microsoft Defender Threat Intelligence or MDTI in short.


It is very easy to ingest the TI indicators. just navigated to Microsoft Sentinel -> Content Hub and search for threat intelligence.


Once installed, navigate to Data Connectors blade and look for Microsoft Defender Threat Intelligence (Preview).


Click on Open Connector Page and configure for how much time would you like to get indicators for. Ideally, you want all data.


Once you hit the Connect button, Microsoft Sentinel will now start ingesting all IoC's (Indicators of Compromise) from various sources.


Note this will attract ingestion charges however I noticed that it seems to be very minimal.


You can use the following query to check for the same.


Usage

| where TimeGenerated >= ago(30d)

| summarize GB=sum(_BilledSize)/1000/1000/1000 by DataType

| where DataType == 'ThreatIntelligenceIndicator'


Once Threat Intel data starts coming in, let us now navigate to Analytic Rules blade, Rule Templates and you can add a filter here for Data Source as shown below.



Now you can see a big list of "TI map ..." rules. Now, you might be wondering do i really need to enable all these rules ?". Well the answer really depends on your environment, I suggest clicking on each rule and seeing if all/most of the data sources are available or not.


For example, I am not ingesting any CEF logs from Palo Alto devices so the following rule might not help me out so much. Pay attention to the grayed out legend which shows that the particular data source is not connected.


I would suggest enabling SecurityEvents and Syslog analytic rules for now. Once you start ingesting more data, you can enable the rest.


Now, let's see how we can leverage Threat Intelligence.


Lets supposed there was someone attempting a brute force attack on one of our servers. We had an analytic rule in place to detect the attack and this has also created an incident. That's great but we need some information on where this attack originated from and what is the threat score for this particular indicator of compromise. This will aid the SOC Team to carry out their investigation and find out if this is a false positive or an actual threat.

If identified as an actual threat, the SOC Team would be able to take precautionary measures to avoid an attack from the malicious actor in the future.


Following is a sample KQL Query can be used to find out geo details and threat score of an IP address.


ThreatIntelligenceIndicator

| where NetworkSourceIP == '190.85.15.251' and TimeGenerated >= ago(30d)

| extend ip_country = geo_info_from_ip_address(NetworkSourceIP)

| extend json_object = todynamic(ip_country)

| extend country = tostring(json_object.country),state = tostring(json_object.state),city = tostring(json_object.city),latitude = tostring(json_object.latitude),longitude = tostring(json_object.longitude)

| extend action = iif(ConfidenceScore >=60,'Block','Investigate')

| project LastSeen = TimeGenerated, ConfidenceScore, action, ThreatType, Description, IndicatorId, ExternalIndicatorId, country, city, latitude, longitude

| summarize arg_max(LastSeen,*)


I am using the IP 190.85.15.251 from my previous blog post.


I will explain the query once and then we can see the output of this query.


  1. ThreatIntelligienceIndicator is the table name (Quite Obvious :))

  2. We now query against the attacker ip address and I am considering looking up data for the past 30 days.

  3. Next, we use the function geo_info_from_ip_address to get geographic details in json format.

  4. Next line sees us storing the values of country, city, state, latitude and longitude into meaningful variables.

  5. This line is totally optional. I just added an if statement to suggest the SOC Team on what action to be taken.

  6. Next, we just project/display the columns that we need like Threat Score, Type, Geo Details and so on.

  7. Last line makes sure to return only the most recent/latest record.


Following is an output of the query.


Pretty neat and useful, don't you think?!


Now, you must ne wondering "Why do I need to run another query just to get these details, why can't I use this directly in my failed login analytic rule?"


Well, you can! Here's the query -


Syslog

| where Facility == 'auth' and SyslogMessage has 'failed password'

| extend Account = tostring(extract("Failed password for (.*) from", 1, SyslogMessage))

| extend PortNumber = tostring(extract("port (\\d+)", 1, SyslogMessage))

| extend IpAddress = tostring(extract("from (\\d+\\.\\d+\\.\\d+\\.\\d+)", 1, SyslogMessage))

| extend Protocol = tostring(extract("port \\d+ (.*)$", 1, SyslogMessage))

| extend Description = iff(Account has "invalid","Failed Login from Invalid Account [Indicates brute force]", " Failed Login from Valid Account [Consider Password Change] [Could be legitimate attempt]")

| extend ip_country = geo_info_from_ip_address(IpAddress)

| extend json_object = todynamic(ip_country)

| extend country = tostring(json_object.country),state = tostring(json_object.state),city = tostring(json_object.city),latitude = tostring(json_object.latitude),longitude = tostring(json_object.longitude)

| join kind=innerunique ThreatIntelligenceIndicator on $left.IpAddress == $right.NetworkSourceIP

| project

IpAddress,

HostIP,

Account,

PortNumber,

Protocol,

HostName,

Computer,

ProcessID,

ProcessName,

Description,

ConfidenceScore, Action, ThreatType,

country, city, latitude, longitude



As you can tell, I am using a join here to co-relate both the tables on a common set of data which in this case is the Attacker IP Address and now, we are able to get both the threat intel details as well as geographic details. Feel free to save it as an analytic rule!


Following is an output of the query-


Next, we will look at automating this process using a logic app. We can leverage third party security vendors for this task as well.


Stay Tuned! Cheers and thanks for reading.



392 views0 comments
bottom of page