Building a script to convert XML file into JSON (with a specific format)

Question

I am working on a script to convert an XML output file into a JSON file with specific formatting. Below is the XML output to be converted. I am having trouble accomplishing this task and am asking what changes to the script at the bottom could I make that would accomplish this?

Snippet from output.xml:

<System>
<tools>
<tool_PSList>
<process>
<process>
<pid>4188</pid>
<ownersid>"S-1-5-21-3556032652-3399990904-2739522603-1001"</ownersid>
<ownername>"Bochm"</ownername>
<ownerdomain>"DESKTOP-LS0HCH3"</ownerdomain>
-<file>
<name>"C:\ProgramFiles\WindowsApps\Microsoft.Messaging_1.10.22012.0_x86__8wekyb3d8bbwe\SkypeHost.exe"</name>
<size>44032</size>
<attr str="ARCHIVE" hex="0x20"/>
<cert_exists>"FALSE"</cert_exists>
<cert_verified>"FALSE"</cert_verified>
<cert_result>"TRUST_E_NOSIGNATURE"</cert_result>
<cert_comment>"The file is not signed"</cert_comment>

The format required for the JSON output is as show below (idealoutput.json with the timestamp replaced by the time the script runs):

{
"os":  "win",
"ts":  "timestamp here",
"value":  "C:\\Program Files\\WindowsApps\\Microsoft.Messaging_1.10.22012.0_x86__8wekyb3d8bbwe\\SkypeHost.exe\"; cert_comment=\"The file is not signed\"
}

Below is the current script I have produced in its current state it is rather broken unfortunately as this is my first week working with PowerShell, XML and JSON. I can include the other files mentioned in my script below but I am guessing there is an easier way than importing the format and then attempting to modify it with the output file so I haven't listed it here. If you'd like to see the other XML files mentioned below just let me know and I would be happy to oblige. Any insight or advice would be appreciated!

#Puts output of crowdresponse in $xml
$xml = [xml](Get-Content -Path C:\Users\Bochm\Downloads\CrowdResponse\output.xml)

#Puts the formating in $format
$format = [xml](Get-Content -Path C:\Users\Bochm\Downloads\CrowdResponse\format.xml)

#Filters the output of crowdresponse into results 
$result = $xml.system.tools.tool_PSList.processes.process.file |
          Select-Object name, cert_comment

#adds the mac address to result
$result += $xml.system | Select-Object macv4

$cert_valid = $xml.system.tools.tool_PSList.processes.process.file | Where-Object {
    $_.cert_signer -notmatch "Microsoft Corporation" -and
    $_.cert_signer -notmatch "Microsoft Windows" -and
    $_.cert_comment -match "The file is signed and the signature was verified"
} | Select-Object name, cert_comment  

$cert_invalid = $xml.system.tools.tool_PSList.processes.process.file |
    Where-Object { $_.cert_comment -match "The file is not signed" } |
    Select-Object name, cert_comment  

$node.innerXml += $cert_invalid.innerXml | Select-Object name, cert_comment

$result | ConvertTo-Json |
    Out-File "C:\Users\Bochm\Downloads\CrowdResponse\output.json"
$cert_valid | ConvertTo-Json |
    Out-File "C:\Users\Bochm\Downloads\CrowdResponse\certs_valid.json"
$cert_invalid | ConvertTo-Json |
    Out-File "C:\Users\Bochm\Downloads\CrowdResponse\certs_invalid.json"
$node | Select-Object os, ts, value, 'text' | ConvertTo-Json |
    Out-File "C:\Users\Bochm\Downloads\CrowdResponse\format.json"

Code for followup question:

$name = $xml.SelectNodes('//file') | ForEach-Object {
          $_.cert_signer -notmatch  "Microsoft Corporation" -and
          $_.cert_signer -notmatch "Microsoft Windows" -and
          $_.cert_comment -match "The file is signed and the signature was verified"
        } | Select-Object cert_comment

$name = $xml.system.tools.tool_PSList.processes.process.file |
        SelectNodes('//file') |
        ForEach-Object {
          $_.cert_signer -notmatch "Microsoft Corporation" -and
          $_.cert_signer -notmatch "Microsoft Windows" -and
          $_.cert_comment -match "The file is signed and the signature was verified"
        } | Select-Object name

Code for hopefully the final question:

$xml = Get-Content 'C:\Users\Bochm\Downloads\CrowdResponse\output.xml'

$name = $xml.system.tools.tool_PSList.processes.process.file| Where-Object {
   $_.cert_signer -notmatch  "Microsoft Corporation" -and
   $_.cert_signer -notmatch "Microsoft Windows" -and
   $_.cert_comment -match "The file is signed and the signature was verified"
 } | ForEach-Object {
  $prop = [ordered]@{
    'os'    = 'win'
    'ts'    = (Get-Date).DateTime
    'value' = "{0}; cert_comment={1}" -f $_.name, $_.cert_comment
  }
  New-Object -Type PSCustomObject -Property $prop
} | Select-Object name 
#$name    = $xml.SelectSingleNode('//name').'#text'
$name = $xml.system.tools.tool_PSList.processes.process.file | Where-Object {
   $_.cert_signer -notmatch  "Microsoft Corporation" -and
   $_.cert_signer -notmatch "Microsoft Windows" -and
   $_.cert_comment -match "The file is signed and the signature was verified"
 } | ForEach-Object {
  $prop = [ordered]@{
    'os'    = 'win'
    'ts'    = (Get-Date).DateTime
    'value' = "{0}; cert_comment={1}" -f $_.name, $_.cert_comment
  }
  New-Object -Type PSCustomObject -Property $prop
} | Select-Object cert_comment 

$name = $xml.system.tools.tool_PSList.processes.process.file | Where-Object {
   $_.cert_signer -notmatch  "Microsoft Corporation" -and
   $_.cert_signer -notmatch "Microsoft Windows" -and
   $_.cert_comment -match "The file is signed and the signature was verified"
 } | ForEach-Object {
  $prop = [ordered]@{
    'os'    = 'win'
    'ts'    = (Get-Date).DateTime
    'value' = "{0}; cert_comment={1}" -f $_.name, $_.cert_comment
  }
  New-Object -Type PSCustomObject -Property $prop
} | Select-Object name 

$name = $xml.system.tools.tool_PSList.processes.process.file| Where-Object {
   $_.cert_comment -match "The file is not signed"
 } | ForEach-Object {
  $prop = [ordered]@{
    'os'    = 'win'
    'ts'    = (Get-Date).DateTime
    'value' = "{0}; cert_comment={1}" -f $_.name, $_.cert_comment
  }
  New-Object -Type PSCustomObject -Property $prop
} | Select-Object cert_comment 

$name = $xml.system.tools.tool_PSList.processes.process.file| Where-Object {
   $_.cert_comment -match "The file is not signed"
 } | ForEach-Object {
  $prop = [ordered]@{
    'os'    = 'win'
    'ts'    = (Get-Date).DateTime
    'value' = "{0}; cert_comment={1}" -f $_.name, $_.cert_comment
  }
  New-Object -Type PSCustomObject -Property $prop
} | Select-Object name 
#$name    = $xml.SelectSingleNode('//name').'#text'

#$comment = $xml.SelectSingleNode('//cert_comment').'#text'

$prop = [ordered]@{
  'os'    = 'win'
  'ts'    = (Get-Date).DateTime
  'value' = "$name; cert_comment=$comment"
}

New-Object -Type PSCustomObject -Property $prop |  ConvertTo-Json | Out-File "C:\Users\Bochm\Downloads\CrowdResponse\end.json"

Apologies, I suppose the question would be what powershell script could convert the Xml file listed above into the Json file listed below it? — DylanB
– DylanB, Commented Dec 10, 2015 at 15:53
There is no XML in your question, only 2 different JSON data structures. — Ansgar Wiechers
– Ansgar Wiechers, Commented Dec 10, 2015 at 16:02
Good catch, the initial XML file is pretty huge and I use the first line of the script to pull into an xml object before parsing with the 5th and 6th command. Here is a copy of the initial xml file. Let me know if you have other questions: pastebin.com/cAWh7Mjt — DylanB
– DylanB, Commented Dec 10, 2015 at 16:11
Please do not refer to external resources that may disappear at any given time. Include a representative sample in your question. — Ansgar Wiechers
– Ansgar Wiechers, Commented Dec 10, 2015 at 16:26

Ansgar Wiechers · Accepted Answer · 2015-12-11 19:37:48Z

2

It's still not quite clear to me what the desired transformation is, since your XML sample isn't well-formed, and 2 of the 3 properties from the output JSON don't seem to appear in it.

Assuming you want to extract the content of the <name> and <cert_comment> nodes and enrich it with OS and timestamp you could do something like this:

[xml]$xml = Get-Content 'C:\path\to\output.xml'

$name    = $xml.SelectSingleNode('//name').'#text'
$comment = $xml.SelectSingleNode('//cert_comment').'#text'

$prop = [ordered]@{
  'os'    = 'win'
  'ts'    = (Get-Date).DateTime
  'value' = "$name; cert_comment=$comment"
}

New-Object -Type PSCustomObject -Property $prop | ConvertTo-Json

Edit:

$name = $xml.SelectNodes('//file') | ForEach-Object {
          $_.cert_signer -notmatch  "Microsoft Corporation" -and
          $_.cert_signer -notmatch "Microsoft Windows" -and
          $_.cert_comment -match "The file is signed and the signature was verified"
        } | Select-Object cert_comment

$name = $xml.system.tools.tool_PSList.processes.process.file |
        SelectNodes('//file') |
        ForEach-Object {
          $_.cert_signer -notmatch "Microsoft Corporation" -and
          $_.cert_signer -notmatch "Microsoft Windows" -and
          $_.cert_comment -match "The file is signed and the signature was verified"
        } | Select-Object name

These two code snippets won't work. For filtering a list of objects you need Where-Object, not ForEach-Object:

 ... | Where-Object {
   $_.cert_signer -notmatch  "Microsoft Corporation" -and
   $_.cert_signer -notmatch "Microsoft Windows" -and
   $_.cert_comment -match "The file is signed and the signature was verified"
 } | ...

Also, you don't want to assign a list of comments to $name but create a list of custom objects, so you need to do the object creation inside a ForEach-Object loop that you put after the filter:

... | ForEach-Object {
  $prop = [ordered]@{
    'os'    = 'win'
    'ts'    = (Get-Date).DateTime
    'value' = "{0}; cert_comment={1}" -f $_.name, $_.cert_comment
  }
  New-Object -Type PSCustomObject -Property $prop
} | ...

edited Dec 11, 2015 at 19:37

answered Dec 10, 2015 at 22:23

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

8 Comments

DylanB Over a year ago

Wow I can't begin to thank you enough! That works almost perfectly, I have only one other question. Would it be possible to modify the code above so that 'value' contains every process with a the following cert comment: <cert_comment>"The file is not signed"</cert_comment> This script will be for malware detection on Windows and I want to ensure it returns all processes with unsigned certificates. Again thank you for the help you have been a real life saver and your patience has been astounding. I appreciate the help and would be happy to elaborate if you have any other questions.

Ansgar Wiechers Over a year ago

Sure. You'll have to select the parent node of name and cert_comment, though, and you'll have to process all of them in a loop (presumably something like $xml.SelectNodes('//process') | ForEach-Object { ... }).

DylanB Over a year ago

Thanks again Ansgar, Unfortunately having trouble getting the value field to populate. I tried using the loops shown below but to no avail: $name = $xml.SelectNodes('//file') | ForEach-Object{ $_.cert_signer -notmatch "Microsoft Corporation" -and $_.cert_signer -notmatch "Microsoft Windows" -and $_.cert_comment -match "The file is signed and the signature was verified" } | Select-Object cert_comment

DylanB Over a year ago

$name = $xml.system.tools.tool_PSList.processes.process.file|SelectNodes('//file') | ForEach-Object{ $_.cert_signer -notmatch "Microsoft Corporation" -and $_.cert_signer -notmatch "Microsoft Windows" -and $_.cert_comment -match "The file is signed and the signature was verified" }| Select-Object name

DylanB Over a year ago

Thanks again @Ansgar Wiechers unfortunately it appears there is an issue with the output as the value field always appears empty in the Json file. Any ideas what might be preventing the

|

Collectives™ on Stack Overflow

Building a script to convert XML file into JSON (with a specific format)

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related