2

I've got a CSV (with headers) from all of our access points and want to add them to Zabbix via XML Upload.

Since I don't want to create an XML file for each AP manually, I'll try to do this in PowerShell. But - how?

I've tried some things with (Get-Content .\Template.xml).Replace() and ForEach-Object, but I haven't had success yet.

Filenames are:

  • AP.csv (list of APs, headers are Hostname and IP):

    Hostname;IP
    AP1;10.29.202.101
    AP2;10.29.202.102
    AP3;10.29.202.103
    AP4;10.29.202.104
    Ap5;10.29.202.105
  • Template.xml:

    <hosts>
      <host>
        <host>Hostname</host>
        <name>Hostname</name>
        [...]
        <interfaces>
          <interface>
            [...]
            <ip>PIIP</ip>
            [...]
          </interface>
          <interface>
    

    The Template.xml has 2 strings Called "Hostname" and 2 Called "PIIP" - Both Hostname strings should be replaced with 1 Hostname from AP.csv, and both PIIP should be replaced with the related IP.

My Last attempt so far was:

$a = Import-Csv .\Ap.csv
$APHost = ($a).Hostname
$APIP = ($a).IP
$xml = Get-Content .\Template.xml
foreach ($row in $a) {
    $xml.Replace('Hostname',$APHost).Replace('PIIP',$APIP) |
        Out-File ".\Aps\$row.xml"
}

But this ended in all hostnames and all IPs where in 1 XML file.

I would like to have 1 XML file for each host at the end with hostnames and related IP.

3
  • 1
    Please add samples of the xml/csv files you refer to, along with the substitutions you expect, and your attempts so far Commented Dec 14, 2016 at 13:09
  • import-csv? have you tried that? Commented Dec 14, 2016 at 13:10
  • Updated the Mainpost, Yep, already tried but i don't know how i select only 1 Hostname and the related ip. Commented Dec 14, 2016 at 14:13

2 Answers 2

2

Your primary problem is:

  • you're storing the AP names and IPs in array variables outside the loop ($APHost and $APIP)

  • and then use those arrays as a whole inside your loop, rather than referencing their elements one by one.

Here's a fix, which simply uses $row.Hostname and $row.IP to get the iteration-appropriate values:

$a = Import-Csv -Delimiter ';' .\Ap.csv
$xml = Get-Content -Raw .\Template.xml
foreach ($row in $a) {
    $xml.Replace('Hostname', $row.Hostname).Replace('PIIP', $row.IP) |
      Out-File ".\Aps\$row.xml"
}

However, you should consider using true XML parsing to make your replacements more robust:

$a = Import-Csv -Delimiter ';' .\Ap.csv
$xmlTmpl = [xml] (Get-Content -Raw .\Template.xml)

foreach ($row in $a) {

  # Find the parent Xml element of the elements to update.
  $parentEl = $xmlTmpl.hosts.host

  # Set the hostnames, by element *name*.
  $parentEl.host = $parentEl.name = $row.Hostname

  # Set the IP addresses, by element *name*
  foreach ($el in $parentEl.interfaces.interface.ChildNodes) {
    if ($el.Name -eq 'ip') { $el.InnerText = $row.IP } 
  }

  # XPath alternative (doesn't work in PowerShell *Core*):
  # foreach ($el in $parentEl.SelectNodes('interfaces/interface/ip'))
  # { 
  #   $el.InnerText = $row.IP
  # }

  # Save to file 
  # Note: This doesn't include an XML header.
  $xmlTmpl.OuterXml | Out-File ".\Aps\$row.xml"
}

Note how specific elements in specific places in the document hierarchy are targeted, which is a more robust way of instantiating the template (no potential for false positives).

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

Comments

1

Example:

$CSVData = Import-Csv "$env:userprofile\desktop\Ap.csv" -Delimiter ";"

$template = Get-Content "$env:userprofile\desktop\template.xml" -Raw

$count = 1

foreach ($row in $CSVData) {

    $template.Replace("Hostname", $row.Hostname).Replace("PIIP", $row.IP) |
        Out-File "$env:userprofile\desktop\$count.xml"

    $count++

}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.