Is their a way to get details of every instance of a sql server on a node and store in a tabular form in a csv or txt file?
Ankur what exactly the information your looking for do you mean to check on what are all the nodes on which sql is installed and running?
if that is the case u can use knife : "knife search node ‘.’ -a package | grep sql > sqlpackage.csv
knife search node ‘.’ -a fqdn -a ipaddress -a packages packagename -F pp >nodeinfo.csv
No Its not that i want to create pre - requisties so that i will get to know the information of all instance installed on one node?
What isn’t clear here is how you’d like chef to participate in this operation. It’s useful to understand that Chef only automates tasks you can already do on systems and provides Resources to make this easier. Chef is also designed around not running one off tasks but ensuring system state matches what recipes declare - while you can have it do one off operations it breaks the model it is designed around to an extent.
To do what I believe you are asking in the most Chef way possible would be to write an Ohai plugin which would do the necessary command line calls to gather a list of running SQL Servers on a node and then it would expose that data as Node Attributes which are then searchable on the chef-server via the knife utility. You could have the same plugin output text/csv but that would not be generally useful in the context of chef-client/server. If you really want something that is just a report sent at the end of a run, I suggest looking at Report Handlers.
Hopefully this gives you some resources to start learning how you might accomplish this task,