MSTICPy Pivot Functions¶

We recently released a new version of MSTICPy with a feature called Pivot functions. You must have msticpy installed to run this notebook:

!pip install --upgrade msticpy

MSTICpy versions >= 1.0.0

This feature has three main goals:

• Making it easy to discover and invoke MSTICPy functionality
• Creating a standardized way to call pivotable functions
• Letting you assemble multiple functions into re-usable pipelines.

Here are a couple of examples showing calling different kinds of enrichment functions from the IpAddress entity:

>>> from msticpy.datamodel.entities import IpAddress, Host
    >>> IpAddress.util.ip_type(ip_str="157.53.1.1"))
    ip          result
    157.53.1.1  Public

    >>> IpAddress.util.whois("157.53.1.1"))
    asn  asn_cidr  asn_country_code  asn_date    asn_description  asn_registry  nets .....
    NA   NA        US                2015-04-01  NA               arin          [{'cidr': '157.53.0.0/16'...

    >>> IpAddress.util.geoloc_mm(value="157.53.1.1"))
    CountryCode  CountryName    State   City   Longitude   Latitude   Asn...
    US           United States  None    None   -97.822     37.751     None...

This second example shows a pivot function that does a data query for host logon events from a Host entity.

>>> Host.AzureSentinel.list_host_logons(host_name="VictimPc")
    Account               EventID   TimeGenerated                      Computer                 SubjectUserName   SubjectDomainName
    NT AUTHORITY\SYSTEM   4624      2020-10-01 22:39:36.987000+00:00   VictimPc.Contoso.Azure   VictimPc$         CONTOSO
    NT AUTHORITY\SYSTEM   4624      2020-10-01 22:39:37.220000+00:00   VictimPc.Contoso.Azure   VictimPc$         CONTOSO
    NT AUTHORITY\SYSTEM   4624      2020-10-01 22:39:42.603000+00:00   VictimPc.Contoso.Azure   VictimPc$         CONTOSO

The pivot functionality exposes operations relevant to a particular entity as methods (or functions) of that entity. These operations include:

• Data queries
• Threat intelligence lookups
• Other data lookups such as geo-location or domain resolution
• and other local functionality

You can also add other functions from 3rd party Python packages or ones you write yourself as pivot functions.

Terminology¶

Before we get into things let's clear up a few terms.

Entities¶

These are Python classes that represent real-world objects commonly encountered in CyberSec investigations and hunting. E.g. Host, URL, IP Address, Account, etc.

Pivoting¶

This comes from the common practice in CyberSec investigations of navigating from one suspect entity to another. E.g. you might start with an alert identifying a potentially malicious IP Address, from there you 'pivot' to see which hosts or accounts were communicating with that address. From there you might pivot again to look at processes running on the host or Office activity for the account.

Background Reading¶

This article is available in Notebook form so that you can try out the examples. [TODO]

There is also full documenation of the Pivot functionality on our ReadtheDocs page

Life before pivot functions¶

Before Pivot functions your ability to use the various bits of functionality in MSTICPy was always bounded by you knowledge of where a certain function was (or your enthusiasm for reading the docs).

For example, suppose you had an IP address that you wanted to do some simple enrichment on.

First you'd need to locate and import the functions. There might also be (as in the GeoIPLiteLookup class) some initialization step you'd need to do before using the functionality.

Next you might have to check the help for each function to work it parameters.

Then finally run the functions

At which point you'd discover that the output from each function was somewhat raw and it would take a bit more work if you wanted to combine it in any way (say in a single table).

We'll see how pivot functions address these problems in the remainder of the notebook.

Getting Started with Pivot functions¶

Typically we use MSTICPy's init_notebook function that handles checking versions and importing some commonly-used packages and modules (both MSTICPy and 3rd party packages like pandas

There are some preliminary steps needed before you can use pivot functions. The main one is loading the Pivot class. Pivot functions are added to the entities dynamically. The Pivot class will try to discover relevant functions from queries, Threat Intel providers and various utility functions.

In some cases, notably data queries, the data query functions are themselves created dynamically, so these need to be loaded before you create the Pivot class. (You can always create a new instance of this class, which forces re-discovery, so don't worry if mess up the order of things).

Note in most cases we don't need to connect/authenticate to a data provider prior to loading Pivot

Let's load our data query provider for AzureSentinel

Now we can load and instantiate the Pivot class.

Why do we need to pass namespace=globals()? Pivot searches through the current objects defined in the Python/notebook namespace. This is most relevant for QueryProviders. In most other cases (like GeoIP and ThreatIntel providers, it will create new ones if it can't find existing ones).

Easy discovery of functionality¶

Find the entity name you need¶

The simplest way to do this is simply enumerate (dir) the contents of the MSTPCPy entities sub-package. This should have already been imported by the init_notebook function that we ran earlier.

The items at the beginning of the list with proper capitalization are the entities.

We're going to make this a little easier in a forthcoming update with this helper function.

Warning: post-0.9.0 functionality

This will throw and error in v0.9.0 of MSTICPy

Listing pivot functions available for an entity¶

Note you can always address an entity using its qualified path, e.g. entities.IpAddress but if you are going to use one or two entities a lot it will save a bit of typing if you import them explicitly.

Once you have the entity you can use the get_pivot_list() function to see which pivot functions are available for it.

Some of the function names are a little unweildy but, in many cases, this is necessary to avoid name collisions. You might notice from the list that the functions are grouped into containers "AzureSentinel", "ti" and "util" in the above example.

Although this makes the function name even longer we thought that this helped to keep related functionality together - so you don't get a TI lookup, when you thought you were running a query.

Fortunately Jupyter notebooks/IPython support tab completion so you should not normally have to remember these names.

The containers ("AzureSentinel", "util", etc.) are also callable functions - they just return the list of functions they contain.

Now we're ready to run any of the functions for this entity

Notice that we didn't need to worry about either the parameter name or format (more on this in the next section). Also, whatever the function, the output is always returned as a pandas DataFrame.

For Data query functions you do need to worry about the parameter name¶

Data query functions are a little more complex than most other functions and specifically often support many parameters. Rather than try to guess which parameter you meant, we require you to be explicit.

To use a data query, we need to authenticate to the provider.

If you are not sure of the parameters required by the query you can use the built-in help

We also have a preview of a notebook tool that lets you browser around entities and their pivot functions, search for a function by keyword and view the help for that function. This is going to be released shortly.

Warning: post-0.9.0 functionality

This will throw and error in v0.9.0 of MSTICPy

Standardized way of calling Pivot functions¶

Due to various factors (historical, underlying data, developer laziness and forgetfullness, etc.) the functionality in MSTICPy can be inconsistent in the way it uses input parameters.

Also, many functions will only accept inputs as a single value, or a list or a DataFrame or some unpredictable combination of these.

Pivot functions allow you to largely forget about this - you can use the same function whether you have:

• a single value
• a list (or any iterable) of values
• a DataFrame with the input value in one of the columns.

Let's take an example.

Suppose we have a set of IP addresses pasted from somewhere that we want to use as input.

We need to convert this into a Python data object of some sort. To do this we can use another Pivot utility %%txt2df. This is a Jupyter/IPython magic function so you can just paste you data in a cell. Use %%txt2df --help in an empty cell to see the full syntax.

The example below we specify a comma separator, that the data has a headers row and to save the converted data as a DataFrame named "ip_df".

Warning this will overwrite any existing variable of this

name

For our example we'll also create a standard Python list from the ip column.

How did this work before?¶

If you recall the earlier example of get_ip_type, passing it a list or DataFrame doesn't result in anything useful.

Pivot versions are (somewhat) agnostic to input data format¶

However, the pivotized version can accept and correctly process a list

In the case of a DataFrame, things are a little more complicated - we have to tell the function the name of the column that contains the input data.

Note: for most functions you can ignore the parameter

name and just specify it as a positional parameter. You can also use the original parameter name of the underlying function or the placeholder name "value".

The following are all equivalent:

IpAddress.util.ip_type(ip_list)
IpAddress.util.ip_type(ip_str=ip_list)
IpAddress.util.ip_type(value=ip_list)
IpAddress.util.ip_type(data=ip_list)

When passing both a DataFrame and column name use:

IpAddress.util.ip_type(data=ip_df, column="col_name")

You can also pass an entity instance of an entity as a input parameter. The pivot code knows which attribute or attributes of an entity will provider the input value.

Iterable/DataFrame inputs and single-value functions¶

Many of the underlying functions only accept single values as inputs. Examples of these are the data query functions - typically they expect a single host name, IP address, etc.

Pivot knows about the type of parameters that the function accepts. It will adjust the input to match the expectations of the underlying function. If a list or DataFrame is passed as input to a single-value function Pivot will split the input and call the function once for each value. It then combines the output into a single DataFrame before returning the results.

You can read a bit more about how this is done in the Appendix TODO

Data queries - where does the time range come from?¶

The Pivot class has a buit-in time range. This is used by default for all queries. Don't worry - you can change it easily

You can edit the time range interactively

Or by setting the timespan property directly

In an upcoming release there is also a convenience function for setting the time directly with Python datetimes or date strings

Warning:

post-0.9.0 functionality
This will throw and error in v0.9.0 of MSTICPy

You can also override the built-in time settings by specifying start and end as parameters.

Supplying extra parameters¶

The Pivot layer will pass any unused keyword parameters to the underlying function. This does not usually apply to positional parameters - if you want parameters to get to the function, you have to name them explicitly. In this example the add_query_items parameter is passed to the underlying query function

Pivot Pipelines¶

Because all pivot functions accept DataFrames as input and produce DataFrames as output, it means that it is possible to chain pivot functions into a pipeline.

Joining input to output¶

You can join the input to the output. This usually only makes sense when the input is a DataFrame. It lets you keep the previously accumumated results and tag on the additional columns produced by the pivot function you are calling.

The join parameter supports "inner", "left", "right" and "outer" joins (be careful with the latter though!) See pivot joins documentation

Although joining is useful in pipelines you can use it on any function whether in a pipeline or not.

Pipelines¶

Pivot pipelines are implemented pandas customr accessors. Read more about Extending pandas here

When you load Pivot it adds the mp_pivot accessor. This appears as an attribute to DataFrames.

>>> ips_df.mp_pivot
<msticpy.datamodel.pivot_pd_accessor.PivotAccessor at 0x275754e2208>

The main pipelining function run is a method of mp_pivot. run requires two parameters - the pivot function to run and the column to use as input. See mp_pivot.run documentation

Here is an example of using it to call 4 pivot functions, each using the output of the previous function as input and using the join parameter to accumulate the results from each stage.

Let's step through it line by line.

1. The whole thing is surrounded by a pair of parentheses - this is just to let us split the whole expression over multiple lines without Python complaining.
2. Next we have ips_df - this is just the starting DataFrame, our input data.
3. Next we call the mp_pivot.run() accessor method on this dataframe. We pass it the pivot function that we want to run and the input column name. This column name is the column in ips_df where our input IP addresses are. We've also specified an join type of inner. In this case the join type doesn't really matter since we know we get exactly one output row for every input row.
4. We're using the pandas query function to filter out unwanted entries from the previous stage. In this case we only want Public IP addresses. This illustrates that you can intersperse standard pandas functions in the same pipeline. We could have also added a column selector expression ([["col1", "col2"...]]) if we wanted to filter the columns passed to the next stage
5. We are calling a further pivot function - whois. Remember the "column" parameter always refers to the input column, i.e. the column from previous stage that we want to use in this stage.
6. We are calling geoloc_mm to get geo location details joining with a left join - this preserves the input data rows and adds null columns in any cases where the pivot function returned no result.
7. Is the same as 6 except is a data query to see if we have any alerts that contain these IP addresses. Remember, in the case of data queries we have to name the specific query parameter that we want the input to go to. In this case, each row value in the "ip" column from the previous stage will be sent to the query.
8. Finally we close the parentheses to form a valid Python expression. The whole expression returns a DataFrame so we can add further pandas operations here (like .head(5) shown here).

Other pipeline functions¶

In addition to run, the mp_pivot accessor also has the following functions:

• display - this simply displays the data at the point called in the pipeline. You can add an optional title, filtering and the number or rows to display
• tee - this forks a copy of the dataframe at the point it is called in the pipeline. It will assign the forked copy to the name given in the var_name parameter. If there is an existing variable of the same name it will not overwrite it unless you add the clobber=True parameter.

In both cases the pipelined data is passed through unchanged. See Pivot functions help for more details.

Use of these is shown below

    ...
    .mp_pivot.run(entities.IpAddress.util.geoloc_mm, column="ip", join="left")
    .mp_pivot.display(title="Geo Lookup", cols=["IP", "City"])  # << display an intermediate result
    .mp_pivot.tee(var_name="geoip_df", clobber=True)  # << save a copy called 'geoip_df'
    .mp_pivot.run(entities.IpAddress.AzureSentinel.SecurityAlert_list_alerts_for_ip, source_ip_list="ip", join="left")

In the next release we've also implemented:

• tee_exec - this executes a function on a forked copy of the DataFrame The function must be a pandas function or custom accessor. A good example of the use of this might be creating a plot or summary table to display partway through the pipeline.

Extending Pivot - adding your own (or someone else's) functions¶

You can add pivot functions of your own. You need to supply:

• the function
• some metadata that describes where the function can be found and how the function works

Full details of this are described here.

The published version of Pivot doesn't let you add functions defined inline (i.e. in the notebook itself) but this will be possible in the next release.

Assume that we've created this function in a Python module my_module.py

Create a definition file

In the next release, this will be available as a simple function that can be used to add a function defined in the notebook.

Warning: post-0.9.0 functionality

This will throw and error in v0.9.0 of MSTICPy

Conclusion¶

We've taken a short tour through the MSTICPy looking at how they make the functionality in the package easier to discover and use. I'm particularly excited about the pipeline functionality. In the next release we're going to make it possible to define reusable pipelines in configuration files and execute them with a single function call. This should help streamline some common patterns in notebooks for Cyber hunting and investigation.

Please send any feedback or suggestions for improvements to msticpy@microsoft.com or create an issue on https://github.com/microsoft/msticpy.

Happy hunting!

Get some input data¶

Pivot functions that we want to execute¶

entities.IpAddress.util.ip_type
entities.IpAddress.util.whois
entities.IpAddress.util.geoloc_mm
entities.IpAddress.AzureSentinel.SecurityAlert_list_related_alerts

We could do this¶

df = entities.IpAddress.util.ip_type(data=ips, column="IP", join="inner")
df2 = entities.IpAddress.util.whois(data=df, column="IP", join="inner")
df3 = entities.IpAddress.util.geoloc_mm(data=df2, column="IP", join="inner")
df3 = entities.IpAddress.AzureSentinel.SecurityAlert_list_related_alerts(data=df3, column="IP", join="inner")

.... but there's a better way¶

Simple pipeline¶

Note:

• inline query to filter to only "Public" IPs
• mp_pivot.display function to display intermediate results

Inline filtering and tee function¶

Save intermediate results to a DataFrame

Add a display function¶

Post-0.9.0 feature

Saving and re-using pipelines as yaml¶

You can specify a pipeline using YAML syntax and execute directly with a DataFrame input.

Here is an example pipeline file with two pipelines, each with multiple steps.

pipelines:
  pipeline1:
    description: Pipeline 1 description
    steps:
      - name: get_logons
        step_type: pivot
        function: util.whois
        entity: IpAddress
        comment: Standard pivot function
        params:
          column: IpAddress
          join: inner
      - name: disp_logons
        step_type: pivot_display
        comment: Pivot display
        params:
          title: "The title"
          cols:
              - Computer
              - Account
          query: Computer.str.startswith('MSTICAlerts')
          head: 10
      - name: tee_logons
        step_type: pivot_tee
        comment: Pivot tee
        params:
          var_name: var_df
          clobber: True
      - name: tee_logons_disp
        step_type: pivot_tee_exec
        comment: Pivot tee_exec with mp_timeline.plot
        function: mp_timeline.plot
        params:
          source_columns:
              - Computer
              - Account
      - name: logons_timeline
        step_type: pd_accessor
        comment: Standard accessor with mp_timeline.plot
        function: mp_timeline.plot
        params:
          source_columns:
              - Computer
              - Account
  pipeline2:
    description: Pipeline 2 description
    steps:
      - name: get_logons
        step_type: pivot
        function: util.whois
        entity: IpAddress
        comment: Standard pivot function
        params:
          column: IpAddress
          join: inner
      - name: disp_logons
        step_type: pivot_display
        comment: Pivot display
        params:
          title: "The title"
          cols:
              - Computer
              - Account
          query: Computer.str.startswith('MSTICAlerts')
          head: 10
      - name: tee_logons
        step_type: pivot_tee
        comment: Pivot tee
        params:
          var_name: var_df
          clobber: True

Create a sample YAML pipeline¶

Qe can store this in a file:

with open("pipelines.yml", "w") as fh:
    fh.write(pipelines_yml)

Load the pipeline and print out what it would look like in code¶

Run the pipeline¶

Adding your own pivot functions¶

A simple example¶

A more realistic example.¶

This function extracts individual elements from a list column into separate rows. In this case the nets column.