Security Delivery Tracker

Purpose

This tool was developed for Security Leaders, Managers and/or Practitioners to address the ongoing challenge in tracking and reporting key security metrics within their organization.  

Some of these metrics include:

• Product Coverage
• Maturity
• Return on Investment
• Administrative Overhead
• License Usage
• Regulatory Drivers
• Trending Patterns
• Monitor Failures
• Identify Wasteful Areas

---

Requirements

• SDT.ZIP located on my Github https://github.com/njfanelli/Security-Delivery-Tracker
• Microsoft Excel
• Microsoft PowerBi - https://powerbi.microsoft.com/en-us/downloads/
• (Optional) Ability to create a scheduled task to run a VBScript.  
• (Optional) Microsoft PowerQuery - https://www.microsoft.com/en-us/download/details.aspx?id=39379

---

Getting Started

1. Download the SDT.ZIP above, this compressed file contains the following files:
   • Security Delivery Tracker.xlsx - This is main file for the solution and is used as a central repository to track changes (i.e. maturity, budget, issues, failures, etc.) to products under your portfolio.
   • Security Delivery Legend.xlsx - This file is only used as a reference and does not link to any of the other files within this package.
   • Security Delivery Archive Script.vbs - This script when executed will create a backup of the “Security Delivery Tracker.xlsx” in to .\Archive directory, the filename will reflect the date at which the script was executed, if you are not comfortable with the script this process could be done manually.  NOTE: it is important you do not rename the filenames of the archives there is code with in the “Security Delivery Trend Data.xlsx” document that will take each filename and normalize it to an actual date.
   • “Archives” Folder - Depending on the frequency you define in running the script above will determine the checkpoint intervals of your reporting data.  The files within this folder should following the naming format of “YYYY-MM-DD”, if leveraging the script above the file will be named appropriately if you are saving an archive manually, you’ll need to keep this in mind when saving your file (this is explain in more detail below).  NOTE: there is one pre-existing entry within the Archives folder, this file is empty but it necessary to make sure the PowerQuery steps execute within the “Security Delivery Trend Data.xlsx” file. 
   • Security Delivery Trend Data.xlsx - This file (initially blank) has a preconfigured PowerQuery that will browse to your Archives folder, it will then open each file that is stored within the repository then copy/paste them into this worksheet thus creating a single table with your rolling updates.  To execute the PowerQuery steps you may go to either the Data Tab and select “Refresh All” within the ribbon bar OR go to the Query Tab and select “Refresh” within the ribbon bar.  NOTE:   COLUMN A is now created, the query takes the filename/s within the source path and normalizes them into an actual date that can be used for reporting purposes.  Therefore, is it important to follow the filename format for archived files.  If you are interested in the steps the query performs you may go to the Query Tab and click on “Edit” within the ribbon bar, a new window will appear then within the right window pane all steps will be listed under “Applied Steps”, you have the ability to select each step from top to bottom to watch the data transform.
   • Security Delivery Maturity Levels.xlsx - This workbook is very simple where it will convert the Maturity Level to an actual Maturity Number value.  This file is only referenced the by the Security Delivery Dashboard.pbix file.
   • Security Delivery Dashboard.pbix - Contains a set of dashboards that are populated by reading data from the Security Delivery Trend Data.xlsx workbook.  This file can be opened with Microsoft PowerBI (a free version is available) and the dashboards available are:
     • Solution Summary - This dashboard provides a snapshot of all technologies identified within your environment and summarizes their current status.  
     • Solution Breakdown - This dashboard allows the user to select and individual technology to view additional details as well as trending patterns for the key metrics we’ve identified within our workbook.
   • “EXAMPLE” Folder - This folder contains a pre-populated workbook and dashboard with fictitious data for you to reference. You may safely remove this folder if you wish.
2. Extract the compress file to the following path: “C:\SDT”.  This is the default patch for the tool and can be changed if needed, refer to the FAQs section for more information on this.
3. Launch Security Delivery Tracker.xlsx
4. Under MASTER worksheet update Product names listed in COLUMN A to reflect your environment.  NOTE: You’ll also need to update the respective worksheet tab to reflect the new product name (e.g. if cell A4 is Infinity Gauntlet so should the worksheet tab labeled Solution 1).  After you’ve modified COLUMN A and the respected worksheet tab you’ll need to update several calculated cells for each solution.  In the MASTER worksheet tab for any product you changed in COLUMN A you will need to update the calculated value identified in COLUMNS E, F, G, H, I, J & L for each product (for instance:  If cell A4 = Infinity Gauntlet and Solution 1 worksheet tab was renamed to Infinity Gauntlet you need to modify the calculated formula in E4, F4, G4, H4, I4, J4 & L4), an easy way to accomplish this is:
   • On the MASTER worksheet tab highlight the entire row you’d like to change (e.g. row 4 but you’ll need to repeat this step for each solution)
   • In the ribbon bar select Find & Select
   • In the window that appears click the Replace tab
     • Find what: Solution 1
     • Replace with: <YOUR PRODUCT>  note this must match the worksheet tab name
     • Within: Sheet
     • Search: By Rows
     • Look in: Formulas
     • Click Find All button
     • Verify the items found and if OK click the Replace All button
     • Click Close
5. Go to the Dropdown Data worksheet tab
   • Update any cell as it relates to your environment for COLUMNS B-O.  Common items to edit are Solution Focus, Team, Analyst & VAR.  These items control many of the drop-down lists that appear throughout the workbook.
6. The Solution worksheet tab/s (you’ll need to do this for each solution)
   • Update the Header to reflect the worksheet tab name
   • Review/Modify the Maturity Description labels specified for each solution and ensure they are applicable for that product, if not feel free to modify.
7. OPTIONAL - Excel has the ability to Share Workbook, this is located under the Review Tab (note: in the newer version of Office this is now identified as a Legacy feature but is still accessible).  When you click on the “Share Workbook” feature a popup will appear, under the Editing tab select the “Use the old shared workbooks feature instead of the new co-authoring experience.” and then click OK
8. OPTIONAL - Excel has the ability to Track Changes this is located under the Review Tab (note: in the newer version of Office this is now identified as a Legacy feature but is still accessible).  Once you click on the “Track Changes” feature a popup will appear, make sure “Track changes while editing. This also shares your workbook.” is selected.  As changes are made moving forward return to this popup and I found the specifying the following criteria under the “Highlight which changes” section helpful…   When: = ALL, also check box to “List changes on a new sheet”.  This will output a rolling list of changes onto a single worksheet for you AND if you go to any of your solution tabs within the Security Delivery Tracker you’ll also notice any field that was modified is now highlighted/outlined indicating a change to the field, if you hover over the field you’ll see details on the change.
9. Save Workbook changes
10. Start Entering Data!!

---

Reporting

Provided below are several screenshots of the dashboard metrics:

 Solution Summary Example

 Solution Breakdown Example

---

Frequently Asked Questions 

QUESTION:  How do I change the default path of the tool from “C:\SDT” to something else?

ANSWER:  Once the files have been extracted and saved in the desired path, you’ll need to open each of the following documents and update the dependent links/connections (Note: this is where Power Query for Excel comes in):

• Browse and launch the “Security Delivery Trend Data.xlsx”
  1. Click Query Tab
  2. Click Edit in the ribbon bar
  3. Click OK to the security notice (if prompted)
  4. In the right window pane labeled Query Setting under Applied Steps “Source” is listed as the first step, click the cog wheel to the right of it
  5. Under folder path enter the new location for your “Archives” folder
  6. Click OK
  7. Click Close & Load in the ribbon bar
  8. Save and Close the file
• Right click the “Security Delivery Archive Script.vbs” file and select Edit (if prompted with a security warning click Open)
  1. Row 8 update the strSource to the new location to your Security Delivery Tracker.xlsx file
  2. Row 11 update the strDest to the new location of your Archives folder
  3. Save and Close the file
• Launch the “Security Delivery Tracker Dashboard.pbix” file
  1. Under the Home Tab click the “Edit Queries” option within the ribbon bar then select “Data Source Settings”
  2. Highlight “c:\sdt\security delivery maturity levels.xlsx” and then select “Change Source…”
  3. Under file path update the location to your respective file
  4. Click OK
  5. Highlight “c:\sdt\security delivery trend data.xlsx” and then select “Change Source…”
  6. Under file path update the location to your respective file
  7. Click OK
  8. Click Close
  9. Save and close file

QUESTION:  I would like the Security Delivery Tracker.xlsx file to be accessed modified by multiple users at one time, how do I do this?

ANSWER:  Excel has the ability to Share Workbook, this is located under the Review Tab (note: in the newer version of Office this is now identified as a Legacy feature but is still accessible).  When you click on the “Share Workbook” feature a popup will appear, under the Editing tab select the “Use the old shared workbooks feature instead of the new co-authoring experience.” and then click OK

QUESTION:  Is there a way to track changes to the Security Delivery Tracker.xlsx workbook so I have a rolling record of what changed and by whom?

ANSWER:  Absolutely.  Excel has the ability to Track Changes this is located under the Review Tab (note: in the newer version of Office this is now identified as a Legacy feature but is still accessible).  Once you click on the “Track Changes” feature a popup will appear, make sure “Track changes while editing. This also shares your workbook.” is selected.  As changes are made moving forward return to this popup and I found the specifying the following criteria under the “Highlight which changes” section helpful…  When: = ALL, also check box to “List changes on a new sheet”.  This will output a rolling list of changes onto a single worksheet for you AND if you go to any of your solution tabs within the Security Delivery Tracker you’ll also notice any field that was modified is now highlighted/outlined indicating a change to the field, if you hover over the field you’ll see details on the change.

QUESTION:  The dashboards within my Security Delivery Tracker Dashboard.pbix file are not displaying data?

ANSWER:  Follow these steps to troubleshoot:  1) Ensure there is data present within your Security Delivery Trend Data.xlsx workbook.  2) If you changed the default path for the SDT tool please confirm the data sources you’re pointing to within the Security Delivery Tracker Dashboard.pbix file are accurate, you can do this by selecting the Home Tab then within the ribbon bar selecting “Edit Queries” followed by clicking “Data source setting”.