Forum Discussion
Parsing embedded JSON into PowerBI table
Hi Everyone,
I have tried to follow the video on http://community.powerbi.com/t5/Desktop/Parsing-Embedded-JSON/m-p/118623/highlight/true#M50071
to get embedded json displayed. unfortunately, I am not getting to any results (which probably gives some idea about my Power-BI skill-level) with my json code. I am trying to get the techniques used by the groups from the following JSON Code
{
"query-continue-offset": 5,
"query": {
"printrequests": [
{
"label": "",
"key": "",
"redi": "",
"typeid": "_wpg",
"mode": 2
},
{
"label": "Has technique",
"key": "Has_technique",
"redi": "",
"typeid": "_wpg",
"mode": 1,
"format": ""
}
],
"results": {
"Group/G0006": {
"printouts": {
"Has technique": [
{
"fulltext": "Technique/T1003",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1003",
"namespace": 0,
"exists": "1",
"displaytitle": "Credential Dumping"
},
{
"fulltext": "Technique/T1036",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1036",
"namespace": 0,
"exists": "1",
"displaytitle": "Masquerading"
},
{
"fulltext": "Technique/T1075",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1075",
"namespace": 0,
"exists": "1",
"displaytitle": "Pass the Hash"
},
{
"fulltext": "Technique/T1076",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1076",
"namespace": 0,
"exists": "1",
"displaytitle": "Remote Desktop Protocol"
},
{
"fulltext": "Technique/T1114",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1114",
"namespace": 0,
"exists": "1",
"displaytitle": "Email Collection"
},
{
"fulltext": "Technique/T1064",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1064",
"namespace": 0,
"exists": "1",
"displaytitle": "Scripting"
},
{
"fulltext": "Technique/T1059",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1059",
"namespace": 0,
"exists": "1",
"displaytitle": "Command-Line Interface"
},
{
"fulltext": "Technique/T1005",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1005",
"namespace": 0,
"exists": "1",
"displaytitle": "Data from Local System"
},
{
"fulltext": "Technique/T1002",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1002",
"namespace": 0,
"exists": "1",
"displaytitle": "Data Compressed"
}
]
},
"fulltext": "Group/G0006",
"fullurl": "https://attack.mitre.org/wiki/Group/G0006",
"namespace": 0,
"exists": "1",
"displaytitle": "Group: APT1, Comment Crew, ..."
},
"Group/G0005": {
"printouts": {
"Has technique": []
},
"fulltext": "Group/G0005",
"fullurl": "https://attack.mitre.org/wiki/Group/G0005",
"namespace": 0,
"exists": "1",
"displaytitle": "Group: APT12, IXESHE, ..."
},
"Group/G0023": {
"printouts": {
"Has technique": []
},
"fulltext": "Group/G0023",
"fullurl": "https://attack.mitre.org/wiki/Group/G0023",
"namespace": 0,
"exists": "1",
"displaytitle": "Group: APT16"
},
"Group/G0025": {
"printouts": {
"Has technique": []
},
"fulltext": "Group/G0025",
"fullurl": "https://attack.mitre.org/wiki/Group/G0025",
"namespace": 0,
"exists": "1",
"displaytitle": "Group: APT17, Deputy Dog"
},
"Group/G0026": {
"printouts": {
"Has technique": [
{
"fulltext": "Technique/T1053",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1053",
"namespace": 0,
"exists": "1",
"displaytitle": "Scheduled Task"
},
{
"fulltext": "Technique/T1107",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1107",
"namespace": 0,
"exists": "1",
"displaytitle": "File Deletion"
},
{
"fulltext": "Technique/T1133",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1133",
"namespace": 0,
"exists": "1",
"displaytitle": "External Remote Services"
},
{
"fulltext": "Technique/T1078",
"fullurl": "https://attack.mitre.org/wiki/Technique/T1078",
"namespace": 0,
"exists": "1",
"displaytitle": "Valid Accounts"
}
]
},
"fulltext": "Group/G0026",
"fullurl": "https://attack.mitre.org/wiki/Group/G0026",
"namespace": 0,
"exists": "1",
"displaytitle": "Group: APT18, Threat Group-0416, ..."
}
},
"serializer": "SMW\\Serializers\\QueryResultSerializer",
"version": 2,
"meta": {
"hash": "2114733f72ac04384ee95f38b7fea909",
"count": 5,
"offset": 0,
"source": "",
"time": "0.020588"
}
}
}I get as far as getting the Groups displayed but not the techniques in use. Hopefully somebody can shed some light on this.
regards,
totaldisaster
Pls check if this delivers what you're after:
let Source = Json.Document(<<YourJSON>>), query = Source[query], results = query[results], #"Converted to Table" = Record.ToTable(results), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}), #"Expanded printouts" = Table.ExpandRecordColumn(#"Expanded Value", "printouts", {"Has technique"}, {"Has technique"}), #"Expanded Has technique" = Table.ExpandListColumn(#"Expanded printouts", "Has technique"), #"Expanded Has technique1" = Table.ExpandRecordColumn(#"Expanded Has technique", "Has technique", {"fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"fulltext.1", "fullurl.1", "namespace.1", "exists.1", "displaytitle.1"}) in #"Expanded Has technique1"How to integrate M-code into your solution -- Check out more PBI- learning resources here
2 Replies
Pls check if this delivers what you're after:
let Source = Json.Document(<<YourJSON>>), query = Source[query], results = query[results], #"Converted to Table" = Record.ToTable(results), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}), #"Expanded printouts" = Table.ExpandRecordColumn(#"Expanded Value", "printouts", {"Has technique"}, {"Has technique"}), #"Expanded Has technique" = Table.ExpandListColumn(#"Expanded printouts", "Has technique"), #"Expanded Has technique1" = Table.ExpandRecordColumn(#"Expanded Has technique", "Has technique", {"fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"fulltext.1", "fullurl.1", "namespace.1", "exists.1", "displaytitle.1"}) in #"Expanded Has technique1"How to integrate M-code into your solution -- Check out more PBI- learning resources here