Security awareness

How to ‘Slice and Dice’ a Security Awareness Report from SecurityIQ

June 30, 2016 by Jonathan Lampe

You may already know that SecurityIQ reports can be displayed in your browser or downloaded as CSVs.  You may also already know that SecurityIQ learners can belong to multiple groups.  However, did you know that you can combine these capabilities to perform powerful “slice and dice” operations on your SecurityIQ data?

Putting Learners in Multiple Groups

The CSV import function on the “Learners” page in SecurityIQ allows you to add learners to groups in two ways: either adding a list of learners to a specific selected group, or simply reading the name of a single group off the fourth column in an import CSV.  What many people do not know is that the “update” function on these pages allows them to add learners to two or more groups by uploading lists of the same learners multiple times.  For example, if I wanted to have my learners in separate “Department”, “Country” and “Region” groups, I could put them into three separate CSV files with those values in the fourth column, and then perform three CSV imports to get all learners into three groups.

Note that SecurityIQ doesn’t distinguish between types of groups, so many customers prepend the type of group to the group name.  For example, instead of “Argentina”, “Brazil” and “Cameroon” groups, I would expect “Country-Argentina”, “Country-Brazil” and “Country-Cameroon” groups.

Pulling CSV Reports

SecurityIQ will automatically send you CSV reports once a week, but there’s a much faster way to get your data.  The following instructions will set up your report and convert it to an Excel spreadsheet so we are ready to “slice and dice” it.

1) Set up an “AwareEd Campaign Status Report” that includes the selected campaign (or all campaigns), ALL status values, and optional columns: Start/End Date, Full Name and Groups


2) Kick off an AwareEd campaign and wait until a few learners have started their training

3) Go to “Reports” and run the report (using the “play” button), then click the “Generate CSV” button to download it as CSV


4) Open the CSV in Excel.  Notice that all the Groups are in a single column, but separated by commas.  Resave the file as an Excel workbook (*.xlsx).


‘Slicing and Dicing’ a SecurityIQ Report

Now that we have a CSV report, we need to do some reformatting to prepare it for pivot table analysis.

1) Move the “Groups” column to be the last column.  We need to to this because “expanding the commas” will overwrite any columns to the right.


Reselect the “Groups” column and then go to the “Data” tab on top.  Click the “Text to Columns” button in the “Data Tools” section.


2) In Step 1 of 3 in the “Convert Text to Columns Wizard”, select “Delimited” and then click “Next >”


3) In Step 2 of 3 in the “Convert Text to Columns Wizard”, select “Comma” as the only Delimiter.  Make sure the groups are broken up in the “Data Preview” screen below.  Finally, click “Next >”


4) In Step 3 of 3 in the “Convert Text to Columns Wizard”, just click “Finish” (leaving all columns as “General” data format) since none of your groups are numbers or dates


5) If necessary, realign columns to match their type (e.g., “Contract”, “Department”, etc.), and then add an appropriate header to each column

6) Add a new column called “Progress” that uses a formula like the following to simplify “In Progress” status
=IF(G2=”Not Started”,”Not Started”, IF(G2=”Complete”,”Complete”,”In Progress”))

7) On a second tab, create a pivot table that references the formatted table and puts “Progress” in as the Column and “Count of Progress” in as the Values.


8) Use different group columns (e.g., “Contract”, “Department”, etc.) in the pivot table rows to “slice and dice” the output (example enclosed)


Posted: June 30, 2016
Jonathan Lampe
View Profile

Jonathan Lampe, CISSP has led the development of award-winning security software and supporting services for Standard Networks, Ipswitch, and  SolarWinds.  He holds computer science and business degrees from Northern Illinois University and the University of Wisconsin, and currently holds SANS GSNA and CCSK certifications in addition to his (ISC)2 credentials.  When not coding, hacking, or writing, Lampe likes to spend time with his family in the beautiful Wisconsin outdoors.