1

I need to extract a particular anti-virus product scan results from a JSON file and list the elements in csv format for further processing. JSON file sample as below:

Contents of a file scanresults.json

{
  "scans": {
    "Bkav": {
      "detected": true,
      "version": "1.3.0.9899",
      "result": "W32.AIDetect.malware2",
      "update": "20230417"
    },
    "Lionic": {
      "detected": true,
      "version": "7.5",
      "result": "Trojan.Win32.Generic.4!c",
      "update": "20230417"
    },
    "Elastic": {
      "detected": true,
      "version": "4.0.85",
      "result": "malicious (high confidence)",
      "update": "20230413"
    },
    "MicroWorld-eScan": {
      "detected": true,
      "version": "14.0.409.0",
      "result": "Trojan.Ransom.Cerber.1",
      "update": "20230417"
    }
  }
}

The JSON file contains Anti-Virus scan results organized by products. It is required to retrieve results related to the product "Elastic" only in csv format for further processing as below:

detected, version, result, update
true, "4.0.85", "malicious (high confidence)", "20230413"

Base on my research, able to extract the result by jq command as shown below:

jq-win64.exe ".scans.Elastic" scanresults.json

{
  "detected": true,
  "version": "4.0.85",
  "result": "malicious (high confidence)",
  "update": "20230413"
}

Tried but failed to use map function to extract the needed results in csv format. The reference link used is https://earthly.dev/blog/convert-to-from-json/.

$ cat simple.json| jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' 
"color","id","value"
"red",1,"#f00"
"green",2,"#0f0"
"blue",3,"#00f"

Thanks for provide solution and comments.

Destroy666
  • 12,350
Sam Chan
  • 11

1 Answers1

0

A specific bat to extract the lines and compose this output layout is what you need, try:

@echo off && setlocal enabledelayedexpansion

for /f skip^=4 %%e in ('echo;prompt $E^|cmd.exe
    ')do set "_$E=%%~e[1F%%~e[0J"


for /f usebackq^delims^=: %%G in (findstr /ni elastic ^< .\scanresults.json)do for /f tokens^=1*^delims^=^:^"^  %%i in ('more .\scanresults.json +%%~G /e
        ')do echo/%%i | find "}" >nul && goto %:^) || if not defined _str (
             set "_str=%%~j")else set _str=!_str:, ,=,! "%%~j
            )
%:^)
>.\Output.csv (
     echo/detected, version, result, update
     echo/!_str!
    ) & type .\Output.csv


endlocal & exit /b
  • .\Output.csv:
detected, version, result, update
true, "4.0.85", "malicious (high confidence)", "20230413"

for multiple .json files, try

@echo off && setlocal enabledelayedexpansion

for /f skip^=4 %%e in ('
     echo;prompt;$E^|%comSpec%'
    )do set "_$E=%%~e[1F%%~e[0J"


cd /d "%~dp0" && >.\Output.csv =;(
     echo/detected, version, result, update
    );=


for /f delims^= %%i in ('where .:*.json'
    )do call %:^) "%%~fi" && set^ _str=1<nul


%:^)
if "%~1" == "" type .\Output.csv && endlocal && exit /b
for /f usebackq^delims^=: %%G in =;(findstr /ni elastic ^&lt; &quot;%~1&quot;
    )do for /f tokens^=1*^delims^=^:^"^  %%i in =;(' more "%~1" +%%~G /e'
        )do echo/"%%~i" | find "}" >nul && =;( echo/!_str!>>.\Output.csv && exit /b 
            );= || if not defined _str (set "_str=%%~j")else set _str=!_str:, ,=,!"%%~j 
                );=
  • .\Output.csv:
detected, version, result, update 
true, "1.3.0.9899", "W32.AIDetect.malware2", "20230417"   
true, "4.0.85", "malicious (high confidence)", "20230413"
true, "14.0.409.0", "Trojan.Ransom.Cerber.1", "20230417"
Io-oI
  • 9,237