Forum Discussion

sshoaib's avatar
sshoaib
Copper Contributor
Mar 04, 2021

Parsing JSON files

Hi All I am trying to add new key value layer to existing JSON files (SQL logs that will be sent to cloud). So the current JSON output is like: [ { "Column1": ".....", "session_server_principal_...
Windows PowerShell
AndySvints's avatar
AndySvints
Iron Contributor
Apr 28, 2021

Hello sshoaib,

If I understood you correctly you need to enrich your existing Json file with additional key value pair and also increase file depth.

One of the approaches could be something like this:

Input Json file containing array of your events:

[
    {
    "Column1": ".....",
    "session_server_principal_name": "",
    "server_principal_name": "....",
    "database_principal_name": "",
    "target_database_principal_name": "",
    "target_server_principal_name": "",
    "server_instance_name": "S.....",
    "statement": "....."
    },
    {
        "Column1": ".....",
        "session_server_principal_name": "",
        "server_principal_name": "....",
        "database_principal_name": "",
        "target_database_principal_name": "",
        "target_server_principal_name": "",
        "server_instance_name": "S.....",
        "statement": "....."
        }
]

 Couple of PowerShell lines to enrich it:

#Assuming your input json  is stored in a file
$JsonFile=Get-Content .\File.json | ConvertFrom-Json

$EnrichedJson=@{
    split_events=$true
    events=$JsonFile
}

#Output result to a file
$Props | ConvertTo-Json | Out-File JsonFileOutput.json

#Or store it in variable
$Result=$Props | ConvertTo-Json

Output file:

{
  "split_events": true,
  "events": [
    {
      "Column1": ".....",
      "session_server_principal_name": "",
      "server_principal_name": "....",
      "database_principal_name": "",
      "target_database_principal_name": "",
      "target_server_principal_name": "",
      "server_instance_name": "S.....",
      "statement": "....."
    },
    {
      "Column1": ".....",
      "session_server_principal_name": "",
      "server_principal_name": "....",
      "database_principal_name": "",
      "target_database_principal_name": "",
      "target_server_principal_name": "",
      "server_instance_name": "S.....",
      "statement": "....."
    }
  ]
}

Hope that helps.

Resources