How to pass Json parameters to powershell script

Question

I would like to pass Json data to Powershell script.

PowerShell script:

Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server" | Out-File "file.json"

New-AzMySqlFirewallRule -Name “” -ResourceGroupName "dev" -ServerName "dev-core" -EndIPAddress "" -StartIPAddress ""

In the above powershell script I need to get values to "" from Json file mentioned below.So how to get Json parameter values during run time and all 3 parameters should be passed to the above command and so that it will create new firewall rule to new DB server.

Also, when I run the powershell command (Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server" | Out-File "file.json") I am getting my Json file data in the below format.Not sure whether this format looks good but I need the below values start from pdbr_home,1.2.3.4 and 5.6.7.8 and similarly another 2 rows of data should be passed to my powershell command here New-AzMySqlFirewallRule -Name “” -ResourceGroupName "dev" -ServerName "dev-core" -EndIPAddress "" -StartIPAddress "". via for loop.

file.Json:

      [
        {
        "EndIPAddress": "1.3.2.2",
        "Id": "/subscriptions/abcdefg/resourceGroups/dev/providers/Microsoft.DBforMySQL/servers/db-dev-  vm/firewallRules/praveen_Home",
        "Name": "praveen_Home",
        "StartIPAddress": "4.3.1.2",
        "Type": "Microsoft.DBforMySQL/servers/firewallRules"
      },
      {
        "EndIPAddress": "2.4.5.6",
        "Id": "/subscriptions/abcdefg/resourceGroups/dev/providers/Microsoft.DBforMySQL/servers/db-dev-  vm/firewallRules/pdbr_Home",
        "Name": "pdbr_Home",
        "StartIPAddress": "3.2.1.2",
        "Type": "Microsoft.DBforMySQL/servers/firewallRules"
      }
    ]

The below command output as follows.

PS /home/praveen> Get-Command json

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Cmdlet          ConvertFrom-Json                                   7.0.0.0    Microsoft.PowerShell.Utility
Cmdlet          ConvertTo-Json                                     7.0.0.0    Microsoft.PowerShell.Utility
Cmdlet          Test-Json                                          7.0.0.0    Microsoft.PowerShell.Utility
Application     json_pp                                            0.0.0.0    /usr/bin/json_pp
Application     json_pp                                            0.0.0.0    /bin/json_pp

Error:

Error:

New-AzMySqlFirewallRule: /home/praveen/dbtest.ps1:21
Line |
  21 |  …  -ServerName "praveen-dev" -EndIPAddress $entry.EndIPAddress -StartI …
     |                                              ~~~~~~~~~~~~~~~~~~~
     | Cannot bind argument to parameter 'EndIPAddress' because it is an empty string.

Final solution worked for me now:

##################### Updating Firewall rules from Soiurce DB server to Target DB server ##################

Write-Host -NoNewline "Updating Firewall rules from Soiurce DB server to Target DB server"
Get-AzMySqlFirewallRule -ResourceGroupName $ResourceGroupName -ServerName $SourceDBServerName | Select-Object Name, StartIPaddress, EndIPaddress | Convertto-Json | Out-File "firewallrule.json"
foreach ($frule in (Get-Content firewallrule.json -raw | ConvertFrom-Json)) {
  New-AzMySqlFirewallRule -Name $frule.Name -ResourceGroupName $ResourceGroupName -ServerName $TargetDBServerName -EndIPAddress $frule.EndIPAddress -StartIPAddress $frule.StartIPAddress
}

Answer 1

//role-definition.json

{
    "RoleName": "MyReadWriteRole",
    "Type": "CustomRole",
    "AssignableScopes": ["/"],
    "Permissions": [{
        "DataActions": [
            "Microsoft.DocumentDB/databaseAccounts/readMetadata",
            "Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers/items/*",
            "Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers/*"
        ]
    }]
}

Coverting it into json object as i need to pass it as an argument in a powershell parameter. So i did below -

1. Created a powershell parameter

   $roleDef= @" { "RoleName": "MyReadWriteRole", "Type": "CustomRole", "AssignableScopes": ["/"], "Permissions": [{ "DataActions": [ "Microsoft.DocumentDB/databaseAccounts/readMetadata", "Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers/items/", "Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers/" ] }] } "@

2. $jsonObject = $roleDef | ConvertFrom-Json

3. az cosmosdb sql role definition create --account-name cosmosdbaccname --resource-group 'my-rg' --body $jsonObject

but I get below error - Failed to parse string as JSON: @{RoleName=MyReadWriteRole; Type=CustomRole; AssignableScopes=System.Object[]; Permissions=System.Object[]} Error detail: Expecting value: line 1 column 1 (char 0)

Answer 2

I was able to fix this solution with help of all the above inputs few days ago. Thanks to all.Really appreciated your help.

Updating Firewall rules from Source DB server to Target DB server

Write-Host -NoNewline "Updating Firewall rules from Soiurce DB server to Target DB server"
Get-AzMySqlFirewallRule -ResourceGroupName $ResourceGroupName -ServerName $SourceDBServerName | Select-Object Name, StartIPaddress, EndIPaddress | Convertto-Json | Out-File "firewallrule.json"
foreach ($frule in (Get-Content firewallrule.json -raw | ConvertFrom-Json)) {
  New-AzMySqlFirewallRule -Name $frule.Name -ResourceGroupName $ResourceGroupName -ServerName $TargetDBServerName -EndIPAddress $frule.EndIPAddress -StartIPAddress $frule.StartIPAddress
}

Answer 3

Use Convertto-Json before writing file. Docs: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/convertto-json?view=powershell-7.2

Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server" | Convertto-Json | Out-File "file.json"

EDIT as requested:

Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server" | Select-Object Name,"StartIP address", "EndIP address"| Convertto-Json | Out-File "file.json"

Another EDIT as requested (fixed my mistake - thank you @sage pourpre):

foreach ($entry in (Get-Content file.json -raw | ConvertFrom-Json) {
    New-AzMySqlFirewallRule -name $entry.Name `
    -ResourceGroupName "dev" `
    -ServerName "dev-core" `
    -StartIPAddress $entry.StartIPAddress `
    -EndIPAddress $entry.EndIPAddress
}

Answer 4

try the below:

Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server" | Convertto-Json | Out-File "file.json"

$data = Get-Content "C:\Users\me\file.json" | Out-String | ConvertFrom-Json #replace path to where you have exported the json file


foreach ($line in $data) {
    New-AzMySqlFirewallRule -name $line.Name `
    -ResourceGroupName "dev" `
    -ServerName "dev-core" `
    -StartIPAddress $line.StartIPAddress `
    -EndIPAddress $line.EndIPAddress
}

A different method, although the question is for JSON, would be to just store the rules in a variable.

        $rules = Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server"
    
    foreach ($rule in $rules){
        New-AzMySqlFirewallRule -name $rule.Name `
            -ResourceGroupName "dev" `
            -ServerName "dev-core" `
            -StartIPAddress $rule.StartIPAddress `
            -EndIPAddress $rule.EndIPAddress
}