cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
totaldisaster
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Parsing embedded JSON into PowerBI table

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Parsing embedded JSON into PowerBI table

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

totaldisaster
Regular Visitor

Re: Parsing embedded JSON into PowerBI table

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Top Solution Authors
Top Kudoed Authors