Parsing embedded JSON into PowerBI table

[Anonymous]

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

[ImkeF · Accepted solution]

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

[Anonymous]

Hello ImKeF,

that was exactly what I was after, thank you very much.

Is it on purpose that PowerBI does not keep the old data set when you update the source?

regards