CODEWITHSUNDEEP
jsonsharepointpower-automate
sugarakis
sugarakis

Reputation: 129

Power automate - Unable to get JSON content from Sharepoint folder

I have a Power Automate flow that gets a JSON file from a SharePoint folder (when dropped into it) and I want to be able to read its contents and extract a key/value. The issue I am having is that it crashes in the second step, Get File Content, with the error

Encountered internal server error. The tracking Id is '3bc1890c-1932-4a2e-af14-6e5cca1534be'.

I realized that it has to do with the JSON file's syntax. When I ran the flow with the below JSON file

{
    "products": {
      "GF": [
        {
          "product_name": "GF",
          "remediation": {
            "type": "package",
            "packages": [
              {
                "service": "av",
                "service_name": "AV",
                "description": "Detects Prestige ransomware payloads",
                "kill_chain": {
                  "step": "Delivery"
                },
                "link": "https://www.GF.com/updates/antivirus?version=90.06850",
                "minimum_version": "90.06850"
              }
            ]
          },
          "detection": {
            "virus": [
              "W32/Filecoder.OMM!tr.ransom"
            ],
            "virusid": [
              10108353
            ]
          }
        }
      ]
    },
    "title": "Prestige Ransomware",
    "sub_title": "Impacting organizations in Ukraine and Poland",
    "link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/",
    "cve_list": [
      "TBA"
    ],
    "description": "Researchers at Microsoft Threat Intelligence Center (MSTIC) have identified evidence of a novel ransomware campaign targeting organizations in the transportation and logistics industries in Ukraine and Poland. According to the report, the new ransomware labels itself with a ransom note of “Prestige ranusomeware”.",
    "tag": "Prestige Ransomware",
    "event_handler_desc": "",
    "m": 851548,
    "additional_resources": [
      {
        "Source": "Microsoft Security Blog",
        "Link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/"
      }
    ],
    "ob_url": "https://www.GF.com/ob-alert/prestige-ransomware"
  }

the PA workflow executes successfully. However, when I try with the same PA workflow with the JSON file below (the required one)

[
  {
    "products": {
      "GF": [
        {
          "product_name": "GF",
          "remediation": {
            "type": "package",
            "packages": [
              {
                "service": "av",
                "service_name": "AV",
                "description": "Detects Prestige ransomware payloads",
                "kill_chain": {
                  "step": "Delivery"
                },
                "link": "https://www.GF.com/updates/antivirus?version=90.06850",
                "minimum_version": "90.06850"
              }
            ]
          },
          "detection": {
            "virus": [
              "W32/Filecoder.OMM!tr.ransom"
            ],
            "virusid": [
              10108353
            ]
          }
        }
      ]
    },
    "title": "Prestige Ransomware",
    "sub_title": "Impacting organizations in Ukraine and Poland",
    "link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/",
    "cve_list": [
      "TBA"
    ],
    "description": "Researchers at Microsoft Threat Intelligence Center (MSTIC) have identified evidence of a novel ransomware campaign targeting organizations in the transportation and logistics industries in Ukraine and Poland. According to the report, the new ransomware labels itself with a ransom note of “Prestige ranusomeware”.",
    "tag": "Prestige Ransomware",
    "event_handler_desc": "",
    "m": 851548,
    "additional_resources": [
      {
        "Source": "Microsoft Security Blog",
        "Link": "https://www.microsoft.com/security/blog/2022/10/14/new-prestige-ransomware-impacts-organizations-in-ukraine-and-poland/"
      }
    ],
    "ob_url": "https://www.GF.com/ob-alert/prestige-ransomware"
  }
]

the workflow crashes with the error I posted in the beginning of my post. It seems that the initial and ending [ ] make the workflow step of Get File Content to crash.

The problem is that this is the actual JSON syntax I need to work with. Any idea or suggestion would ne very valuable, because I have been working on it for the last couple of days and I am stuck BIG TIME!

The PA workflow is the below enter image description here

Thank you in advance, Nikos

Upvotes: 2

Views: 5593

Answers (1)

Skin
Skin

Reputation: 11277

If the content of the file can't be inferred (because the file doesn't have an extension) that action returns a base64 string.

Data

When you rename the file with an extension of .txt it infers the content as text and therefore works.

Rather than rename the file, which if you want to make it perfect will require elements of pain depending on the approach you take, you can simply decode the base64 string in the $content field by using an expression like this ...

decodeBase64(body('Get_file_content')?['$content'])

... within the Content field of the Parse JSON action but within the Expression section, not the Dynamic content section.

Expression

That should give you what you want.

Result

Result

Upvotes: 4

Related Questions