Creating Excel Reports Using The Knife Command

Hello, I am having a lot of trouble creating an excel spreadsheet of all nodes with information on their domain, environments and run_lists on our network.

I am using this command "knife search ':' --format=json > c:\test1.json". This is pulling all the information about the nodes and adds them to a json file.

I am able to create an excel sheet manually from this json, however, there is a lot of manual work involved and I would like to completely automate this process for reporting purposes. So far I haven't been able to find any answers after googling this for a few days now.

Is there any way to format this information with the knife command or is there a easy way to convert this json file to a excel spreadsheet through code? I would like to put together a script to automatically create a spreadsheet from this information without any manual work.

Thanks,

I'm not sure if this is going to help you at all, but we use jq (https://stedolan.github.io/jq/) for processing json data like this, although it takes a while to learn effectively. I have an example of when we did a similar query and output the result as a csv file, which can then be easily imported or opened as a spreadsheet:

knife search node '*:*' -a platform_version -a platform -a name -Fj | jq -r '.rows[][] | [.[]] | @csv' > nodes.csv

You can add whatever attributes you want as columns with extra -a parameters to the knife command.