MSTICPy 1.0.0 Released!

  1. Implementing pip “extras” to split dependencies into optional chunks for faster installation
  2. Settings editor and management tools for easier configuration
  3. User defaults — preload data providers and other components automatically when you initialize MSTICPy at the start of each notebook
  4. SQL to KQL translator for cross compatibility
  5. Pivot functions updates
  6. Improvements to notebook initialization


pip install msticpy

MSTICPy Dependencies and “extras”

  • Common packages such as pandas, numpy and bokeh
  • Security/network-specific packages like tldextract, dnspython and ipwhois.
  • Kqlmagic —the core client library for Azure Sentinel queries.
  • More specialized packages used by only one or two MSTICPy components: scipy, statsmodel, scikit-learn, virustotal, splunk.
pip install msticpy[ml]
MSTICPy exception when using a component with a missing dependency. It tells you which “extra” to use with “pip install msticpy” so that the dependency is installed.
Example of an exception when trying to use a component with a missing dependency

Settings editor and settings management

MSTICPy settings editor showing configuration for a threat intelligence provider.
Adding settings for VirusTotal (btw that isn’t a real VirusTotal key!)
# set an environment variable pointing to your existing config file
# %env MSTICPYCONFIG=./msticpyconfig2.yaml
mpedit = MpConfigEdit()
MpConfig file utility lets you manipulate settings files. Buttons show several functions including Load file, save file, convert, validate, show Key Vault secrets and others.
MpConfigFile in interactive mode

User Defaults

Settings editor showing adding the Splunk data connector to the auto-load list.
The query providers auto-load editor
>>> msticpy.current_providers{'qry_azsent': < at 0x21604110ac8>,
'qry_myworkspace': < at 0x216041459c8>,
'qry_cybersoc': < at 0x21660d41308>,
'qry_splunk': < at 0x21661127208>,
'qry_local': < at 0x216605a7c48>,
'ti_lookup': <msticpy.sectools.tilookup.TILookup at 0x216611c7908>,
'geoip': <msticpy.sectools.geoip.GeoLiteLookup at 0x21660659c88>,
'pivot': <msticpy.datamodel.pivot.Pivot at 0x216602d8e88>,
'az_data': < at 0x21668aaf708>,
'azs_api': < at 0x21603f42388>,
'nb': <module 'msticnb' from 'e:\\src\\msticnb\\msticnb\\'>}

SQL to KQL Translator

  • Where and Select
  • Joins and Unions
  • Sub-queries
  • Grouping
sql = """
SELECT Message
FROM apt29Host a
SELECT ProcessGuid
FROM apt29Host
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(ParentImage) RLIKE '.*partial_string.*'
AND LOWER(Image) LIKE '%cmd.exe'
) b
ON a.ParentProcessGuid = b.ProcessGuid
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(Image) LIKE '%powershell.exe'
table_map = {"apt29Host": "SecurityEvent", "MyTable": "SigninLogs"}
kql = sql_to_kql(sql, table_map)
| join kind=inner (SecurityEvent
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(ParentImage) matches regex '.*partial.string.*'
and tolower(Image) endswith 'cmd.exe'
| project ProcessGuid) on $left.ParentProcessGuid == $right.ProcessGuid
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(Image) endswith 'powershell.exe'
| project Message

Pivot Updates

  • Pivot browser — that shows you entities and their pivot functions along with the help documentation for each function. You can also search for pivot functions by keyword of anything in the name or docstring of a function.
Pivot function browser showing a list of entities. The Account entity is selected and shows a list of all pivot functions for that entity. Below these lists is a help drop down showing the parameters and usage of the highlighted function.
Pivot browser
  • Persistent pipelines — we’ve extended this feature to allow you to save multi-step pandas pipelines as yaml files and re-execute them with different input DataFrames. We’ve also added a couple of useful pandas accessor methods that you can use in your pipelines. As a reminder, an example of a pandas pipeline using pivot functions is shown below, along with the output.
# Lookup IPs at VT, column="IPAddress")
# Filter on high severity
.query("Severity == 'high'")
# lookup whois info for IPs, column="Ioc", join="left")
# display sample of intermediate results
.mp_pivot.display(title="TI High Severity IPs", cols=["Ioc", "Provider", "Reference"], head=5)
# Query IPs that have login attempts, ip_address_list="Ioc")
# Send the output of this to a plot
title="High Severity IPs with Logon attempts",
source_columns=["UserPrincipalName", "IPAddress", "ResultType", "ClientAppUsed", "UserAgent", "Location"],
Execution results of a pandas pivot pipeline
description: Pipeline 1 description
- name: get_logons
step_type: pivot
function: util.whois
entity: IpAddress
comment: Standard pivot function
column: IpAddress
join: inner
- name: disp_logons
step_type: pivot_display
comment: Pivot display
title: "The title"
- Computer
- Account
query: Computer.str.startswith('MSTICAlerts')
head: 10
- name: tee_logons
step_type: pivot_tee
comment: Pivot tee
var_name: var_df
clobber: True
  • Shortened, friendlier names for many functions
  • Shortcuts — a subset of more popular functions are available as direct methods of the entities (rather than in a subcontainer)
  • Entity instances as input — for “shortcut” methods, these also work as instance methods. This means that if you have an initialized IpAddress object with the “Address” attribute populated, you can run pivot functions from this entity. Rather than supplying the address as a parameter, the pivot will take the Address value of the entity as the input to the function.
  • Several new pandas “accessor” extensions allowing you to do simple row and column filtering, sort data, expand lists and json values to rows and columns
  • All pivot functions will support “join” functionality.

Notebook Startup and Initialization

  • Defaulting to using the cool native data browser in Azure Machine Learning notebooks
  • Tidying up and minimizing output so that warnings are clearer and
    help URLs are displayed clearly
  • Enabling single sign-on for Azure Sentinel notebooks using Azure CLI (all notebooks in a session will use the Azure CLI authenticated session to obtain tokens for your Azure Sentinel workspaces). To use this just run az login from an AzureML compute terminal or just run !az login from a notebook cell.
  • Plus a bunch of other useability fixes and improvements.





This is the account of the Microsoft Threat Intelligence Center (MSTIC).

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Treasury V1 — Development Update

Introduction to Amazon S3

JSON Parser in Salesforce Flow

Kafka Multi-Tenancy Architecture: SSL client authentication

On the delivery of AI to Business

So, most of pepole from networking field don’t know what route table stands for .

CentOS 8 and libvirt-daemon iptables rules

I am a recruiter and I love recommender systems

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store


This is the account of the Microsoft Threat Intelligence Center (MSTIC).

More from Medium

Writing a Data Provider for MSTICPy

Writing a shell in python

Scrape Reddit post using Python

What does a Spacecraft Operations Engineer do? Smiti Dhami’s journey