Export CSV into granular rows - how to break out complex fields to be on a row per field
Most of our users are generalists of varying levels. Often times, we have a great knowledge of the inner workings of the platform but struggle with nuances that can drive you crazy. Here is a use case that you may find helpful.
Use Case: Customer is doing an audit whereas they would like to bring in a complex object that may have more than one result and would like each result to be on their own line.
*This may be a vulnerability or software and in that case, do know that we will have a module for vulnerability management in 4.6 (early spring) and software asset management in 4.7 (Mid summer) that will make this even easier.
Let's prep the table... first, what is a complex object?
Complex objects are fields that contain multiple fields inside of them. Many people think of them as parent fields that hold the child field.
Why do we have complex fields?... Well, that is because each column has its own filterable logic. It is very easy to organize topics in a high level field that we could call the parent and each child field would be related. Take Vulnerable Software or Network Interfaces. Both of those fields are parents that have a myriad of sub fields under them
If you look out of the box, you will see Vulnerable software has the following children:
- CVE ID
- Software Name
- Software Version
- Software Vendor
- CVSS Version
- CVSS String
- CVSS V2 Score
- CVSS V3 Score
- Adapter Source Score
- CVE Severity
- CVE Description
- CVE Synopsis
- Is Exploit
- CVE Type
- CVSS Vector
If we look up a child field by itself, it may have from 1 to 200 different fields next to each other. If we were looking at the software name it may say: [software1][software2][+4]
If we look at the parent, we may see something more like this: [CVE ID: CVE-2016-12345 Software Name: Example1 Software Version:18.104.22.168 Software Vendor: Example Software LLC CVSS Version:v3.0 CVSS:7.8 CVSS String: CVSS 7.8 CVE Severity: HIGH]
There is a ton more information on the parent field but it can be daunting if there are more than one item on a single line. Well this is what we do.
First. Set your query exactly how you want it to be output.
Second. If you happen to have a field that has multiple inputs(that you want to parse by rows), you will want to take note of it.
Next. Click on the export CSV button.
Then. Click on the dropdown that says "Split by field values" and find your field. If we are looking at the Parent Vulnerable Software" field, then drop that down.
Lastly. Click "Export"
This will change the export from being an output of the exact number of devices to the exact number of devices broken into chunks by each vulnerable software. Do note that this can be EXTREMELY large if you choose a parent field with hundreds or thousands of fields.
***On average, if you are exporting to excel, CSV can import more than 43k rows but excel struggles to manipulate data that is larger than 43k. If you are going to bring in a huge dataset, you may want to consider a SQL database, python load, or other large data aggregator.