Samuel Brookes (@sjb-ch1mp)
PivotDrill is a simple tool for exploring and summarizing JSON data.
PivotDrill does not require any installation. Simply download the zip file from the PivotDrill repository, decompress it and open the file 'PivotDrill.html' in a browser.
If PivotDrill encounters an error, the error message will be announced in the top banner and the stack trace will be dumped to the console. If you encounter any bugs or errors while using PivotDrill, please feel free to add them as an issue in the PivotDrill repository.
My preference is that you use the error message as the issue title and paste the stack trace in the comment section.
PivotDrill only accepts JSON files (MIME type "application/json"). To upload a file, click the Browse...
button at the top right, select your file and click OK
.
The PivotDrill workspace is divided into three sections: the FIELDS
panel, the PIVOT
panel, and the DRILL
panel.
Each panel can be expanded to fill the entire workspace by clicking on the red banner at the bottom of the panel.
Each panel also contains a menu. This can be expanded by clicking the left-hand button in the blue banner at the top of the panel.
The functionality of PivotDrill will be explained by describing each panel separately. Throughout this guide, I will be using the following JSON file (example.json) to demonstrate how PivotDrill works.
example.json
{
"meta_key_1":"meta_value_1",
"meta_key_2":["meta_value_2","meta_value_3","meta_value_4"],
"root_key":{
"key_1":"value_1",
"key_2":{
"key_a":{
"key_i": "value_i",
"key_ii": "value_ii",
"key_iii": "value_iii",
"key_iv": ["value_iv","value_v","value_vi"],
"key_v": "value_vii"
},
"key_b":{
"key_i": "value_vii",
"key_ii": "value_viii",
"key_iii": "value_ix",
"key_iv": ["value_x","value_xi","value_xii"]
},
"key_c":{
"key_i": "value_xiii",
"key_ii": "value_xiv",
"key_iii": "value_xv",
"key_iv": ["value_xvi","value_xvii","value_xviii"],
"key_v": "value_xix"
},
"key_d":{
"key_i": "value_xx",
"key_ii": "value_xxi",
"key_iii": "value_xxii",
"key_iv": ["value_xxiii","value_xxiv","value_xxv"]
}
},
"key_3":[
"value_2",
"value_3",
"value_4",
"value_5"
]
}
}
When a new JSON file is uploaded to PivotDrill, all unique valued keys (i.e. with at least one non-null value) are 'flattened', summarized and saved as a 'dataset' in the FIELDS
panel. Saved datasets can be accessed by expanding the FIELDS
panel menu.
example.json after uploading to PivotDrill
Keys that share a common parent are grouped together beneath that parent key. By clicking on a parent key, a new dataset is created with the name ROOT_<parent_key>
, consisting of all unique valued keys of all children of that parent. In the example below, 3 new datasets are created by clicking the root_key
parent, the key_2
parent, and the key_a
parent.
ROOT_ROOT_KEY dataset fields
ROOT_KEY_2 dataset fields
ROOT_KEY_A dataset fields
As you can see above, the ROOT_KEY_2
dataset comprises of 4 siblings which share common keys, e.g. key_i
, key_ii
, etc. By holding the ALT
key on your keyboard and clicking on one of these common keys, the siblings will be 'merged' into a new dataset with the name MERGE_<merge_key>
.
Merging siblings removes the parent keys from the common keys so that they can be summarised in the PIVOT
panel. Note that the datasets ROOT_KEY_A and MERGE_KEY_I both have the same fields, but the former contains only those values for key_a
, while the latter contains the values for all sibling keys key_a
, key_b
, key_c
and key_d
.
MERGE_KEY_I dataset fields
Clicking on a field button in the FIELDS
panel will add a table to the PIVOT
panel that contains a list of all unique values for that key in that dataset. In the example below, all keys in the MERGE_KEY_I
dataset have been added as 'pivot' tables to the PIVOT
panel.
All keys from the MERGE_KEY_I dataset
Pivot tables can be removed from the PIVOT
panel by clicking on the table header, or toggling the button in the FIELDS
panel.
You can download all the currently visible pivot tables into a JSON file by clicking the DOWNLOAD PIVOT TABLES
button in the PIVOT
panel menu.
Clicking on a value in a pivot table will add that key=value pair to the 'drill query' in the DRILL
panel. Holding the ALT
key while clicking on a value in a pivot table will add that key=value pair as a NOT conditional, i.e. NOT (key=value).
When the drill query changes, it will automatically search through the current dataset for any 'entities' in which the drill query evaluates to TRUE, and summarise these as a table in the DRILL
panel.
In the example below, the drill query is searching through the current dataset, MERGE_KEY_I
, for any 'entity' in which the key key_i
is equal to value_i
. As you can see, this condition is only true in one out of the four 'entities'.
Drill query example 1
In the example below, the drill query is searching through the current dataset, MERGE_KEY_I
, for any 'entity' in which the key key_i
is NOT equal to value_i
. As you can see, this condition is true for three out of the four 'entities'.
Drill query example 2
Note that keys that contain a list as their value are hidden by a toggle-able button [...]
. You can click on this button to expand or hide the values in the list.
You can save the results of a drill query as a new dataset by clicking the SAVE AS NEW DATASET
button. This will create a new dataset in the FIELDS
panel.
Alternatively, you can download the results of a drill query as a JSON file by clicking the DOWNLOAD DRILL RESULTS
button.
In the below example, a JSON file generated by the VirusTotal REST API is uploaded into PivotDrill. PivotDrill is then utilised to extract the details for all engines that have classified the file submitted to VirusTotal as malicious.
The file vt-test.json is uploaded to PivotDrill...
...I create the dataset ROOT_LAST_ANALYSIS_RESULTS
by clicking parents data
> attributes
> last_analysis_results
...
...I create the dataset MERGE_CATEGORY
by ALT
+clicking on the category
field. By clicking on the value malicious
in the category
pivot table, I add this key=value pair to the drill query and the resulting table in the DRILL
panel summarises the data for all VirusTotal engines that classified the provided file as 'malicious'...
Date | Change Type | Applicable to Version | Description |
---|---|---|---|
2021-01-31 | VERSION | 1.0.0 | Version 1.0.0 committed. |
2021-02-04 | BUG | 1.0.0 | Cleaned up console.log() debug messages (Issue #4). Parsing of malformed JSON files now fails gracefully (Issue #3). |
2021-02-06 | BUG | 1.0.0 | Fixed bug in which restoring hidden drill table columns would create duplicate toggle arrays (Issue #5). |