Reporting with PowerBI (via Custom Report .csv)

Overview

Reporting in PowerBI streamlines auditing with advanced filtering on categories such as:

• Scan Name
• Targets
• Classifications
• File Type
• Data types

Spirion reports can be accessed in PowerBI via CSV, Rest API, and Scripts, among other data sources.

This article provides a Power BI template designed to answer the questions of where and how much SPI exists. Thus, enabling data stewards and auditors a means to quickly determine which users and files can be prioritized for highest impact to risk, data accuracy, and organizational policy.

NOTE: Exporting report data from the Spirion console can expose file paths, server names, and risk metrics. Take precautions to safeguard this data.

Links

• Steps to use report template
• Required Files - Spirion-High Level-v1.0.4.pbit
  

Pertinent Reporting Categories

• Location Type: Selection based on Cloud (Email, Storage), Standard Files (File, Mdb, Compressed File), Exchange (Email, Attachments), SharePoint, NoSQL
• Scans ordered by most recent
• Targets ordered by Match count or Location count
• Classifications by Location count
• File Type by count
• Data Type by Match count
• Parent: the root of the location extracted from the Location (full string) based on the Location Type:
• • Cloud Email/Storage: User FQDN
  • Exchange: Username
  • Files (Compressed, Mdb, File): Root folder of Location
  • SharePoint: Site name
  • NoSQL: Database name
  • Web Page: site root

Screenshot: Report interface populated and filtered on Cloud Storage

How to Use Report Template

Procedure:

1. Create a Custom Report named "Spirion-BI-Report-001" with the following fields: 

Report Data Fields

• Endpoints: Target Name
• Locations: Location
• Location: File Type
• Locations: Internal location Type
• Locations: File Owner (not used)
• Locations: File Size
• Locations: Date/Time Modified
• Locations: Classifications
• Matches: Database Column Names (not used)
• Matches: Match Count
• • Aggregate: Sum
• Matches: Action
• Matches: Data Type Name
• Searches: Search Start Date/Time
• Searches: Scan Name

2. Set filters as needed to constrain the data set.
   

NOTE: Reports with many rows are processed for a compressed download.

Unless filters are applied, there will be one row for every location containing a match and every unique data type found in that Location (file path, database table, SharePoint site collection).

An estimate for duration of generation can be made using ~8500 rows/minute.

• Report Generation Time (1M rows)
  ~118 minutes (~1.96 hours)
• Estimated Uncompressed Size (1M Rows)
  ~278.94 MB
• Estimated Compressed Size (1M Rows)
  ~5.22 MB
• Example Filters:
• • Search Start Date/Time>Last X (Weeks, Months, Years)
  • Scans (select scans to include from the pick list)
  • Target (select Targets to include from the pick list)
    
    

3. Generate CSV export
4. 1. (Optional) By Scheduled export
   2. (Optional) On-demand
   3. 1. Hover the row menu icon ⋮ of the Report Name to export
      2. Click Generate Export
4. Download the zip file
5. Extract the CSV to "C:\SpirionReports"
6. Open the PowerBI template file (.pbit)
7. 1. Example: Spirion-High Level-v1.0.x.pbit
   2. Change the report path if you wish (Optional)
   3. 1. Click Home (tab)>Transform data (Queries section)
      2. Click Advanced Editor (Query section)
      3. Modify the "FilePath" parameter.
      4. Close & Apply