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

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

Imke Feldmann

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

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

Imke Feldmann

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors