Notebook Version: 1.0
Python Version: Python 3.6 (including Python 3.6 - AzureML)
Required Packages: kqlmagic, msticpy, pandas, numpy, matplotlib, networkx, ipywidgets, ipython, dnspython, ipwhois, folium, maxminddb_geolite2
Platforms Supported:
Data Sources Required:
Brings together a series of queries and visualizations to help you determine the security state of an Account. The account can be a Windows or Linux account or an Azure Active Directory/Office 365 account.
Our broad initial hunting hypothesis is that a we have received account name entity which is suspected to be compromised and is being used malicious manner in internal networks, we will need to hunt from a range of different positions to validate or disprove this hypothesis.
Before you start hunting please run the cells in Setup at the bottom of this Notebook.
# Imports
import sys
import warnings
from msticpy.nbtools.utility import check_py_version
MIN_REQ_PYTHON = (3, 6)
check_py_version(MIN_REQ_PYTHON)
from IPython import get_ipython
from IPython.display import display, HTML, Markdown
import ipywidgets as widgets
import matplotlib.pyplot as plt
import seaborn as sns
sns.set()
import pandas as pd
pd.set_option("display.max_rows", 100)
pd.set_option("display.max_columns", 50)
pd.set_option("display.max_colwidth", 100)
from msticpy.data import QueryProvider
from msticpy.nbtools import *
from msticpy.sectools import *
from msticpy.nbtools.utility import md, md_warn
from msticpy.nbtools.wsconfig import WorkspaceConfig
WIDGET_DEFAULTS = {
"layout": widgets.Layout(width="95%"),
"style": {"description_width": "initial"},
}
# Some of our dependencies (networkx) still use deprecated Matplotlib
# APIs - we can't do anything about it so suppress them from view
from matplotlib import MatplotlibDeprecationWarning
warnings.simplefilter("ignore", category=MatplotlibDeprecationWarning)
ws_config = WorkspaceConfig()
Using Open PageRank. See https://www.domcop.com/openpagerank/what-is-openpagerank
Use the following syntax if you are authenticating using an Azure Active Directory AppId and Secret:
%kql loganalytics://tenant(aad_tenant).workspace(WORKSPACE_ID).clientid(client_id).clientsecret(client_secret)
instead of
%kql loganalytics://code().workspace(WORKSPACE_ID)
Note: you may occasionally see a JavaScript warning displayed at the end of the authentication - you can safely ignore this.
On successful authentication you should see a popup schema
button.
To find your Workspace Id go to Log Analytics. Look at the workspace properties to find the ID.
# Authentication
qry_prov = QueryProvider(data_environment="LogAnalytics")
qry_prov.connect(connection_str=ws_config.code_connect_str)
table_index = qry_prov.schema_tables
The notebook is expecting your Azure Sentinel Tenant ID and Workspace ID to be configured in one of the following places:
config.json
in the current foldermsticpyconfig.yaml
in the current folder or location specified by MSTICPYCONFIG
environment variable.For help with setting up your config.json
file (if this hasn't been done automatically) see the ConfiguringNotebookEnvironment
notebook in the root folder of your Azure-Sentinel-Notebooks project. This shows you how to obtain your Workspace and Subscription IDs from the Azure Sentinel Portal. You can use the SubscriptionID to find your Tenant ID). To view the current config.json
run the following in a code cell.
%pfile config.json
For help with setting up your msticpyconfig.yaml
see the Setup section at the end of this notebook and the ConfigureNotebookEnvironment notebook
Type the account name that you want to search for and the time bounds over which you want to search.
You can specify the account as:
alice
)alice@contoso.com
)mydomain\alice
In the second two cases the domain qualifier will be stripped off before the search. The search is not case sensitive and will match full substrings. E.g. bob
will match domain\bob
and bob@contoso.com
but not bobg
or bo
.
accountname_text = widgets.Text(description='Enter the Account name to search for:', **WIDGET_DEFAULTS)
display(accountname_text)
Text(value='', description='Enter the Account name to search for:', layout=Layout(width='95%'), style=Descript…
query_times = nbwidgets.QueryTime(units='day', max_before=200, before=5, max_after=7)
query_times.display()
HTML(value='<h4>Set query time boundaries</h4>')
HBox(children=(DatePicker(value=datetime.date(2019, 10, 31), description='Origin Date'), Text(value='01:18:51.…
VBox(children=(IntRangeSlider(value=(-5, 7), description='Time Range (day):', layout=Layout(width='80%'), max=…
# Set up function to allow easy reference to common parameters for queries
def acct_query_params():
return {
"start": query_times.start,
"end": query_times.end,
"account_name": accountname_text.value,
}
This shows all of the tables in the workspace with a string matching the account name entered.
# KQL query for full text search of IP address and display all datatypes
datasource_status = '''
search \'{account_name}\'
| where TimeGenerated >= datetime({start}) and TimeGenerated <= datetime({end})
| summarize RowCount=count() by Table=$table
'''.format(**acct_query_params())
%kql -query datasource_status
datasource_status_df = _kql_raw_result_.to_dataframe()
#Display result as transposed matrix of datatypes availabel to query for the query period
if len(datasource_status_df) > 0:
display(Markdown("### <span style='color:blue'> "
+ "Datasources available to query for Account "
+ f"*{acct_query_params()['account_name']}* </span>"))
display(datasource_status_df)
else:
display(Markdown(f'### <span style="color:orange"> No datasources available to query for the query period </span>'))
# AAD
md("Searching for AAD activity...")
summarize_clause = """
| summarize arg_max(TimeGenerated, *) by UserPrincipalName, OperationName,
Identity, IPAddress, tostring(LocationDetails)
| project TimeGenerated, UserPrincipalName, Identity, IPAddress, LocationDetails"""
aad_signin_df = (qry_prov.Azure
.list_aad_signins_for_account(**acct_query_params(),
add_query_items=summarize_clause)
)
md("Searching for Azure activity...")
# Azure Activity
summarize_clause = """
| summarize arg_max(TimeGenerated, *) by Caller, OperationName,
CallerIpAddress, ResourceId
| project TimeGenerated, UserPrincipalName=Caller, IPAddress=CallerIpAddress"""
azure_activity_df = (qry_prov.Azure
.list_azure_activity_for_account(**acct_query_params(),
add_query_items=summarize_clause)
)
md("Searching for Office365 activity...")
# Office Activity
summarize_clause = """
| project TimeGenerated, UserId = tolower(UserId), OfficeWorkload, Operation, ClientIP, UserType
| summarize arg_max(TimeGenerated, *) by UserId, OfficeWorkload, ClientIP
| order by TimeGenerated desc"""
o365_activity_df = (qry_prov.Office365
.list_activity_for_account(**acct_query_params(),
add_query_items=summarize_clause)
)
md("Searching for Windows logon activity...")
# Windows Host
summarize_clause = """
| extend LogonStatus = iff(EventID == 4624, "success", "failed")
| project TimeGenerated, TargetUserName, TargetDomainName, Computer, LogonType, SubjectUserName,
SubjectDomainName, TargetUserSid, EventID, IpAddress, LogonStatus
| summarize arg_max(TimeGenerated, *) by TargetUserName, TargetDomainName, LogonType, Computer, LogonStatus"""
win_logon_df = (qry_prov.WindowsSecurity
.list_logon_attempts_by_account(**acct_query_params(),
add_query_items=summarize_clause)
)
md("Searching for Linux logon activity...")
# Linux host
summarize_clause = """
| summarize arg_max(TimeGenerated, *) by LogonType, SourceIP, Computer, LogonResult"""
linux_logon_df = (qry_prov.LinuxSyslog
.list_logons_for_account(**acct_query_params(),
add_query_items=summarize_clause)
)
rec_count = (
len(aad_signin_df) + len(azure_activity_df)
+ len(o365_activity_df) + len(win_logon_df)
+ len(linux_logon_df)
)
md(f"Found {rec_count} records...")
Searching for AAD activity...
Searching for Azure activity...
Searching for Office365 activity...
Searching for Windows logon activity...
Searching for Linux logon activity...
Found 76 records...
Choose Account to Explore
from collections import namedtuple
AccountDFs = namedtuple("AccountDFs", ["linux", "windows", "aad", "azure", "o365"])
account_dfs = AccountDFs(
linux=linux_logon_df,
windows=win_logon_df,
aad=aad_signin_df,
azure=azure_activity_df,
o365=o365_activity_df,
)
# Combine into single data frame
lx_df = (linux_logon_df[["AccountName", "TimeGenerated"]]
.groupby("AccountName")
.max()
.reset_index()
.assign(Source="LinuxHostLogon"))
win_df = (win_logon_df[["TargetUserName", "TimeGenerated"]]
.groupby("TargetUserName")
.max()
.reset_index()
.rename(columns={"TargetUserName": "AccountName"})
.assign(Source="WindowsHostLogon"))
o365_df = (o365_activity_df[["UserId", "TimeGenerated"]]
.groupby("UserId")
.max()
.reset_index()
.rename(columns={"UserId": "AccountName"})
.assign(Source="O365Activity"))
aad_df = (aad_signin_df[["UserPrincipalName", "TimeGenerated"]]
.groupby("UserPrincipalName")
.max()
.reset_index()
.rename(columns={"UserPrincipalName": "AccountName"})
.assign(Source="AADLogon"))
azure_df = (azure_activity_df[["UserPrincipalName", "TimeGenerated"]]
.groupby("UserPrincipalName")
.max()
.reset_index()
.rename(columns={"UserPrincipalName": "AccountName"})
.assign(Source="AzureActivity"))
all_sources_df = pd.concat([lx_df, win_df, o365_df, aad_df, azure_df])
# Display the results that we've found
format_tuple = (lambda x:
(x.AccountName + " " + x.Source
+ " (Last activity: " + str(x.TimeGenerated) + ")",
x.AccountName + " " + x.Source))
accts_dict = {item[0]: item[1] for item in all_sources_df.apply(format_tuple, axis=1)}
def display_activity(selected_item):
acct, source = selected_account(selected_item)
utils.md(f"{acct} (source: {source})", "bold")
if source == "LinuxHostLogon":
display(linux_logon_df[linux_logon_df["AccountName"] == acct]
.sort_values("TimeGenerated", ascending=True))
if source == "WindowsHostLogon":
display(win_logon_df[win_logon_df["TargetUserName"] == acct]
.sort_values("TimeGenerated", ascending=True))
if source == "AADLogon":
display(aad_signin_df[aad_signin_df["UserPrincipalName"] == acct]
.sort_values("TimeGenerated", ascending=True))
if source == "AzureActivity":
display(azure_activity_df[azure_activity_df["UserPrincipalName"] == acct]
.sort_values("TimeGenerated", ascending=True))
if source == "O365Activity":
display(o365_activity_df[o365_activity_df["UserId"] == acct]
.sort_values("TimeGenerated", ascending=True))
def selected_account(selected_acct):
if not selected_acct:
return "", ""
acct, source = selected_acct.split(" ")
return acct, source
select_acct = nbwidgets.SelectString(
item_dict=accts_dict,
auto_display=True,
description="Select an account to explore",
action=display_activity,
height="200px",
width="100%")
VBox(children=(Text(value='', description='Filter:', style=DescriptionStyle(description_width='initial')), Sel…
account_name, account_source = selected_account(select_acct.value)
related_alerts = qry_prov.SecurityAlert.list_related_alerts(
**acct_query_params()
)
def print_related_alerts(alertDict, entityType, entityName):
if len(alertDict) > 0:
md(f"Found {len(alertDict)} different alert types related to this {entityType} (`{entityName}`)",
"large, bold"
)
for (k, v) in alertDict.items():
print(f"- {k}, # Alerts: {v}")
else:
md(f"No alerts for {entityType} entity `{entityName}`")
if isinstance(related_alerts, pd.DataFrame) and not related_alerts.empty:
alert_items = (
related_alerts[["AlertName", "TimeGenerated"]]
.groupby("AlertName")
.TimeGenerated.agg("count")
.to_dict()
)
print_related_alerts(alert_items, "account", account_name)
nbdisplay.display_timeline(
data=related_alerts, title="Alerts", source_columns=["AlertName"], height=200
)
else:
display(Markdown("No related alerts found."))
def disp_full_alert(alert):
global related_alert
related_alert = SecurityAlert(alert)
nbdisplay.display_alert(related_alert, show_entities=True)
if related_alerts is not None and not related_alerts.empty:
related_alerts["CompromisedEntity"] = related_alerts["src_accountname"]
display(Markdown("### Click on alert to view details."))
rel_alert_select = nbwidgets.AlertSelector(
alerts=related_alerts,
action=disp_full_alert,
)
rel_alert_select.display()
Found 33 different alert types related to this account (`alexw@m365x648731.onmicrosoft.com`)
- A malicious PowerShell Cmdlet was invoked on the machine, # Alerts: 3 - A script with suspicious content was observed, # Alerts: 1 - A user was added to an administrative group, # Alerts: 2 - Activity from a Tor IP address, # Alerts: 29 - Activity from infrequent country, # Alerts: 2 - An active 'Mikatz' high-severity malware was detected, # Alerts: 4 - Anonymous IP address, # Alerts: 20 - Encoded Powershell Run - Custom Alert, # Alerts: 11 - MDATP Detections, # Alerts: 3 - MDATP Suspicious Powershell Command Line, # Alerts: 2 - Malicious credential theft tool execution detected, # Alerts: 1 - Masquerading as System Files (custom), # Alerts: 8 - Mass delete, # Alerts: 1 - Mass download, # Alerts: 2 - Mass download by a single user, # Alerts: 1 - Network connection to a risky host, # Alerts: 4 - New group added suspiciously, # Alerts: 2 - PowerShell Downloads, # Alerts: 12 - PowerShell downloads - From Hunting Queries, # Alerts: 12 - Powershell Empire cmdlets seen in command line, # Alerts: 5 - Powershell Empire cmdlets seen in command line data, # Alerts: 3 - Publicly shared confidential files, # Alerts: 4 - Sensitive credential memory read, # Alerts: 6 - Sticky Keys binary hijack detected, # Alerts: 2 - Suspicious Powershell Command Line in MDATP, # Alerts: 59 - Suspicious Powershell commandline, # Alerts: 24 - Suspicious access to LSASS service, # Alerts: 18 - Suspicious behavior by a svchost.exe was observed, # Alerts: 15 - Suspicious behavior by cmd.exe was observed, # Alerts: 15 - Suspicious inbox forwarding, # Alerts: 1 - Suspicious registration of an accessibility debugger under the IFEO registry key, # Alerts: 3 - Suspicious sequence of exploration activities, # Alerts: 10 - UnfamiliarLocation, # Alerts: 2
VBox(children=(Text(value='', description='Filter alerts by title:', style=DescriptionStyle(description_width=…
Any alerts with a matching account name are shown here. Select a bookmark to view the contents.
acct_name = acct_query_params()["account_name"]
related_bkmark_df = qry_prov.AzureSentinel.list_bookmarks_for_entity(
**acct_query_params(), entity_id=acct_name
)
def print_related_bkmk(bookmarks, entityType, entityName):
if len(bookmarks) > 0:
md(f"Found {len(bookmarks)} different bookmarks related to this {entityType} (`{entityName}`)",
"large, bold"
)
else:
md(f"No alerts for {entityType} entity `{entityName}`")
if isinstance(related_bkmark_df, pd.DataFrame) and not related_bkmark_df.empty:
bookmarks = (related_bkmark_df
.apply(lambda x: (f"{x.BookmarkName} {x.Tags} {x.TimeGenerated}", x.BookmarkId),
axis=1)
.tolist())
print_related_bkmk(bookmarks, "account", account_name)
nbdisplay.display_timeline(
data=related_bkmark_df,
title="Bookmarks",
source_columns=["BookmarkName", "Tags"], height=200
)
else:
display(Markdown("No related bookmarks found."))
def disp_bookmark(bookmark_id):
display(related_bkmark_df[related_bkmark_df["BookmarkId"] == bookmark_id].T)
if related_bkmark_df is not None and not related_bkmark_df.empty:
display(Markdown("### Click on bookmark to view details."))
rel_bkmk_select = nbwidgets.SelectString(
item_list=bookmarks,
action=disp_bookmark,
auto_display=True
)
Found 31 different bookmarks related to this account (`alexw@m365x648731.onmicrosoft.com`)
VBox(children=(Text(value='', description='Filter:', style=DescriptionStyle(description_width='initial')), Sel…
Depending on the type of account (AAD or Host/Endpoint account) we can drill deeper to look at data specific to that account type.
# Function definitions used below
# This cell should be executed before continuing further.
# WHOIS lookup function
from functools import lru_cache
from ipwhois import IPWhois
from ipaddress import ip_address
@lru_cache(maxsize=1024)
def get_whois_info(ip_lookup, show_progress=False):
try:
ip = ip_address(ip_lookup)
except ValueError:
return "Not an IP Address", {}
if ip.is_private:
return "private address", {}
if not ip.is_global:
return "other address", {}
whois = IPWhois(ip)
whois_result = whois.lookup_whois()
if show_progress:
print(".", end="")
return whois_result["asn_description"], whois_result
ti_lookup = TILookup()
def check_ip_ti(df, ip_col):
ip4_rgx = r"((?:[0-9]{1,3}\.){3}[0-9]{1,3})"
df = (df
.assign(IP_ext=lambda x: x[ip_col].str.extract(ip4_rgx, expand=False))
.rename(columns={ip_col: ip_col + "_orig"})
.rename(columns={"IP_ext": ip_col})
)
src_ip_addrs = (df[[ip_col]]
.dropna()
.drop_duplicates()
)
md(f"Querying TI for {len(src_ip_addrs)} indicators...")
ti_results = ti_lookup.lookup_iocs(data=src_ip_addrs, obs_col=ip_col)
ti_results = ti_results[ti_results["Severity"] > 0]
ti_merged_df = df.merge(ti_results, how="left", left_on=ip_col, right_on="Ioc")
return ti_results, ti_merged_df, src_ip_addrs
geo_lookup = GeoLiteLookup()
def check_geo_whois(ip_df, df, ip_col):
ip4_rgx = r"((?:[0-9]{1,3}\.){3}[0-9]{1,3})"
df = (df
.assign(IP_ext=lambda x: x[ip_col].str.extract(ip4_rgx, expand=False))
.rename(columns={ip_col: ip_col + "_orig"})
.rename(columns={"IP_ext": ip_col})
)
md(f"Querying geolocation for {len(ip_df)} ip addresses...")
geo_ips = geo_lookup.lookup_ip(ip_addr_list=list(ip_df[ip_col].values))
# TODO replace
ip_dicts = [{**ent.Location.properties, "IpAddress": ent.Address} for ent in geo_ips[1]]
df_out = pd.DataFrame(data=ip_dicts)
geo_df = df.merge(df_out, how="left", left_on=ip_col, right_on="IpAddress")
md(f"Querying WhoIs for {len(ip_df)} ip addresses...")
whois_df = ip_df.copy()
# Get the WhoIs results
whois_df[["ASNDesc", "WhoisResult"]] = (
ip_df
.apply(lambda x: get_whois_info(x[ip_col], show_progress=True),
axis=1, result_type="expand"))
geo_whois_df = geo_df.merge(whois_df, how="left", right_on=ip_col, left_on=ip_col)
return geo_whois_df
# Based on the account type, advice the user where to go next.
acct, source = selected_account(select_acct.value)
md(f"Account '{acct}'. Source is '{source}'", "bold, large, blue")
goto = lambda x: display(Markdown(f"### For further analysis go to {x}"))
if source == "LinuxHostLogon":
goto("go to [LinuxHostLogon](#Linux-Host)")
if source == "WindowsHostLogon":
goto("go to [WindowsHostLogon](#Windows-Host)")
if source in ["AADLogon", "AzureActivity", "O365Activity"]:
goto("go to [AAD/Office Account](#AAD/Office-Account)")
Account 'alexw@m365x648731.onmicrosoft.com'. Source is 'O365Activity'
For Windows accounts we look for the following types of data:
ext_logon_status = "| extend LogonStatus = iff(EventID == 4624, 'success', 'failed')"
all_win_logons = (qry_prov.WindowsSecurity
.list_logon_attempts_by_account(**acct_query_params(),
add_query_items=ext_logon_status))
logon_summary = (all_win_logons
.groupby("Computer")
.agg(
TotalLogons=pd.NamedAgg(column="EventID", aggfunc="count"),
LogonResult=pd.NamedAgg(column="LogonStatus", aggfunc=lambda x: x.value_counts().to_dict()),
IPAddresses=pd.NamedAgg(column="IpAddress", aggfunc=lambda x: x.unique().tolist()),
LogonTypeCount=pd.NamedAgg(column="LogonType", aggfunc=lambda x: x.value_counts().to_dict()),
FirstLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="min"),
LastLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="max"),
)
)
display(logon_summary)
nbdisplay.display_timeline(data=all_win_logons,
group_by="IpAddress",
source_columns=["Computer", "LogonStatus", "LogonType"],
title="Logons")
Then reload provider settings:
mylookup = TILookup()
mylookup.reload_provider_settings()
ti_results, all_win_logons_ti, src_ip_addrs_win = check_ip_ti(df=all_win_logons, ip_col="IpAddress")
if not ti_results.empty:
md(f"{len(ti_results)} threat intelligence hits have been "
+ "matched on one or more source IP addresses.", "bold, red, large")
md(" You should investigate these hosts using "
+ "the 'Entity Explorer - Windows Host' notebook", "bold, red" )
md("Logon details for TI matches are in the `all_win_logons_ti` DataFrame")
display(ti_results)
else:
md("No additional items found for logged on hosts")
10 threat intelligence hits have been matched on one or more source IP addresses.
You should investigate these hosts using the 'Entity Explorer - Windows Host' notebook
Logon details for TI matches are in the `all_win_logons_ti` DataFrame
Ioc | IocType | QuerySubtype | Provider | Result | Severity | Details | RawResult | Reference | Status | |
---|---|---|---|---|---|---|---|---|---|---|
0 | 185.81.128.116 | ipv4 | None | OTX | True | 2 | {'pulse_count': 4, 'names': ['RDP Attackers - August 2019 - A', 'Scan port 3389 RDP (S3#)', 'Sca... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.81.128.116/general | 0 |
1 | 185.107.45.130 | ipv4 | None | OTX | True | 2 | {'pulse_count': 3, 'names': ['RDP Attackers - October 2019 - C', 'Shunlist IPs - 2018-03-18', 'R... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.107.45.130/general | 0 |
2 | 173.249.58.228 | ipv4 | None | OTX | True | 2 | {'pulse_count': 8, 'names': ['RDP Attackers - October 2019 - B', 'RDP Attackers - September 2019... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/173.249.58.228/general | 0 |
3 | 63.150.106.131 | ipv4 | None | OTX | True | 2 | {'pulse_count': 2, 'names': ['Shunlist IPs - 2018-01-21', 'RiskDiscovery HoneyDB sensors feeds -... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/63.150.106.131/general | 0 |
4 | 212.92.106.156 | ipv4 | None | OTX | True | 2 | {'pulse_count': 8, 'names': ['RDP Attackers - October 2019 - C', 'RDP Attackers - October 2019 -... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/212.92.106.156/general | 0 |
0 | 185.81.128.116 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Firewall deny log analysis', 're... | {'ip': '185.81.128.116', 'history': [{'created': '2014-12-17T07:27:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/185.81.128.116 | 0 |
1 | 185.107.45.130 | ipv4 | None | XForce | True | 2 | {'score': 10, 'cats': {'Spam': 100, 'Dynamic IPs': 71}, 'categoryDescriptions': {'Spam': 'This c... | {'ip': '185.107.45.130', 'history': [{'created': '2015-07-04T06:21:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/185.107.45.130 | 0 |
2 | 173.249.58.228 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '173.249.58.228', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/173.249.58.228 | 0 |
3 | 63.150.106.131 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '63.150.106.131', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/63.150.106.131 | 0 |
4 | 212.92.106.156 | ipv4 | None | XForce | True | 2 | {'score': 10, 'cats': {'Spam': 100}, 'categoryDescriptions': {'Spam': 'This category lists IP ad... | {'ip': '212.92.106.156', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/212.92.106.156 | 0 |
all_win_logons_geo = check_geo_whois(src_ip_addrs_win, all_win_logons, "IpAddress")
md("Geolocations and ASN Owner for account logon source IP addresses. Information only", "bold")
(all_win_logons_geo[~all_win_logons_geo["CountryName"].isna()]
.groupby(["Computer", "IpAddress", "CountryCode","CountryName", "City", "ASNDesc"])
.agg(
TotalLogons=pd.NamedAgg(column="EventID", aggfunc="count"),
LogonResult=pd.NamedAgg(column="LogonStatus", aggfunc=lambda x: x.value_counts().to_dict()),
LogonTypeCount=pd.NamedAgg(column="LogonType", aggfunc=lambda x: x.value_counts().to_dict()),
FirstLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="min"),
LastLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="max"),
)
)
Geolocations and ASN Owner for account logon source IP addresses. Information only
TotalLogons | LogonResult | LogonTypeCount | FirstLogon | LastLogon | ||||||
---|---|---|---|---|---|---|---|---|---|---|
Computer | IpAddress | CountryCode | CountryName | City | ASNDesc | |||||
WebServer-1 | 173.249.58.228 | DE | Germany | Nuremberg | CONTABO, DE | 1 | {'failed': 1} | {3: 1} | 2019-10-30 09:18:11.770 | 2019-10-30 09:18:11.770 |
185.107.45.130 | NL | Netherlands | Oss | NFORCE, NL | 1 | {'failed': 1} | {3: 1} | 2019-10-30 04:19:25.500 | 2019-10-30 04:19:25.500 |
related_host_alerts = []
for host in all_win_logons["Computer"].unique():
host_alerts = qry_prov.SecurityAlert.list_related_alerts(
start=acct_query_params()["start"],
end=acct_query_params()["end"],
host_name=host
)
related_host_alerts.append(host_alerts)
related_host_alerts_df = pd.concat(related_host_alerts)
# Show host alerts that were not in the Account alerts list
related_host_alerts_df = related_host_alerts_df[~related_host_alerts_df["SystemAlertId"]
.isin(related_alerts["SystemAlertId"])]
if not related_host_alerts_df.empty:
md(f"{len(related_host_alerts_df)} additional alerts have been "
+ "triggered on one or more hosts.", "bold, red, large")
md(" You should investigate these hosts using "
+ "the 'Entity Explorer - Windows Host' notebook", "bold, red" )
display(related_host_alerts_df)
else:
md("No additional alerts found")
No additional alerts found
We can also search for alerts that contain the IP addresses that were the origin of logons to the host.
ip_list = ",".join(list(all_win_logons["IpAddress"].unique()))
related_ip_alerts_df = qry_prov.SecurityAlert.list_alerts_for_ip(
start=acct_query_params()["start"],
end=acct_query_params()["end"],
source_ip_list=ip_list
)
# remove Account and host alerts already seen
related_ip_alerts_df = related_ip_alerts_df[~related_ip_alerts_df["SystemAlertId"]
.isin(related_alerts["SystemAlertId"])]
related_ip_alerts_df = related_ip_alerts_df[~related_ip_alerts_df["SystemAlertId"]
.isin(related_host_alerts_df["SystemAlertId"])]
if not related_ip_alerts_df.empty:
md(f"{len(related_ip_alerts_df)} additional alerts have been "
+ "triggered from one or more source IPs.", "bold, red, large")
md(" You should investigate these IPs using "
+ "the 'Entity Explorer - IP Address' notebook", "bold, red" )
display(related_ip_alerts_df)
else:
md("No additional alerts found.")
No additional alerts found.
related_host_bkmks = []
for host in all_win_logons["Computer"].unique():
host_bkmks = qry_prov.AzureSentinel.list_bookmarks_for_entity(
start=acct_query_params()["start"],
end=acct_query_params()["end"],
entity_id=f"'{host}'"
)
related_host_bkmks.append(host_bkmks)
related_host_bkmks_df = pd.concat(related_host_bkmks)
# Show host bookmarks that were not in the Account bookmarks list
related_host_bkmks_df = related_host_bkmks_df[~related_host_bkmks_df["BookmarkId"]
.isin(related_bkmark_df["BookmarkId"])]
if not related_host_bkmks_df.empty:
md(f"{len(related_host_bkmks_df)} additional investigation bookmarks have been "
+ "found for one or more hosts.", "bold, red, large")
md(" You should investigate these hosts using "
+ "the 'Entity Explorer - Windows Host' notebook", "bold, red" )
display(related_host_bkmks_df)
else:
md("No additional items found for logged on hosts")
No additional items found for logged on hosts
For Linux accounts we look for the following types of data:
all_lx_logons = (qry_prov.LinuxSyslog
.list_logons_for_account(**acct_query_params()))
logon_summary = (all_lx_logons
.groupby("Computer")
.agg(
TotalLogons=pd.NamedAgg(column="Computer", aggfunc="count"),
FailedLogons=pd.NamedAgg(column="LogonResult", aggfunc=lambda x: x.value_counts().to_dict()),
IPAddresses=pd.NamedAgg(column="SourceIP", aggfunc=lambda x: x.unique().tolist()),
LogonTypeCount=pd.NamedAgg(column="LogonType", aggfunc=lambda x: x.value_counts().to_dict()),
FirstLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="min"),
LastLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="max"),
)
)
display(logon_summary)
nbdisplay.display_timeline(data=all_lx_logons,
group_by="SourceIP",
source_columns=["Computer", "LogonResult", "LogonType"],
title="Logons");
Then reload provider settings:
mylookup = TILookup()
mylookup.reload_provider_settings()
ti_results_lx, all_lx_logons_ti, src_ip_addrs_lx = check_ip_ti(df=all_lx_logons, ip_col="SourceIP")
if not ti_results_lx.empty:
md(f"{len(ti_results_lx)} threat intelligence hits have been "
+ "matched on one or more source IP addresses.", "bold, red, large")
md(" You should investigate these hosts using "
+ "the 'Entity Explorer - Linux Host' notebook", "bold, red" )
display(ti_results_lx)
else:
md("No additional items found for logged on hosts")
all_lx_logons_geo = check_geo_whois(src_ip_addrs_lx, all_lx_logons, "SourceIP")
md("Geolocations and ASN Owner for account logon source IP addresses. Information only", "bold")
(all_lx_logons_geo[~all_lx_logons_geo["CountryName"].isna()]
.groupby(["Computer", "SourceIP", "CountryCode","CountryName", "City", "ASNDesc"])
.agg(
TotalLogons=pd.NamedAgg(column="SourceSystem", aggfunc="count"),
LogonResult=pd.NamedAgg(column="LogonResult", aggfunc=lambda x: x.value_counts().to_dict()),
LogonTypeCount=pd.NamedAgg(column="LogonType", aggfunc=lambda x: x.value_counts().to_dict()),
FirstLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="min"),
LastLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="max"),
)
)
related_host_alerts = []
for host in all_lx_logons["Computer"].unique():
host_alerts = qry_prov.SecurityAlert.list_related_alerts(
start=acct_query_params()["start"],
end=acct_query_params()["end"],
host_name=host
)
related_host_alerts.append(host_alerts)
related_host_alerts_df = pd.concat(related_host_alerts)
# Show host alerts that were not in the Account alerts list
related_host_alerts_df = related_host_alerts_df[~related_host_alerts_df["SystemAlertId"]
.isin(related_alerts["SystemAlertId"])]
if not related_host_alerts_df.empty:
md(f"{len(related_host_alerts_df)} additional alerts have been "
+ "triggered on one or more hosts.", "bold, red, large")
md(" You should investigate these hosts using "
+ "the 'Entity Explorer - Linux Host' notebook", "bold, red" )
display(related_host_alerts_df[['TenantId','TimeGenerated','AlertDisplayName','ConfidenceLevel','ConfidenceScore','Computer','ExtendedProperties','Entities']])
else:
md("No additional items found for logged on hosts")
We can also search for alerts that contain the IP addresses that were the origin of logons to the host.
ip_list = ",".join(list(all_lx_logons["SourceIP"].unique()))
related_ip_alerts_df = qry_prov.SecurityAlert.list_alerts_for_ip(
start=acct_query_params()["start"],
end=acct_query_params()["end"],
source_ip_list=ip_list
)
# remove Account and host alerts already seen
related_ip_alerts_df = related_ip_alerts_df[~related_ip_alerts_df["SystemAlertId"]
.isin(related_alerts["SystemAlertId"])]
related_ip_alerts_df = related_ip_alerts_df[~related_ip_alerts_df["SystemAlertId"]
.isin(related_host_alerts_df["SystemAlertId"])]
if not related_ip_alerts_df.empty:
md(f"{len(related_ip_alerts_df)} additional alerts have been "
+ "triggered from one or more source IPs.", "bold, red, large")
md(" You should investigate these IPs using "
+ "the 'Entity Explorer - IP Address' notebook", "bold, red" )
display(related_ip_alerts_df)
else:
md("No additional alerts found.")
related_host_bkmks = []
for host in all_lx_logons["Computer"].unique():
host_bkmks = qry_prov.AzureSentinel.list_bookmarks_for_entity(
start=acct_query_params()["start"],
end=acct_query_params()["end"],
entity_id=host
)
related_host_bkmks.append(host_bkmks)
related_host_bkmks_df = pd.concat(related_host_bkmks)
# Show host bookmarks that were not in the Account bookmarks list
related_host_bkmks_df = related_host_bkmks_df[~related_host_bkmks_df["BookmarkId"]
.isin(related_bkmark_df["BookmarkId"])]
if not related_host_bkmks_df.empty:
md(f"{len(related_host_bkmks_df)} additional investigation bookmarks have been "
+ "found for one or more hosts.", "bold, red, large")
md(" You should investigate these hosts using "
+ "the 'Entity Explorer - Windows Host' notebook", "bold, red" )
display(related_host_bkmks_df)
else:
md("No additional items found for logged on hosts")
For an Azure Active Directory account we look for the following data:
# Fetch the data
aad_sum_qry = """
| extend UserPrincipalName=tolower(UserPrincipalName)
| project-rename Operation=OperationName, AppResourceProvider=AppDisplayName"""
aad_signin_df = (qry_prov.Azure
.list_aad_signins_for_account(**acct_query_params(),
add_query_items=aad_sum_qry)
)
az_sum_qry = """
| extend UserPrincipalName=tolower(Caller)
| project-rename IPAddress=CallerIpAddress, Operation=OperationName,
AppResourceProvider=ResourceProvider"""
azure_activity_df = (qry_prov.Azure
.list_azure_activity_for_account(**acct_query_params(),
add_query_items=az_sum_qry)
)
o365_sum_qry = """
| extend UserPrincipalName=tolower(UserId)
| project-rename IPAddress=ClientIP, ResourceId=OfficeObjectId,
AppResourceProvider=OfficeWorkload"""
o365_activity_df = (qry_prov.Office365
.list_activity_for_account(**acct_query_params(),
add_query_items=o365_sum_qry)
)
az_all_data = pd.concat([aad_signin_df, azure_activity_df, o365_activity_df], sort=False)
nbdisplay.display_timeline(data=az_all_data,
group_by="AppResourceProvider",
source_columns=["Operation", "IPAddress", "AppResourceProvider"],
title="Azure Signin activity by Provider")
nbdisplay.display_timeline(data=az_all_data,
group_by="IPAddress",
source_columns=["Operation", "IPAddress", "AppResourceProvider"],
title="Azure Operations by Source IP")
nbdisplay.display_timeline(data=az_all_data,
group_by="Operation",
source_columns=["Operation", "IPAddress", "AppResourceProvider"],
title="Azure Operations by Operation");
(az_all_data
.groupby(["UserPrincipalName", "Type", "IPAddress", "AppResourceProvider", "UserType"])
.agg(
OperationCount=pd.NamedAgg(column="Type", aggfunc="count"),
OperationTypes=pd.NamedAgg(column="Operation", aggfunc=lambda x: x.unique().tolist()),
Resources=pd.NamedAgg(column="ResourceId", aggfunc="nunique"),
FirstOperation=pd.NamedAgg(column="TimeGenerated", aggfunc="min"),
LastOperation=pd.NamedAgg(column="TimeGenerated", aggfunc="max"),
)
)
OperationCount | OperationTypes | Resources | FirstOperation | LastOperation | |||||
---|---|---|---|---|---|---|---|---|---|
UserPrincipalName | Type | IPAddress | AppResourceProvider | UserType | |||||
alexw@m365x648731.onmicrosoft.com | OfficeActivity | SharePoint | Regular | 15 | [FileAccessed] | 7 | 2019-09-16 18:06:06 | 2019-09-19 19:18:06 | |
104.41.146.53 | SharePoint | Regular | 7 | [SearchQueryPerformed] | 5 | 2019-09-19 19:16:20 | 2019-09-20 18:20:49 | ||
109.70.100.26 | SharePoint | Regular | 7 | [FilePreviewed] | 3 | 2019-09-20 18:20:50 | 2019-09-20 18:20:50 | ||
176.10.104.240 | SharePoint | Regular | 208 | [FileModified, FileAccessed, FileUploaded, FileModifiedExtended, FileDeleted] | 36 | 2019-09-19 19:27:55 | 2019-09-19 20:26:23 | ||
176.10.99.200:45866 | Exchange | Admin | 1 | [New-InboxRule] | 1 | 2019-09-20 20:11:57 | 2019-09-20 20:11:57 | ||
185.207.139.2:30396 | Exchange | Admin | 1 | [Remove-InboxRule] | 1 | 2019-09-24 23:10:38 | 2019-09-24 23:10:38 | ||
185.207.139.2:7127 | Exchange | Admin | 1 | [Remove-InboxRule] | 1 | 2019-09-24 23:10:35 | 2019-09-24 23:10:35 | ||
185.220.101.1 | SharePoint | Regular | 7 | [FilePreviewed] | 3 | 2019-10-16 18:09:39 | 2019-10-16 18:09:41 | ||
185.220.101.31 | SharePoint | Regular | 26 | [FilePreviewed, FileAccessed, ListCreated, SearchQueryPerformed, PageViewed] | 25 | 2019-09-18 17:02:06 | 2019-09-18 17:10:23 | ||
185.220.101.6 | SharePoint | Regular | 15 | [FileDownloaded] | 14 | 2019-09-16 18:11:40 | 2019-09-16 18:12:53 | ||
185.220.102.8 | SharePoint | Regular | 168 | [FileUploaded, PermissionLevelAdded, PageViewed, FileAccessed, FileModified, FilePreviewed, Anon... | 46 | 2019-09-16 18:41:42 | 2019-09-16 20:43:20 | ||
199.249.230.111 | SharePoint | Regular | 8 | [FilePreviewed] | 4 | 2019-09-19 19:16:23 | 2019-09-19 19:16:26 | ||
199.249.230.113 | SharePoint | Regular | 1 | [FileAccessed] | 1 | 2019-09-16 18:16:38 | 2019-09-16 18:16:38 | ||
20.190.128.101 | SharePoint | Regular | 1 | [FilePreviewed] | 1 | 2019-09-19 19:16:16 | 2019-09-19 19:16:16 | ||
20.190.128.103 | SharePoint | Regular | 5 | [FilePreviewed] | 4 | 2019-09-19 19:16:14 | 2019-09-19 19:16:14 | ||
20.190.129.100 | SharePoint | Regular | 4 | [FilePreviewed] | 4 | 2019-09-20 18:20:46 | 2019-09-20 18:20:48 | ||
23.129.64.152 | SharePoint | Regular | 32 | [FileAccessed, FilePreviewed, PageViewed, SearchQueryPerformed] | 31 | 2019-09-18 17:01:25 | 2019-09-18 17:03:03 | ||
40.117.152.107 | SharePoint | Regular | 17 | [SearchQueryPerformed] | 11 | 2019-09-16 17:42:17 | 2019-09-18 17:02:37 | ||
40.126.9.49 | SharePoint | Regular | 2 | [FilePreviewed] | 1 | 2019-10-16 18:09:38 | 2019-10-16 18:09:38 | ||
40.126.9.50 | SharePoint | Regular | 5 | [FilePreviewed] | 3 | 2019-09-16 17:42:18 | 2019-09-16 17:42:23 | ||
40.126.9.51 | SharePoint | Regular | 4 | [FilePreviewed] | 2 | 2019-10-16 18:09:36 | 2019-10-16 18:09:36 | ||
52.109.6.30 | SharePoint | Regular | 7 | [FileAccessed] | 6 | 2019-09-19 19:16:23 | 2019-10-16 18:09:42 | ||
66.146.193.33 | SharePoint | Regular | 33 | [PageViewed, FileAccessed, FileDeleted, FolderDeleted, FilePreviewed] | 32 | 2019-09-19 19:17:59 | 2019-09-19 19:22:05 | ||
77.247.181.163 | SharePoint | Regular | 4 | [FilePreviewed] | 4 | 2019-09-16 17:42:29 | 2019-09-16 17:42:38 | ||
92.62.139.103 | SharePoint | Regular | 100 | [FileAccessed, FilePreviewed, SearchQueryPerformed, PageViewed, FileDownloaded, FileAccessedExte... | 63 | 2019-09-16 18:05:57 | 2019-09-16 18:18:07 | ||
[2a02:418:6017::148]:45644 | Exchange | Admin | 1 | [New-InboxRule] | 1 | 2019-09-16 17:43:36 | 2019-09-16 17:43:36 |
Then reload provider settings:
mylookup = TILookup()
mylookup.reload_provider_settings()
ti_results_az, all_az_ti, src_ip_addrs_az = check_ip_ti(df=az_all_data, ip_col="IPAddress")
if not ti_results_az.empty:
md(f"{len(ti_results_az)} threat intelligence hits have been "
+ "matched on one or more source IP addresses.", "bold, red, large")
md(" You should investigate these IP addresses using "
+ "the 'Entity Explorer - IP Address' notebook", "bold, red" )
display(ti_results_az)
else:
md("No additional items found")
63 threat intelligence hits have been matched on one or more source IP addresses.
You should investigate these IP addresses using the 'Entity Explorer - IP Address' notebook
Ioc | IocType | QuerySubtype | Provider | Result | Severity | Details | RawResult | Reference | Status | |
---|---|---|---|---|---|---|---|---|---|---|
3 | 23.129.64.193 | ipv4 | None | OTX | True | 2 | {'pulse_count': 35, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'VNC honeypot logs for 2019/... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/23.129.64.193/general | 0 |
4 | 185.220.101.48 | ipv4 | None | OTX | True | 2 | {'pulse_count': 35, 'names': ['TOR Nodes', 'IOCs weekly 03/10/19', 'spraying attack against Offi... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.220.101.48/general | 0 |
5 | 198.98.58.135 | ipv4 | None | OTX | True | 2 | {'pulse_count': 7, 'names': ['TOR Nodes', 'IOCs weekly 03/10/19', 'spraying attack against Offic... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/198.98.58.135/general | 0 |
10 | 176.10.99.200 | ipv4 | None | OTX | True | 2 | {'pulse_count': 50, 'names': ['Webscanners 2018-02-09 thru current day', 'TOR Nodes', 'N6 Torli... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/176.10.99.200/general | 0 |
17 | 87.118.116.103 | ipv4 | None | OTX | True | 2 | {'pulse_count': 30, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'N6 Torlist 2019-08-05', 'VN... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/87.118.116.103/general | 0 |
19 | 217.115.10.132 | ipv4 | None | OTX | True | 2 | {'pulse_count': 50, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'VNC honeypot logs for 2019/... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/217.115.10.132/general | 0 |
20 | 185.4.132.135 | ipv4 | None | OTX | True | 2 | {'pulse_count': 4, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'N6 Torlist 2019-08-05', 'dan... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.4.132.135/general | 0 |
23 | 185.220.102.8 | ipv4 | None | OTX | True | 2 | {'pulse_count': 10, 'names': ['TOR Nodes', 'SSH - US Honeypot IoCs 2019-09-19', 'N6 Torlist 2019... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.220.102.8/general | 0 |
24 | 77.247.181.163 | ipv4 | None | OTX | True | 2 | {'pulse_count': 50, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'VNC honeypot logs for 2019/... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/77.247.181.163/general | 0 |
25 | 185.220.101.6 | ipv4 | None | OTX | True | 2 | {'pulse_count': 45, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'VNC honeypot logs for 2019/... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.220.101.6/general | 0 |
26 | 92.62.139.103 | ipv4 | None | OTX | True | 2 | {'pulse_count': 9, 'names': ['TOR Nodes', 'IOCs weekly 03/10/19', 'spraying attack against Offic... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/92.62.139.103/general | 0 |
29 | 199.249.230.113 | ipv4 | None | OTX | True | 2 | {'pulse_count': 31, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'Suspicious IPs-August-10-08... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/199.249.230.113/general | 0 |
33 | 185.220.101.1 | ipv4 | None | OTX | True | 2 | {'pulse_count': 45, 'names': ['Webscanners 2018-02-09 thru current day', 'TOR Nodes', 'N6 Torli... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.220.101.1/general | 0 |
34 | 23.129.64.152 | ipv4 | None | OTX | True | 2 | {'pulse_count': 36, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'N6 Torlist 2019-08-05', 'VN... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/23.129.64.152/general | 0 |
35 | 185.220.101.31 | ipv4 | None | OTX | True | 2 | {'pulse_count': 39, 'names': ['TOR Nodes', 'IOCs weekly 03/10/19', 'spraying attack against Offi... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.220.101.31/general | 0 |
36 | 176.10.104.240 | ipv4 | None | OTX | True | 2 | {'pulse_count': 44, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'N6 Torlist 2019-08-05', 'VN... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/176.10.104.240/general | 0 |
38 | 66.146.193.33 | ipv4 | None | OTX | True | 2 | {'pulse_count': 5, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'N6 Torlist 2019-08-05', 'dan... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/66.146.193.33/general | 0 |
41 | 199.249.230.111 | ipv4 | None | OTX | True | 2 | {'pulse_count': 29, 'names': ['TOR Nodes', 'N6 Torlist 2019-08-22', 'Suspicious IPs-August-10-08... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/199.249.230.111/general | 0 |
43 | 185.207.139.2 | ipv4 | None | OTX | True | 2 | {'pulse_count': 4, 'names': ['TOR Nodes', 'IOCs weekly 03/10/19', 'spraying attack against Offic... | {'sections': ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list',... | https://otx.alienvault.com/api/v1/indicators/IPv4/185.207.139.2/general | 0 |
0 | 131.107.174.181 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Third party feed', 'reasonDescri... | {'ip': '131.107.174.181', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.174.181 | 0 |
1 | 131.107.159.181 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Third party feed', 'reasonDescri... | {'ip': '131.107.159.181', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.159.181 | 0 |
2 | 131.107.160.181 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Third party feed', 'reasonDescri... | {'ip': '131.107.160.181', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.160.181 | 0 |
3 | 23.129.64.193 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Spam': 71, 'Anonymisation Services': 86, 'Bots': 57}, 'categoryDescript... | {'ip': '23.129.64.193', 'history': [{'created': '2017-07-20T06:21:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/23.129.64.193 | 0 |
4 | 185.220.101.48 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '185.220.101.48', 'history': [{'created': '2017-09-13T06:21:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/185.220.101.48 | 0 |
5 | 198.98.58.135 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86}, 'categoryDescriptions': {'Anonymisation S... | {'ip': '198.98.58.135', 'history': [{'created': '2012-07-06T06:28:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/198.98.58.135 | 0 |
6 | 109.70.100.26 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '109.70.100.26', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/109.70.100.26 | 0 |
7 | 131.107.160.77 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.160.77', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/131.107.160.77 | 0 |
8 | 50.35.65.178 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '50.35.65.178', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional I... | https://api.xforce.ibmcloud.com/ipr/50.35.65.178 | 0 |
9 | 167.220.2.105 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '167.220.2.105', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/167.220.2.105 | 0 |
10 | 176.10.99.200 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Scanning IPs': 29, 'Bots': 43}, 'category... | {'ip': '176.10.99.200', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/176.10.99.200 | 0 |
11 | 131.107.159.205 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.159.205', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.159.205 | 0 |
12 | 167.220.2.123 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '167.220.2.123', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/167.220.2.123 | 0 |
13 | 131.107.159.143 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.159.143', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.159.143 | 0 |
14 | 131.107.147.105 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.147.105', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.147.105 | 0 |
15 | 131.107.174.205 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.174.205', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.174.205 | 0 |
16 | 131.107.160.205 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.160.205', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.160.205 | 0 |
17 | 87.118.116.103 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '87.118.116.103', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/87.118.116.103 | 0 |
18 | 109.70.100.24 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '109.70.100.24', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/109.70.100.24 | 0 |
19 | 217.115.10.132 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Scanning IPs': 29, 'Bots': 43}, 'category... | {'ip': '217.115.10.132', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/217.115.10.132 | 0 |
20 | 185.4.132.135 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86}, 'categoryDescriptions': {'Anonymisation S... | {'ip': '185.4.132.135', 'history': [{'created': '2012-09-28T06:27:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/185.4.132.135 | 0 |
21 | 131.107.147.205 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.147.205', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.147.205 | 0 |
22 | 131.107.174.123 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '131.107.174.123', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/131.107.174.123 | 0 |
23 | 185.220.102.8 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '185.220.102.8', 'history': [{'created': '2017-09-13T06:21:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/185.220.102.8 | 0 |
24 | 77.247.181.163 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 29}, 'categoryDescriptions': {'Ano... | {'ip': '77.247.181.163', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/77.247.181.163 | 0 |
25 | 185.220.101.6 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '185.220.101.6', 'history': [{'created': '2017-09-13T06:21:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/185.220.101.6 | 0 |
26 | 92.62.139.103 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Spam': 29, 'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescript... | {'ip': '92.62.139.103', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/92.62.139.103 | 0 |
27 | 40.117.152.107 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '40.117.152.107', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/40.117.152.107 | 0 |
28 | 40.126.9.50 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '40.126.9.50', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional In... | https://api.xforce.ibmcloud.com/ipr/40.126.9.50 | 0 |
29 | 199.249.230.113 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86}, 'categoryDescriptions': {'Anonymisation S... | {'ip': '199.249.230.113', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/199.249.230.113 | 0 |
30 | 40.126.9.51 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '40.126.9.51', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional In... | https://api.xforce.ibmcloud.com/ipr/40.126.9.51 | 0 |
31 | 52.109.6.30 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '52.109.6.30', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional In... | https://api.xforce.ibmcloud.com/ipr/52.109.6.30 | 0 |
32 | 40.126.9.49 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '40.126.9.49', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional In... | https://api.xforce.ibmcloud.com/ipr/40.126.9.49 | 0 |
33 | 185.220.101.1 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86}, 'categoryDescriptions': {'Anonymisation S... | {'ip': '185.220.101.1', 'history': [{'created': '2017-09-13T06:21:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/185.220.101.1 | 0 |
34 | 23.129.64.152 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Spam': 86, 'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescript... | {'ip': '23.129.64.152', 'history': [{'created': '2017-07-20T06:21:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/23.129.64.152 | 0 |
35 | 185.220.101.31 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '185.220.101.31', 'history': [{'created': '2017-09-13T06:21:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/185.220.101.31 | 0 |
36 | 176.10.104.240 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '176.10.104.240', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/176.10.104.240 | 0 |
37 | 104.41.146.53 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '104.41.146.53', 'history': [{'created': '2014-05-08T06:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/104.41.146.53 | 0 |
38 | 66.146.193.33 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '66.146.193.33', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/66.146.193.33 | 0 |
39 | 20.190.128.101 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '20.190.128.101', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/20.190.128.101 | 0 |
40 | 20.190.128.103 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '20.190.128.103', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/20.190.128.103 | 0 |
41 | 199.249.230.111 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 57}, 'categoryDescriptions': {'Ano... | {'ip': '199.249.230.111', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regiona... | https://api.xforce.ibmcloud.com/ipr/199.249.230.111 | 0 |
42 | 20.190.129.100 | ipv4 | None | XForce | True | 1 | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '20.190.129.100', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional... | https://api.xforce.ibmcloud.com/ipr/20.190.129.100 | 0 |
43 | 185.207.139.2 | ipv4 | None | XForce | True | 2 | {'score': 8.6, 'cats': {'Anonymisation Services': 86, 'Bots': 43}, 'categoryDescriptions': {'Ano... | {'ip': '185.207.139.2', 'history': [{'created': '2017-06-10T06:21:00.000Z', 'reason': 'Regional ... | https://api.xforce.ibmcloud.com/ipr/185.207.139.2 | 0 |
all_az_geo = check_geo_whois(src_ip_addrs_az.iloc[0:50], az_all_data, "IPAddress")
md("Geolocations and ASN Owner for source IP addresses. Information only", "bold")
(all_az_geo[~all_az_geo["CountryName"].isna()]
.groupby(["UserPrincipalName", "IPAddress", "CountryCode","CountryName", "City", "ASNDesc"])
.agg(
TotalOperations=pd.NamedAgg(column="SourceSystem", aggfunc="count"),
Operations=pd.NamedAgg(column="Operation", aggfunc=lambda x: x.value_counts().to_dict()),
AppResources=pd.NamedAgg(column="AppResourceProvider", aggfunc=lambda x: x.unique().tolist()),
FirstLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="min"),
LastLogon=pd.NamedAgg(column="TimeGenerated", aggfunc="max"),
)
)
Querying geolocation for 44 ip addresses...
Querying WhoIs for 44 ip addresses...
Geolocations and ASN Owner for source IP addresses. Information only
TotalOperations | Operations | AppResources | FirstLogon | LastLogon | ||||||
---|---|---|---|---|---|---|---|---|---|---|
UserPrincipalName | IPAddress | CountryCode | CountryName | City | ASNDesc | |||||
alexw@m365x648731.onmicrosoft.com | 104.41.146.53 | US | United States | Washington | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 7 | {'SearchQueryPerformed': 7} | [SharePoint] | 2019-09-19 19:16:20.000 | 2019-09-20 18:20:49.000 |
109.70.100.24 | AT | Austria | Vienna | APPLIEDPRIVACY-AS, AT | 6 | {'Sign-in activity': 6} | [Office 365 Exchange Online] | 2019-09-25 16:21:43.562 | 2019-09-29 17:35:00.099 | |
109.70.100.26 | AT | Austria | Vienna | APPLIEDPRIVACY-AS, AT | 17 | {'Sign-in activity': 10, 'FilePreviewed': 7} | [O365 Suite UX, Office 365 SharePoint Online, SharePoint] | 2019-09-19 19:15:59.381 | 2019-09-20 18:20:50.000 | |
131.107.147.105 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 36 | {'Create Saved Search': 14, 'Update Case Investigation': 12, 'Sign-in activity': 6, 'Gets workfl... | [Azure Notebooks, Azure Portal, Microsoft.OperationalInsights, Microsoft.Logic, Microsoft.Securi... | 2019-10-14 21:23:46.112 | 2019-10-16 00:02:03.868 | |
131.107.147.205 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 40 | {'Update Case Investigation': 34, 'Update Cases': 4, 'Gets workflow recommend operation groups': 2} | [Microsoft.SecurityInsights, Microsoft.Logic] | 2019-10-15 15:58:35.552 | 2019-10-23 16:39:05.220 | |
131.107.159.143 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 1 | {'Sign-in activity': 1} | [Azure Portal] | 2019-10-17 16:27:42.396 | 2019-10-17 16:27:42.396 | |
131.107.159.181 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 1 | {'Sign-in activity': 1} | [Azure Portal] | 2019-10-29 23:41:38.870 | 2019-10-29 23:41:38.870 | |
131.107.159.205 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 3 | {'Sign-in activity': 3} | [Azure Portal] | 2019-10-17 15:27:34.722 | 2019-10-22 15:26:25.738 | |
131.107.160.181 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 1 | {'Sign-in activity': 1} | [Azure Portal] | 2019-10-29 23:44:32.382 | 2019-10-29 23:44:32.382 | |
131.107.160.205 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 2 | {'Sign-in activity': 2} | [Azure Portal] | 2019-10-17 20:39:51.333 | 2019-10-18 15:42:59.049 | |
131.107.160.77 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 5 | {'Sign-in activity': 5} | [Azure Portal] | 2019-10-15 15:45:44.295 | 2019-10-23 16:38:09.019 | |
131.107.174.123 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 4 | {'Update Case Investigation': 4} | [Microsoft.SecurityInsights] | 2019-10-17 15:49:17.520 | 2019-10-17 15:52:18.166 | |
131.107.174.181 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 1 | {'Sign-in activity': 1} | [Azure Portal] | 2019-10-29 23:40:25.427 | 2019-10-29 23:40:25.427 | |
131.107.174.205 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 3 | {'Sign-in activity': 3} | [Azure Portal] | 2019-10-17 18:06:33.396 | 2019-10-17 18:11:01.639 | |
167.220.2.105 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 1 | {'Sign-in activity': 1} | [Azure Portal] | 2019-10-15 20:19:45.715 | 2019-10-15 20:19:45.715 | |
167.220.2.123 | US | United States | Redmond | MICROSOFT-CORP-AS - Microsoft Corporation, US | 2 | {'Sign-in activity': 2} | [Azure Portal] | 2019-10-17 15:33:57.417 | 2019-10-17 15:34:04.089 | |
185.4.132.135 | GR | Greece | Nafplion | TOPHOST, GR | 4 | {'Sign-in activity': 4} | [Office 365 Exchange Online] | 2019-09-24 23:09:28.253 | 2019-09-24 23:09:32.785 | |
198.98.58.135 | US | United States | Buffalo | PONYNET - FranTech Solutions, US | 2 | {'Sign-in activity': 2} | [Office 365 Exchange Online] | 2019-09-28 17:32:59.827 | 2019-09-28 17:32:59.827 | |
20.190.128.101 | US | United States | San Antonio | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 1 | {'FilePreviewed': 1} | [SharePoint] | 2019-09-19 19:16:16.000 | 2019-09-19 19:16:16.000 | |
20.190.128.103 | US | United States | San Antonio | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 5 | {'FilePreviewed': 5} | [SharePoint] | 2019-09-19 19:16:14.000 | 2019-09-19 19:16:14.000 | |
20.190.129.100 | IE | Ireland | Dublin | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 4 | {'FilePreviewed': 4} | [SharePoint] | 2019-09-20 18:20:46.000 | 2019-09-20 18:20:48.000 | |
217.115.10.132 | DE | Germany | Berlin | NETSIGN, DE | 2 | {'Sign-in activity': 2} | [Office 365 Exchange Online] | 2019-09-24 23:06:14.437 | 2019-09-24 23:06:14.437 | |
23.129.64.152 | US | United States | Seattle | EMERALD-ONION - Emerald Onion, US | 32 | {'FileAccessed': 18, 'FilePreviewed': 7, 'PageViewed': 4, 'SearchQueryPerformed': 3} | [SharePoint] | 2019-09-18 17:01:25.000 | 2019-09-18 17:03:03.000 | |
23.129.64.193 | US | United States | Seattle | EMERALD-ONION - Emerald Onion, US | 4 | {'Sign-in activity': 4} | [Office 365 Exchange Online] | 2019-09-27 17:26:43.304 | 2019-09-27 17:26:49.681 | |
40.117.152.107 | US | United States | Washington | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 17 | {'SearchQueryPerformed': 17} | [SharePoint] | 2019-09-16 17:42:17.000 | 2019-09-18 17:02:37.000 | |
40.126.9.49 | NL | Netherlands | Amsterdam | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 2 | {'FilePreviewed': 2} | [SharePoint] | 2019-10-16 18:09:38.000 | 2019-10-16 18:09:38.000 | |
40.126.9.50 | NL | Netherlands | Amsterdam | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 5 | {'FilePreviewed': 5} | [SharePoint] | 2019-09-16 17:42:18.000 | 2019-09-16 17:42:23.000 | |
40.126.9.51 | NL | Netherlands | Amsterdam | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 4 | {'FilePreviewed': 4} | [SharePoint] | 2019-10-16 18:09:36.000 | 2019-10-16 18:09:36.000 | |
50.35.65.178 | US | United States | Redmond | FRONTIER-FRTR - Frontier Communications of America, Inc., US | 27 | {'Create Saved Search': 12, 'Update Case Investigation': 6, 'Sign-in activity': 5, 'Gets workflo... | [Azure Portal, Microsoft.Logic, Microsoft.OperationalInsights, Microsoft.SecurityInsights] | 2019-10-15 12:18:55.118 | 2019-10-24 23:19:31.193 | |
52.109.6.30 | US | United States | Boydton | MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation, US | 7 | {'FileAccessed': 7} | [SharePoint] | 2019-09-19 19:16:23.000 | 2019-10-16 18:09:42.000 | |
66.146.193.33 | US | United States | Chicago | ONSH-NET-CHGO-BLK01 - OnShore, Inc., US | 33 | {'FileAccessed': 17, 'FileDeleted': 9, 'PageViewed': 4, 'FilePreviewed': 2, 'FolderDeleted': 1} | [SharePoint] | 2019-09-19 19:17:59.000 | 2019-09-19 19:22:05.000 | |
92.62.139.103 | LT | Republic of Lithuania | Kaunas | BALTNETA Customers AS, LT | 100 | {'FileAccessed': 35, 'FilePreviewed': 27, 'PageViewed': 16, 'SearchQueryPerformed': 14, 'FileDow... | [SharePoint] | 2019-09-16 18:05:57.000 | 2019-09-16 18:18:07.000 |
ip_list = ",".join(list(src_ip_addrs_az["IPAddress"].unique()))
related_ip_alerts_df = qry_prov.SecurityAlert.list_alerts_for_ip(
start=acct_query_params()["start"],
end=acct_query_params()["end"],
source_ip_list=ip_list
)
# remove Account and host alerts already seen
related_ip_alerts_df = related_ip_alerts_df[~related_ip_alerts_df["SystemAlertId"]
.isin(related_alerts["SystemAlertId"])]
if not related_ip_alerts_df.empty:
md(f"{len(related_ip_alerts_df)} additional alerts have been "
+ "triggered from one or more source IPs.", "bold, red, large")
md(" You should investigate these IPs using "
+ "the 'Entity Explorer - IP Address' notebook", "bold, red" )
display(related_ip_alerts_df)
13 additional alerts have been triggered from one or more source IPs.
You should investigate these IPs using the 'Entity Explorer - IP Address' notebook
TenantId | TimeGenerated | AlertDisplayName | AlertName | Severity | Description | ProviderName | VendorName | VendorOriginalId | SystemAlertId | ResourceId | SourceComputerId | AlertType | ConfidenceLevel | ConfidenceScore | IsIncident | StartTimeUtc | EndTimeUtc | ProcessingEndTime | RemediationSteps | ExtendedProperties | Entities | SourceSystem | WorkspaceSubscriptionId | WorkspaceResourceGroup | ExtendedLinks | ProductName | ProductComponentName | Type | SystemAlertId1 | ExtendedProperties1 | Entities1 | MatchingIps | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
19 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 22:11:41 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 2df2d792-aca7-43b5-9e31-fa4e0618ad8c | 61787eba-f903-4b71-b211-dc1d6ec9b5f8 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 21:56:35 | 2019-10-16 22:06:35 | 2019-10-16 22:11:41 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 61787eba-f903-4b71-b211-dc1d6ec9b5f8 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
20 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 22:03:33 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 1d2be0b9-aded-4750-a372-47fbf1bf98b6 | 2519550e-4850-4616-974f-422b4867a161 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 21:48:26 | 2019-10-16 21:58:26 | 2019-10-16 22:03:33 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 2519550e-4850-4616-974f-422b4867a161 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
21 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 22:21:52 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 227353f7-f56b-4500-b843-564dec775729 | 695d982e-de0b-4dad-90fb-5ddc9ece3344 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 22:06:35 | 2019-10-16 22:16:35 | 2019-10-16 22:21:52 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "https://bit.ly/35CsnLI"\r\n },... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 695d982e-de0b-4dad-90fb-5ddc9ece3344 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "https://bit.ly/35CsnLI"\r\n },... | [176.10.99.200] | ||||
22 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 22:48:10 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 8168f50f-5776-4f3b-99a0-0d32b8fb9ecd | 3d0e8da1-c877-48db-a36f-978828669c25 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 21:43:04 | 2019-10-16 22:43:04 | 2019-10-16 22:48:10 | {\r\n "Query": "ZScaler\r\n| where SourceIP == \"137.135.26.148\"\r\n| where Url contains \"bit... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "https://bit.ly/35CsnLI"\r\n },... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 3d0e8da1-c877-48db-a36f-978828669c25 | {\r\n "Query": "ZScaler\r\n| where SourceIP == \"137.135.26.148\"\r\n| where Url contains \"bit... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "https://bit.ly/35CsnLI"\r\n },... | [176.10.99.200] | ||||
23 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 22:33:12 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 3ef5d7e4-dae9-4a97-b51f-74ee823362d5 | 7aef8c9f-6e0f-49cd-b651-2327f9a1c801 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 22:18:05 | 2019-10-16 22:28:05 | 2019-10-16 22:33:12 | {\r\n "Query": "ZScaler\r\n| where SourceIP == \"137.135.26.148\"\r\n| where Url contains \"bit... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "https://bit.ly/35CsnLI"\r\n },... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 7aef8c9f-6e0f-49cd-b651-2327f9a1c801 | {\r\n "Query": "ZScaler\r\n| where SourceIP == \"137.135.26.148\"\r\n| where Url contains \"bit... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "https://bit.ly/35CsnLI"\r\n },... | [176.10.99.200] | ||||
26 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-09-27 08:23:08 | Activity from infrequent country | Activity from infrequent country | Medium | Megan Bowen (meganb@m365x648731.onmicrosoft.com) performed an activity. No activity was performe... | MCAS | Microsoft | B048A8BF-01C1-3C1A-9985-66191429FD36 | 4ea929d7-94f2-25b3-da0a-0247f9f7c206 | MCAS_ALERT_ANUBIS_DETECTION_NEW_COUNTRY | Unknown | NaN | False | 2019-09-27 08:18:42 | 2019-09-27 08:18:42 | 2019-09-27 08:23:07 | {\r\n "Cloud Applications": "Microsoft Azure",\r\n "Countries": "US",\r\n "IP Addresses": "50... | [\r\n {\r\n "$id": "3",\r\n "Address": "50.35.65.178",\r\n "Type": "ip"\r\n },\r\n {... | Detection | [\r\n {\r\n "Href": "https://m365x648731.portal.cloudappsecurity.com/#/policy/?id=eq(5d77739... | Microsoft Cloud App Security | SecurityAlert | 4ea929d7-94f2-25b3-da0a-0247f9f7c206 | {\r\n "Cloud Applications": "Microsoft Azure",\r\n "Countries": "US",\r\n "IP Addresses": "50... | [\r\n {\r\n "$id": "3",\r\n "Address": "50.35.65.178",\r\n "Type": "ip"\r\n },\r\n {... | [50.35.65.178] | ||||||
56 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 20:11:36 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | e4d06ca1-3bf4-4e8b-a6da-787091dc63ae | 42925d1c-236c-4175-a2a6-39643b223902 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 19:06:30 | 2019-10-16 20:06:30 | 2019-10-16 20:11:36 | {\r\n "Query": "ZScaler_CL\r\n| extend Url = Url_s\r\n| where DeviceAction_s contains \"allow\"... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 42925d1c-236c-4175-a2a6-39643b223902 | {\r\n "Query": "ZScaler_CL\r\n| extend Url = Url_s\r\n| where DeviceAction_s contains \"allow\"... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
57 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 20:19:18 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 4dcea248-aebc-4b38-a1e3-575afe5a0277 | b8eb1175-5262-434c-9de2-8b5854693c1f | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 19:14:11 | 2019-10-16 20:14:11 | 2019-10-16 20:19:18 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | b8eb1175-5262-434c-9de2-8b5854693c1f | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
58 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 20:29:17 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 52ba3b53-76b7-47f4-a8ca-707785c9315c | 944e2de6-8c77-43b8-ac1e-feb2e893d33d | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 19:24:12 | 2019-10-16 20:24:12 | 2019-10-16 20:29:17 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 944e2de6-8c77-43b8-ac1e-feb2e893d33d | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
59 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 20:49:18 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 643a03f9-4899-4a0e-90a0-4c59cf30f183 | 22db1193-9161-43db-bf1c-6c489878a1f2 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 19:44:12 | 2019-10-16 20:44:12 | 2019-10-16 20:49:18 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | 22db1193-9161-43db-bf1c-6c489878a1f2 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
61 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 20:59:20 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | c39ce083-84a2-4fc4-8805-2e74b42de9bd | a358c066-810d-4db7-a272-cc9e79b7d2f9 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 19:54:12 | 2019-10-16 20:54:12 | 2019-10-16 20:59:20 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | a358c066-810d-4db7-a272-cc9e79b7d2f9 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
63 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 20:01:35 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | 80cf53be-6612-4288-bc38-d9cf6104c950 | fdba59d6-731a-43e9-888b-97690a90a64c | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 18:56:30 | 2019-10-16 19:56:30 | 2019-10-16 20:01:35 | {\r\n "Query": "ZScaler_CL\r\n| extend Url = Url_s\r\n| where DeviceAction_s contains \"allow\"... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | fdba59d6-731a-43e9-888b-97690a90a64c | {\r\n "Query": "ZScaler_CL\r\n| extend Url = Url_s\r\n| where DeviceAction_s contains \"allow\"... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] | ||||
64 | a927809c-8142-43e1-96b3-4ad87cfe95a3 | 2019-10-16 20:39:19 | Access from a suspicious IP leading to suspicious endpoint activity | Access from a suspicious IP leading to suspicious endpoint activity | High | Access from a suspicious IP leading to suspicious endpoint activity | ASI Scheduled Alerts | Microsoft | de87dbd4-aafd-40f5-aa5a-54f3006d044e | a399a710-351b-42b0-97c9-9f0d1a6ec972 | a927809c-8142-43e1-96b3-4ad87cfe95a3_62bc82a0-1f59-49b6-82f2-266a836d072c | Unknown | NaN | False | 2019-10-16 19:34:12 | 2019-10-16 20:34:12 | 2019-10-16 20:39:19 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | Detection | 1c4b4612-7123-47db-bb74-f3b6fde75431 | RedmondSentinelDemoRG | Azure Sentinel | Scheduled Alerts | SecurityAlert | a399a710-351b-42b0-97c9-9f0d1a6ec972 | {\r\n "Query": "ZScaler\r\n| where DeviceAction contains \"allow\"\r\n| join kind=inner (\r\nHe... | [\r\n {\r\n "$id": "3",\r\n "Type": "url",\r\n "Url": "http://host.gomencom.website/Do... | [176.10.99.200] |
print('List of current DataFrames in Notebook')
print('-' * 50)
current_vars = list(locals().keys())
for var_name in current_vars:
if isinstance(locals()[var_name], pd.DataFrame) and not var_name.startswith('_'):
print(var_name)
To save the contents of a pandas DataFrame to an Excel spreadsheet use the following syntax
writer = pd.ExcelWriter('myWorksheet.xlsx')
my_data_frame.to_excel(writer,'Sheet1')
writer.save()
If you have not run this Notebook before please run this cell before running the rest of the Notebook.
import sys
import warnings
warnings.filterwarnings("ignore",category=DeprecationWarning)
MIN_REQ_PYTHON = (3,6)
if sys.version_info < MIN_REQ_PYTHON:
print('Check the Kernel->Change Kernel menu and ensure that Python 3.6')
print('or later is selected as the active kernel.')
sys.exit("Python %s.%s or later is required.\n" % MIN_REQ_PYTHON)
# Package Installs - try to avoid if they are already installed
try:
import Kqlmagic
from ipwhois import IPWhois
print('If you answer "n" this cell will exit with an error in order to avoid the pip install calls,')
print('This error can safely be ignored.')
resp = input('msticpy and Kqlmagic packages are already loaded. Do you want to re-install? (y/n)')
if resp.strip().lower() != 'y':
sys.exit('pip install aborted - you may skip this error and continue.')
else:
print('After installation has completed, restart the current kernel and run '
'the notebook again skipping this cell.')
except ImportError:
pass
print('\nPlease wait. Installing required packages. This may take a few minutes...')
!pip install msticpy --upgrade --user
!pip install ipwhois --upgrade --user
# Uncomment to refresh the maxminddb database
# !pip install maxminddb-geolite2 --upgrade
print('To ensure that the latest versions of the installed libraries '
'are used, please restart the current kernel and run '
'the notebook again skipping this cell.')
msticpyconfig.yaml
configuration File¶You can configure primary and secondary TI providers and any required parameters in the msticpyconfig.yaml
file. This is read from the current directory or you can set an environment variable (MSTICPYCONFIG
) pointing to its location.
To configure this file see the ConfigureNotebookEnvironment notebook