I often get asked for advice on manipulating content in and out of Archer, how to relate certain things & build reports, etc. Somebody recently asked the following and I figured hey, why not blog the answer.
“I’d like to do a review of my PCI control standards offline in Excel. What’s the best way to do this?”
Great question…This is part of a broader topic around working with mappings in Archer. The tips I outline here will work for a single authoritative source, multiple authoritative sources, and pretty much any other situation involving similar applications in Archer.
First recall that Archer’s Authoritative Sources application is what we call “leveled” meaning it has a hierarchy as opposed to a flat application which doesn’t. (Actually flat apps are essentially leveled apps with only one level but that’s beside the point.) The purpose of a leveled app is to provide a means for representing the native hierarchical structure common to most narrative documents such as structured standards, policies, and so on. The top level is usually a single record that describes the source such as “Payment Card Industry Data Security Standard v3.0”. The next level down might take a similar form to a table of contents and the remaining child levels will then contain the rest of the structural text. The leveled application structure allows us to deconstruct the source into its granular constituent components which is useful for clarifying the context of individual statements within the source.
Therefore when we map authoritative sources to Archer’s Control Standards library we typically map at the lowest levels meaning the most granular elements since the mappings are intended to reflect contextual relationships. Using PCI as an example if we were to merely identify all the Archer control standards that related to all of the individual aspects of PCI and lump those into a single cross-reference at the top level we’d obscure a lot of useful information. You’d only see the top level record and several hundred related control standards but no visibility into the context of why one thing is mapped to another. You may also obscure performance measurement by hampering the ability to inform findings, risks, and other upstream and downstream operational elements that may be tied to those standards.
Establishing the mapping connections further down at the lowest levels eliminates this issue. So when you view an individual record like PCI:8.2.6 which describes a requirement around setting first-time passwords, suddenly the related mapping to Archer control standard 308 (“Initial Passwords”) makes a lot more sense. Furthermore if you have individual stakeholder ownership assigned to different control standards it’s much easier to translate that ownership up to the external requirements those folks are also responsible for. Or, go the other way and identify specific compliance objectives and then easily correlate the related control standards, policies, and so on that drive compliance against those stated objectives.
So back to the original question about analyzing these offline and continuing with PCI as our example, PCI has mappings in its bottom two levels (Section and Sub Section). So both levels need to be considered in the analysis. Starting from the Authoritative Sources application click “Advanced Search“, then click “Add New Relationship” in the left dialog box and choose “Control Standards”. This creates an n-tier report which is really just a fancy way of saying you want Archer to crawl the cross-reference relationship between Authoritative Sources and Control Standards so you can include additional fields from the Control Standards library in your output. Next choose your desired fields to include in the report such as Source Name, Topic ID, Topic Name, Section ID, Section Name, etc. (Note: I’m stopping at the Section level for a reason.) Now scroll down in the left-had field selector and grab some fields from Control Standards such as Standard ID, Standard Name, Description, and Owner, or whichever ones you want.
Now add a new filter criterion to “Filter By Record” from the Source level and in the filter value dialog click the selector button & find the authoritative source(s) you want to include. Set the report type to “Column – Flat” in the Display options and click the Search button. Archer will return a flat list of the authoritative sources you selected (including native structure) down to the section level along with any control standard(s) a given section record is mapped to, each on its own row.
By default this will also include unmapped records too. You can optionally check the “Enforce Relationships” box to strip those out but I find sometimes it’s better to keep them in so I can also see anything that isn’t mapped. Plus it’s easy to filter them out in Excel later anyway.
Export the results to Excel format or a CSV file and then go back and modify the Archer search criteria. Add the Sub Section ID and Sub Section Name fields from the field picker on the left and remove the section level fields on the right by clicking the “X” button next to each one. Leave the Control Standards fields you chose for the first report alone and run the search again. Export those results to a second file. Open both files in Excel and then paste the contents from one into the other to render a single flat list of all mappings for that authoritative source (I usually paste the Sub Section records to the end of the Section file but the order doesn’t really matter). You can use these same techniques for any leveled application scenario.
Note that if a control standard has been mapped more than once it will appear multiple times in the list. Use Excel’s highlight duplicates feature on the Standard ID or Standard Name column to color code the duplicates and filter them down further if needed for pivot table analysis or whatever you want to do. Don’t forget you can already do tons of analysis like this in Archer too but sometimes it’s nice to take it offline to the beach or...ahem, wherever you choose to be productive outside of the office.
That does it for this issue of the content mailbag. Got some tricks of your own to share? We’d love to hear them! And keep an eye out for exciting new content headed your way soon!
