Orchestrator 5.1 REPORT-All VMs in Datacentre and create a CSV

[BWinchell]

Hello,

What I am basically trying to do is create a CSV report of all VMs within the datacentre with various information (VMname, FQDN, IPaddress, Tools status, Datastores, etc...).  The export function of list in the client is inadequate (especially for any KPI report).

Problem: (Workflow is still in the works so actual email send not working and need to clean up code)

I am stuck at the part of creating an array which can be parsed into the CSV correctly.  The 2 ways I have tried will produce a single object report or combine the entire array into a single string (where I am now).  I believe the main problem I have is I need to create a 2D array within my function to push to the final array that is written to CSV.  I basically do not understand how to push my variables while in a loop of an array.

Any help or assistance?

Thanks

B

[Burke-]

Hi there, I did this a couple years ago for a number of things: Virtual Machines, Hosts, Datastores... a whole set of modular workflows. The approach I took was:

• Write a simple workflow that gets the desired data from a single object and output that as a 1 line CSV data string and 1 line CSV string with the headers (makes importing the CSV into XLS nice with header columns)
• Build loop workflow to generate CSV file:
• -- Initialize: Retrieve an array of the object that the first workflow processes, optionally write a header line to the CSV file that is to be created
• -- Run loop: each iteration of the loop adds the CSV single line from first workflow as new line in the CSV file that the loop workflow created
• -- After loop: close out the CSV file and pass as output type MimeAttachment
• Next, I created an e-mail workflow that would call workflow 2 - it ran workflow 2 (which of course ran workflow 1 x number of times), then it took the MimeAttachment output, added that to an e-mail and sent the e-mail.

Now, I could just give you the package but then what fun is that :smileysilly: I'll consider doing it in the near future... until then...

The approach I took above is of course one way, and creating an array then processing the array to generate the CSV is another.

Here's the scriptable task from the "Generate CSV VM Quickstats Report" workflow :

var byteToGiga=1024*1024*1024;
var headerLine = ("VM Name, State, Status, Provisioned Space [GB], Commited Space [GB], CPU Count, NIC Count, Mem Size (MB), Host, Guest OS, Snapshot Count, Ballooned Memory [MB], Compressed Memory [Kb], Consumed Overhead Memory [MB], Guest Memory Usage [MB], Host Memory Usage [MB], Overall CPU Demand [MHz], Overall CPU Usage [MHz], Private Memory [MB], Shared Memory [MB], Swapped Memory [MB], Uptime [sec], Notes, VC UUID, BIOS UUID, IP Address, Tools Status, Tools Version Status, Tools Running Status, EVC, Alarm Actions Enabled?, Active Alarms");

if (vm != null){
    var vmName = vm.name;
    //System.log("Processing VM: "+vmName);
    var alarmActionsEnabled = vm.alarmActionsEnabled;
    if(alarmActionsEnabled){
        var declaredAlarmState = vm.declaredAlarmState; // array of VcAlarmState
        var activeAlarms = "";
        if (declaredAlarmState != null && declaredAlarmState.length > 0){
            // Can process alarms here if needed. See the VcAlarmState object for details
            for each (alarmState in declaredAlarmState){
                //System.log("Key: " + alarmState.key);
                // Gray and Green are unknown and OK, so only get yellow and red:
                if(alarmState.overallStatus.value == "red" || alarmState.overallStatus.value == "yellow"){
                    //if(alarmState.alarm.info.enabled && !alarmState.acknowledged){
                        System.log(alarmState.time+": "+vmName+" "+alarmState.alarm.info.name+" ("+alarmState.overallStatus.value+")");
                        //System.log("Enabled? "+alarmState.alarm.info.enabled);
                        //System.log("Acknowledged? "+alarmState.acknowledged);
                        //System.log("Overall Status: "+alarmState.overallStatus.value);
                        //System.log("Time: "+alarmState.time);
                        if (activeAlarms.length > 1){activeAlarms += ";"}
                        activeAlarms += alarmState.time+": "+alarmState.alarm.info.name+" ("+alarmState.overallStatus.value+")";
                    //}
                }
            }
        }
    }
    var notes = vm.summary.config.annotation;
    if (notes != null){
        //System.log("Notes before: "+notes);
        notes = notes.replace(new RegExp("\"", "g"), "'");
        notes = notes.replace(new RegExp("™", "g"), "(TM)");
        //System.log("Notes after: "+notes);
    }
    var vcUuid = vm.summary.config.instanceUuid;
    var biosUuid = vm.summary.config.uuid;

    var host = vm.summary.runtime.host.name;
    var state = vm.summary.runtime.powerState.value;
    var consolidationNeeded = vm.summary.runtime.consolidationNeeded;
    var evc = vm.summary.runtime.minRequiredEVCModeKey;
    if (evc == null){evc = "N/A";}

    var status = vm.summary.overallStatus.value;

    var committed = vm.summary.storage.committed;
    var uncommitted = vm.summary.storage.uncommitted;
    //System.log("Committed: "+committed);
    //System.log("Uncommitted: "+uncommitted);
    var provisionedSpace = committed + uncommitted;
    provisionedSpace = System.formatNumber(provisionedSpace/byteToGiga,"0.00");
    //System.log("Provisioned: "+provisionedSpace);

    committed = System.formatNumber(committed/byteToGiga,"0.00");
    //System.log("Committed: "+committed+"GB");
    //System.log("provisionsedSpace: "+provisionedSpace+"GB");
    //var datastores = vm.datastore;

    var memSize = vm.config.hardware.memoryMB;
    //memSize = System.formatNumber(memSize/1000,"0.000"); // Display as GB

    var cpuCount = vm.config.hardware.numCPU;
    var devices = vm.config.hardware.device;
    var nicCount = 0;
    for (var i in devices){
        if (devices[i] instanceof VcVirtualE1000 || devices[i] instanceof VcVirtualVmxnet || devices[i] instanceof VcVirtualPCNet32 || devices[i] instanceof VcVirtualVmxnet2 || devices[i] instanceof VcVirtualVmxnet3) {
            nicCount++;
          }
    }

    var guestOS = vm.config.guestFullName;
    var snapInfo = vm.snapshot;
    var snapshotCount = 0;
    if (snapInfo != null){
        var snapshotList = snapInfo.rootSnapshotList;
        if( snapshotList != null){
            snapshotCount = snapshotList.length;
            if (snapshotCount > 0){
                // Insert code here to get more details about snapshots (don't forget to update the header line and outCSV as needed
                /*for each (snap in snapshotCount){
                    snapshotCount++;
                    for each (snapshot in snap.childSnapshot){
                        snapshotCount++
                    }
                }*/
            }
        }
    }

    var quickStats = vm.summary.quickStats; // Get the QuickStats object so we can extract further details:
    var balloonedMemory = quickStats.balloonedMemory; // Size of the ballon driver in the VM, in MB.
    var compressedMemory = quickStats.compressedMemory; // Amount of compressed memory currently consumed by VM, in Kb
    var consumedOverheadMemory = quickStats.consumedOverheadMemory; // Amount of consumed overhead memory in MB for this VM
    var distributedCpuEntitlement = quickStats.distributedCpuEntitlement; // Amount of CPU resources, in MHz, that this VM is entitled to (only valid for VM managed by DRS)
    var distributedMemoryEntitlement = quickStats.distributedMemoryEntitlement; // Amount of memory in MB that this VM is entitled to (only valid for VM managed by DRS)
    var guestMemoryUsage = quickStats.guestMemoryUsage; // Guest memory utilization in MB, also referred to as active guest memory
    var hostMemoryUsage = quickStats.hostMemoryUsage; // Host memory utilization in MB, also referred to as consumed host memory
    var overallCpuDemand = quickStats.overallCpuDemand; // Basic CPU performance stats in MHz. Valid while VM is running
    var overallCpuUsage = quickStats.overallCpuUsage; //  Basic CPU performance stats in MHz. Valid while VM is running
    var privateMemory = quickStats.privateMemory; // Portion of memory in MB that is granted to this VM from non-shared host memory
    var sharedMemory = quickStats.sharedMemory; // Portion of memory in MB that is granted to this VM from host memory that is shared between VMs
    var swappedMemory = quickStats.swappedMemory; // Portion of memory in MB that is granted to this VM from the host's swap space. Is sign of memory pressure on the host
    var uptimeSeconds = quickStats.uptimeSeconds; // The system uptime of the VM in seconds

    var guestSummary = vm.summary.guest;
    var ipAddress = guestSummary.ipAddress;
    var toolsStatus = guestSummary.toolsStatus.value;
    var toolsVersionStatus = guestSummary.toolsVersionStatus;
    var toolsRunningStatus = guestSummary.toolsRunningStatus;

    var outCSV = '"' + vmName + '","' + consolidationNeeded +'","'+state + '","' + status + '","' + provisionedSpace + '","' + committed + '","' + cpuCount + '","' + nicCount + '","' + memSize + '","' + host + '","' + guestOS+ '","' + snapshotCount + '","' + balloonedMemory + '","' + compressedMemory + '","' + consumedOverheadMemory + '","' + guestMemoryUsage + '","' + hostMemoryUsage + '","' + overallCpuDemand + '","' + overallCpuUsage + '","' + privateMemory + '","' + sharedMemory + '","' + swappedMemory + '","' + uptimeSeconds + '","' + notes + '","' + vcUuid + '","' + biosUuid + '","' + ipAddress + '","' + toolsStatus + '","' + toolsVersionStatus + '","' + toolsRunningStatus + '","' + evc + '","' + alarmActionsEnabled + '","'  + activeAlarms +'"';

    System.log("VM Name, Consolidation Needed, State, Status, Provisioned Space [GB], Commited Space [GB], CPU Count, NIC Count, Mem Size (MB), Host, Guest OS, Snapshot Count, Ballooned Memory [MB], Compressed Memory [Kb], Consumed Overhead Memory [MB], Guest Memory Usage [MB], Host Memory Usage [MB], Overall CPU Demand [MHz], Overall CPU Usage [MHz], Private Memory [MB], Shared Memory [MB], Swapped Memory [MB], Uptime [sec], Notes, VC UUID, BIOS UUID, IP Address, Tools Status, Tools Version Status, Tools Running Status, EVC, Alarm Actions Enabled?, Active Alarms");
    System.log(outCSV);
}

function getChildCount(snapshot){
    var counter = 0;

    return counter;
}

Note that some of that code starts to drill down into some info but ends up not getting used... Like I said, this code is a couple years old and did what I needed back when I wrote it.. it could definitely use some clean-up, that's why I haven't quite released the package yet.

[Burke-]

Hmm... looking at your code in CreateCSVfile has me quite confused.. I don't ever claim to be a JavaScript expert, but you declared your function like:

function getVmInf(){
// some stuff in here
return results;
}

So that function from my understanding does NOT take parameters... yet you call it within a loop like:

getVmInfo((results),vm);

I am really not being an ass, I just don't get the syntax - is there a URL you can point me to that explains how/why this should work?

If I were writing your function and calling it, I would do something more along these lines:

function getVmInfo(vm){
// do some cool stuff here
return vmCSVLine;
}

I would then write my loop along these lines:

// code to initialize CSV file here
for each (vm in vmsFiltered){
csvFile.write(getVmInfo(vm));
// or like this:
// var vmData = getVmInfo(vm);
// csvFile.write(vmData);
}
// code to close out CSV file

[BWinchell]

Hi Burke,

I don't even know enough javascript to be dangerous (I am a network engineer and dabble in Orchestrator).  So the info I say now might be completely wrong but, hopefully someone who really knows chimes in.

I found an article on StackOverflow (I need to try to find again) that mentioned how to declare an array outside a function, write to it within the function, and return the results.  If you declare the array within the loop, every cycle will overwrite the array.If this is best practice, or script illegal, or not best practice, I honestly do not know.

I will try your suggestions and try to find that link again.

Thanks

[BWinchell]

I believe this is what lead me to that code.

For each in an array. How to do that in JavaScript? - Stack Overflow

[Burke-]

LOL, okay then... just making sure I didn't totally miss something here And that link you provided doesn't appear to provide such an example... No worries, possibly just a misunderstanding...

Anyway, how did things turn out after trying my suggestion? Are you back on track now?

[BWinchell]

Hi Burke,

I have not had a chance to spend time on this (preparing for a DC move this weekend...fun ).  I will get back to this early next week and let you know how I made out.

Thanks

[BWinchell]

Hello,

I got the basics working (did basically what you did by writing the array string after each object in a loop).  I am stuck on this particular issue (trying to cleanup my code).

What I have:

//Get VMFolder
//This needs to be written better.  Cannot figure out how yet(small detail)
  folder = vm.parent;
  folderName = folder.name;
  folderPathL1 = folder.parent.parent.parent.parent.parent.parent.name;
  folderPathL2 = folder.parent.parent.parent.parent.parent.name;
  folderPathL3 = folder.parent.parent.parent.parent.name;
  folderPathL4 = folder.parent.parent.parent.name;
  folderPathL5 = folder.parent.parent.name;
  folderPathL6 = folder.parent.name;
  var folderPath = (folderPathL1 + "/" + folderPathL2 +"/" + folderPathL3
  + "/" + folderPathL4 + "/" + folderPathL5 + "/" + folderPathL6 + "/" + folderName);
  System.log("folderPath: " + folderPath);

What I am looking for(plain English):

//Find the folder the VM resides in

folder = vm.parent;

folderName = folder.name;

//Find the next folder, if exists

if (typeof folder.parent === 'undefined) {

quit;

}

I have no idea how to get this piece started.  Any help or assistance?

Thanks

[Burke-]

Couldn't resist looking this one over....

System.log("Checking VM: "+vm.name);
var parent = vm.parent;
var parentNames = new Array();
var vmPathName = "";
while (parent instanceof VcFolder){
    parentNames.push(parent.name);
    parent = parent.parent;
}

for (i=(parentNames.length-1); i>=0; i--){
//vmPathName += "/"+parentNames.pop();
    vmPathName += "/"+parentNames[i];
}
System.log("VM Path: "+vmPathName);

Output:

[2014-03-05 16:01:37.364] [I] Checking VM: TinyCore (a8f19959-7b6d-44b9-85df-64b2010dd41e)
[2014-03-05 16:01:37.366] [I] VM Path: /vm/uslab-vcd/vcoteam (cdfedc9a-5986-4a5d-a76b-81fcf4b98896)/vcoteam-orgvdc (d46837d4-9ca9-4744-bf18-2d3e9adcb4cb)/TinyCore (5f63193f-7a88-4e74-8407-a8cb230c4bcf)

Message was edited by: Burke Azbill - changed to forward slash as in your code sample

[BWinchell]

Hello,

With a lot of your help (and original code), I was able to get the basic report working.  The email piece is still broken (I will try to work on this over the next few days).  Hopefully I will also be able to make the report dynamic as what categories you want to see in the report in the next version.

If anyone would like to consolidate the code and cleanup further, please do and re-post.

Thanks

B

[Burke- · Accepted solution]

:smileycool:BOOM!

Added some comments, removed the hard-coded path to the temp csv file, removed the hard coded port 25 for the SMTP -- the e-mail settings should be obtained from the MAIL plug-in configuration. And, fixed / confirmed that the workflow now includes attachment for the e-mail and completes successfully !

[BWinchell]

Hello Burke,

That was a huge help (I was on a much more difficult path to accomplish that).

I added the ability now to customize the report for each category to be included in the original report.

on a side note:

I found some sort of bug concerning the "template" tag.  I added in the code to filter any VM that was considered a template.  The problem was I had templates (as far as vCenter was concerned - system logs would show the template flag as true) but still showed up in the report [which causes errors].  If I went to those templates, converted to VM and back to template, then they would be excluded as expected.

The only thing I can think of is these templates were originally created in 4.1 and moved to 5.1 (register the .vmtx with vCenter).  I will have to investigate further.

If anyone else can try this, please let me know your results.

Again, since I am not a programmer, I am sure there is a way to improve on the code.  Please feel free to make adjustments and re-post.

Thanks for all you assistance.

B

Message was edited by: BWinchell My customizable report did not have the ability to find the resource pool.  This version(3.0.2) has that ability added.

[ware06]

can you share the steps, how to import the workflow to vRO8.0 and validate ? 

I'm new to this and would need your support to understand.

[imtrinity94]

@ware06 vRO 8.x lost the ability to import Workflows. Only packages can be imported now. However, @xian_ has developed a workaround. You can import the desired workflow using this method https://kuklis.github.io/cma/post/vro8-import-workflow/

Just remember to use vRO 8.5 or later.

[ware06]

Thanks @imtrinity94, i'm able to import the workflow using the steps shared.

However, workflow failed at "CreateCSV" file with the below error... do i need to set some permisison on /var folder or should the workflow be ran using "administrator" user ?

[imtrinity94]

Could you try to add that location to js-io-rights.conf using this link

https://docs.vmware.com/en/vRealize-Orchestrator/8.11/com.vmware.vrealize.orchestrator-install-confi...