[FireEye]Excelerating Analysis, Part 2 — X[LOOKUP] Gon’ Pivot To Ya

[igor51]

Excelerating Analysis, Part 2 — X[LOOKUP] Gon’ Pivot To Ya

In December 2019, we published a blog post on     href="/content/fireeye-www/en_US/blog/threat-research/2019/12/tips-and-tricks-to-analyze-data-with-microsoft-excel.html">augmenting
    analysis using Microsoft Excel for various data sets for
  incident response investigations. As we described, investigations
  often include custom or proprietary log formats and miscellaneous,
  non-traditional forensic artifacts. There are, of course, a variety of
  ways to tackle this task, but Excel stands out as a reliable way to
  analyze and transform a majority of data sets we encounter.

 

In our first post, we discussed summarizing verbose artifacts using
  the CONCAT function, converting timestamps using the TIME function,
  and using the COUNTIF function for log baselining. In this post, we
  will cover two additional versatile features of Excel: LOOKUP
  functions and PivotTables.

 

For this scenario, we will use a dataset of logon events for an
  example Microsoft Office 365 (O365) instance to demonstrate how an
  analyst can enrich information in the dataset. Then we will
  demonstrate some examples of how to use PivotTables to summarize
  information and highlight anomalies in the data quickly.

 

Our data contains the following columns:

 

• Description – Event description
• User – User’s
    name
• User Principle Name – email address
• App – such
      as Office 365, Sharepoint, etc.
• Location – Country

 
• Date
• IP address
• User agent (simplified)

   
• Organization – associated with IP address (as identified by
    O365)

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture1.png" alt="" />
 
 Figure 1: O365 data set

 

LOOKUP for Data Enrichment

 

It may be useful to add more information to the data that could help
  us in analysis that isn’t provided by the original log source. A step
  FireEye Mandiant often performs during investigations is to take all
  unique IP addresses and query threat intelligence sources for each IP
  address for reputation, WHOIS information, connections to known threat
  actor activity, etc. This grants more information about each IP
  address that we can take into consideration in our analysis.

 

While FireEye Mandiant is privy to historical engagement data and     href="/content/fireeye-www/en_US/mandiant/threat-intelligence.html">Mandiant
    Threat Intelligence, if security teams or organizations do not
  have access to commercial threat intelligence feeds, there are
  numerous open source intelligence services that can be leveraged.

 

We can also use IP address geolocation services to obtain latitude
  and longitude related to each source IP address. This information may
  be useful in identifying anomalous logons based on geographical location.

 

After taking all source IP addresses, running them against threat
  intelligence feeds and geolocating them, we have the following data
  added to a second sheet called “IP Address Intel” in our Excel document:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture2.png" alt="" />
 
 Figure 2: IP address enrichment

 

We can already see before we even dive into the logs themselves that
  we have suspicious activity: The five IP addresses in the
  203.0.113.0/24 range in our data are known to be associated with
  activity connected to a fictional threat actor tracked as TMP.OGRE.

 

To enrich our original dataset, we will add three columns to our
  data to integrate the supplementary information: “Latitude,”
  “Longitude,” and “Threat Intel” (Figure 3). We can use the VLOOKUP or
  XLOOKUP functions to quickly retrieve the supplementary data and
  integrate it into our main O365 log sheet.

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture3.png" alt="" />
 
 Figure 3: Enrichment columns

 

VLOOKUP

 

The traditional way to look up particular data in another array is
  by using the     href="https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1">VLOOKUP
  function. We will use the following formula to reference the
  “Latitude” values for a given IP address:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture4.png" alt="" />
 
 Figure 4: VLOOKUP formula for Latitude

 

There are four parts to this formula:

 

1. Value to look up:
   • This dictates what cell value we are
             going to look up more information for. In this case, it is cell
             G2, which is the IP address.
2. Table
         array:
   • This defines the entire array in which we will look
             up our value and return data from. The first column in the array
             must contain the value being looked up. In the aforementioned
             example, we are searching in ‘IP Address Intel’!$A$2:$D:$15. In
             other words, we are looking in the other sheet in this workbook
             we created earlier titled “IP Address Intel”, then in that
             sheet, search in the cell range of A2 to D15.
      
                src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture5.png"
             alt="" /> Figure 5: VLOOKUP table
               array
      
            
      Note the use of the “$” to ensure these are absolute
             references and will not be updated by Excel if we copy this
             formula to other cells.
3. Column index
         number:
   • This identifies the column number from which to
             return data. The first column is considered column 1. We want to
             return the “Latitude” value for the given IP address, so in the
             aforementioned example, we tell Excel to return data from column
           2.
4. Range lookup (match type)
   • This
             part of the formula tells Excel what type of matching to perform
             on the value being looked up. Excel defaults to “Approximate”
             matching, which assumes the data is sorted and will match the
             closest value. We want to perform “Exact” matching, so we put
             “0” here (“FALSE” is also accepted).

 

With the VLOOKUP function complete for the “Latitude” data, we can
        href="https://support.office.com/en-us/article/copy-a-formula-by-dragging-the-fill-handle-in-excel-for-mac-dd928259-622b-473f-9a33-83aa1a63e218">use
    the fill handle to update this field for the rest of the data set.

 

To get the values for the “Longitude” and “Threat Intel” columns, we
  repeat the process by using a similar function and, adjusting the
  column index number to reference the appropriate columns, then use the
  fill handle to fill in the rest of the column in our O365 data sheet:

 

• For Longitude:
  • =VLOOKUP(G2,'IP Address
          Intel'!$A$2:$D$15,3,0)
• For Threat
        Intel:
  • =VLOOKUP(G2,'IP Address
          Intel'!$A$2:$D$15,4,0)

 

Bonus Option: XLOOKUP

 

The     href="https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929">XLOOKUP
  function in Excel is a more efficient way to reference the threat
  intelligence data sheet. XLOOKUP is a newer function introduced to
  Excel to replace the legacy VLOOKUP function and, at the time of
  writing this post, is only available to “O365 subscribers in the
  Monthly channel”, according to Microsoft. In this instance, we will
  also leverage Excel’s     href="https://blog-insider.office.com/2019/06/13/dynamic-arrays-and-new-functions-in-excel/">dynamic
    arrays and “spilling” to fill in this data more efficiently,
  instead of making an XLOOKUP function for each column.

 

NOTE: To utilize dynamic arrays and spilling, the data we are
  seeking to enrich cannot be in the form of a “Table” object. Instead,
  we will apply filters to the top row of our O365 data set by selecting
  the “Filter” option under “Sort & Filter” in the “Home” ribbon:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture6.png" alt="" />
 
 Figure 6: Filter option

 

To reference the threat intelligence data sheet using XLOOKUP, we
  will use the following formula:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture7.png" alt="" />
 
 Figure 7: XLOOKUP function for enrichment

 

There are three parts to this XLOOKUP formula:

 

1. Value to lookup:
   • This dictates what cell value we are
             going to look up more information for. In this case, it is cell
             G2, which is the IP address.
2. Array to look
         in:
   • This will be the array of data in which Excel will
             search for the value to look up. Excel does exact matching by
             default for XLOOKUP. In the aforementioned example, we are
             searching in ‘IP Address Intel’!$A$2:$A:$15. In other words, we
             are looking in the other sheet in this workbook titled “IP
             Address Intel”, then in that sheet, search in the cell range of
             A2 to A15:
      
                src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture8.png"
             alt="" /> Figure 8: XLOOKUP array to look
               in
      
            
      Note the use of the “$” to ensure these are absolute
             references and will not be updated by Excel if we copy this
             formula to other cells.
3. Array of data to
         return:
   • This part will be the array of data from which
             Excel will return data. In this case, Excel will return the data
             contained within the absolute range of B2 to D15 from the “IP
             Address Intel” sheet for the value that was looked up. In the
             aforementioned example formula, it will return the values in the
             row for the IP address 198.51.100.126:
      
                src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture9.png"
             alt="" /> Figure 9: Data to be returned
               from ‘IP Address Intel’ sheet
      
            
      Because this is leveraging dynamic arrays and spilling,
             all three cells of the returned data will populate, as seen in
             Figure 4.

 

Now that our dataset is completely enriched by either using VLOOKUP
  or XLOOKUP, we can start hunting for anomalous activity. As a quick
  first step, since we know at least a handful of IP addresses are
  potentially malicious, we can filter on the “Threat Intel” column for
  all rows that match “TMP.OGRE” and reveal logons with source IP
  addresses related to known threat actors. Now we have timeframes and
  suspected compromised accounts to pivot off of for additional
  hunting through other data.

 

PIVOT! PIVOT! PIVOT!

 

One of the most useful tools for highlighting anomalies by
  summarizing data, performing frequency analysis and quickly obtaining
  other statistics about a given dataset is Excel’s     href="https://support.office.com/en-us/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576">PivotTable function.

 

Location Anomalies

 

Let’s utilize a PivotTable to perform frequency analysis on the
  location from which users logged in. This type of technique may
  highlight activity where a user account logged in from a location
  which is unusual for them.

 

To create a PivotTable for our data, we can select any cell in our
  O365 data and select the entire range with Ctrl+A. Then, under the
  “Insert” tab in the ribbon, select “PivotTable”:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture10.png" alt="" />
 
 Figure 10: PivotTable selection

 

This will bring up a window, as seen in Figure 11, to confirm the
  data for which we want to make a PivotTable (Step 1 in Figure 11).
  Since we selected our O365 log data set with Ctrl+A, this should be
  automatically populated. It will also ask where we want to put the
  PivotTable (Step 2 in Figure 11). In this instance, we created another
  sheet called “PivotTable 1” to place the PivotTable:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture11.png" alt="" />
 
 Figure 11: PivotTable creation

 

Now that the PivotTable is created, we must select how we want to
  populate the PivotTable using our data. Remember, we are trying to
  determine the locations from which all users logged in. We will want a
  row for each user and a sub-row for each location the user has logged
  in from. Let’s add a count of how many times they logged in from each
  location as well. We will use the “Date” field to do this for this example:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture12.png" alt="" />
 
 Figure 12: PivotTable field definitions

 

Examining this table, we can immediately see there are two users
  with source location anomalies: Ginger Breadman and William Brody have
  a small number of logons from “FarFarAway”, which is abnormal for
  these users based on this data set.

 

We can add more data to this PivotTable to get a timeframe of this
  suspicious activity by adding two more “Date” fields to the “Values”
  area. Excel defaults to “Count” of whatever field we drop in this
  area, but we will change this to the “Minimum” and “Maximum” values by
  using the “Value Field Settings”, as seen in Figure 13.

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture13.png" alt="" />
 
 Figure 13: Adding min and max dates

 

Now we have a PivotTable that shows us anomalous locations for
  logons, as well as the timeframe in which the logons occurred, so we
  can hone our investigation. For this example, we also formatted all
  cells with timestamp values to reflect the format FireEye Mandiant
  typically uses during analysis by selecting all the appropriate cells,
  right-clicking and choosing “Format Cells”, and using a “Custom”
  format of “YYYY-MM-DD HH:MM:SS”.

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture14.png" alt="" />
 
 Figure 14: PivotTable with suspicious
    locations and timeframe

 

IP Address Anomalies

 

Geolocation anomalies may not always be valuable. However, using a
  similar configuration as the previous example, we can identify
  suspicious source IP addresses. We will add “User Principle Name” and
  “IP Address” fields as Rows, and “IP Address” as Values. Let’s also
  add the “App” field to Columns. Our field settings and resulting table
  are displayed in Figure 15:

 

      src="https://www.fireeye.com/content/dam/fireeye-www/blog/images/excelerating2/Picture15.png" alt="" />
 
 Figure 15: PivotTable with IP addresses
    and apps

 

With just a few clicks, we have a summarized table indicating which
  IP addresses each user logged in from, and which app they logged into.
  We can quickly identify two users logged in from IP addresses in the
  203.0.113.0/24 range six times, and which applications they logged
  into from each of these IP addresses.

 

While these are just a couple use cases, there are many ways to
  format and view evidence using PivotTables. We recommend trying
  PivotTables on any data set being reviewed with Excel and
  experimenting with the Rows, Columns, and Values parameters.

 

We also recommend adjusting the     href="https://support.office.com/en-us/article/pivottable-options-27c02eb7-27de-4b3f-9677-c48e3fe7637b">PivotTable
  options, which can help reformat the table itself into a format
  that might fit requirements.

 

Conclusion

 

These Excel functions are used frequently during investigations at
  FireEye Mandiant and are considered important forensic analysis
  techniques. The examples we give here are just a glimpse into the
  utility of LOOKUP functions and PivotTables. LOOKUP functions can be
  used to reference a multitude of data sources and can be applied in
  other situations during investigations such as tracking remediation
  and analysis efforts.

 

PivotTables may be used in a variety of ways as well, depending on
  what data is available, and what sort of information is being analyzed
  to identify suspicious activity. Employing these techniques, alongside
  the ones we highlighted previously, on a consistent basis will go a
  long way in "excelerating" forensic analysis skills and efficiency.

Source: Excelerating Analysis, Part 2 — X[LOOKUP] Gon’ Pivot To Ya