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
}

[1] PLEASE wrap your code in formatting markers. ///// the sample JSON you posted does not look like proper JSON. have you looked at the result of Get-Command *json* yet? [grin] — Lee_Dailey
– Lee_Dailey, Commented May 7, 2022 at 8:49
@Lee_Dailey Yes, it was not formatted as I did wanted to show that to you why it was generated improperly and the above json code format has been generated when I run the following command: Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server" | Out-File "file.json" — PRAVEEN PDBR
– PRAVEEN PDBR, Commented May 7, 2022 at 18:54
@Lee_Dailey Now after sharing the command below by An-dir I have run the command and updated Json data above. Now can you please help me to iterate Json data through for loop using powershell script. Now, I want to pass Name,StartIP address, EndIP address of Json data to the below command while creating new firewallrule for another DB. New-AzMySqlFirewallRule -Name “” -ResourceGroupName "dev" -ServerName "dev-core" -EndIPAddress "" -StartIPAddress "". via for loop. Please share the for loop how to iterate the above Json parameters to the powershell command. — PRAVEEN PDBR
– PRAVEEN PDBR, Commented May 7, 2022 at 19:48
you convert data to JSON format and back again using the JSON cmdlets. again, take a look at Get-Command *json* for the how-to. [grin] — Lee_Dailey
– Lee_Dailey, Commented May 8, 2022 at 3:30
@Lee_Dailey I have ran the command Get-Command json and the result is as updated above.Now can you please let me know how to pass parameters from Json to powershell script. — PRAVEEN PDBR
– PRAVEEN PDBR, Commented May 8, 2022 at 18:38

An-dir · Accepted Answer · 2022-05-15 17:47:37Z

1

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
}

edited May 15, 2022 at 17:47

answered May 7, 2022 at 8:53

An-dir

4653 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

PRAVEEN PDBR Over a year ago

Now the format is working fine after I run the command below. Get-AzMySqlFirewallRule -ResourceGroupName "dev" -ServerName "dev-DB-Server" | Convertto-Json | Out-File "file.json" Now, I want to pass Name,StartIP address, EndIP address to the below command while creating new firewallrule for another DB.

PRAVEEN PDBR Over a year ago

Any update from your end please?

An-dir Over a year ago

Just edited my code. Check If that is what you need

PRAVEEN PDBR Over a year ago

Now,I need to pass those parameters from JSON to powershell script one by one using for loop in powershell. Here in JSON script will have multiple values for the 3 parameters and those I need to pass 3 Params at a time and again 3 params at time to the below powershell command. There I got stuck actually. Can you please help there? Here the below command needs to pick the 3 parameter values from json and should execute the below command: New-AzMySqlFirewallRule -Name “” -ResourceGroupName "dev" -ServerName "dev-core" -EndIPAddress "" -StartIPAddress "".

Sage Pourpre Over a year ago

In your last example, you used ConvertTo-Json. I believe you intended ConvertFrom-Json there :) (eg: (Get-Content file.json -raw | ConvertFrom-Json)

|

EZRyan · Accepted Answer · 2022-05-12 14:00:41Z

0

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
}

edited May 12, 2022 at 14:00

answered May 12, 2022 at 13:40

EZRyan

694 bronze badges

1 Comment

PRAVEEN PDBR Over a year ago

Sorry for the late, I was able to fix this and solution is updated above.

PRAVEEN PDBR · Accepted Answer · 2022-05-18 05:44:19Z

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
}

Aakansha Verma · Accepted Answer · 2022-12-01 08:40:06Z

0

//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 -

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/" ] }] } "@
$jsonObject = $roleDef | ConvertFrom-Json
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)

answered Dec 1, 2022 at 8:40

Aakansha Verma

113 bronze badges

2 Comments

Aakansha Verma Over a year ago

@EZRyan - Any help on above?

Pandapip1 Over a year ago

If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review

Collectives™ on Stack Overflow

How to pass Json parameters to powershell script

4 Answers 4

9 Comments

1 Comment

Updating Firewall rules from Source DB server to Target DB server

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

1 Comment

Updating Firewall rules from Source DB server to Target DB server

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related