Automation

 View Only
Expand all | Collapse all

Power CLI script for reports

victorarok

victorarokOct 07, 2011 09:20 AM

  • 1.  Power CLI script for reports

    Posted Oct 07, 2011 07:20 AM

    Hi ,

    Iam new to scripting.Iam trying to create script for getting  allocation and utilization report.This report i need to genarate every 15th and 1st of every month how to genarate in microsoft excel.The out put should be clustername,ESX hostname,VMname,VM IP address,Note,allocated CPU,allocated memory(GB),Allocated disk,Avg CPU usage(%) and Avg Memory usage(%).

    Please guide me to create this script.Iam doing these report  mannuly which is takeing 4 hours of time.I want to learn scripting and automat the report creation.

    Please help me.

    Thanks,

    Victor  



  • 2.  RE: Power CLI script for reports

    Posted Oct 07, 2011 07:43 AM

    Hi Victor,

    You can try something like this

    foreach($cluster in Get-Cluster ){
        Get-VM -Location $cluster |
        select @{N="Cluster";E={$cluster.Name}},
            @{N="ESX Hostname";E={$_.Host.Name}},
            @{N="VM Name";E={$_.Name}},
            @{N="VM IP address";E={[string]::Join(',',$_.Guest.IPAddress)}},
            @{N="Note";E={$_.Description}},
            @{N="Allocated CPU";E={$_.NumCPU}},
            @{N="Allocated Memory (GB)";E={[math]::Round($_.MemoryMB/1KB,1)}},
            @{N="Allocated Disk (GB)";E={[math]::Round(($_.HardDisks | Measure-Object -Sum -Property CapacityKB).Sum/1MB,1)}},
            @{N="Used Disk (GB)";E={[math]::Round($_.UsedSpaceGB,1)}},
            @{N="Avg CPU Usage (%)";E={(Get-Stat -Entity $_ -Stat cpu.usage.average -Realtime -MaxSamples 1).Value}},
            @{N="Avg Memory Usage (%)";E={(Get-Stat -Entity $_ -Stat mem.usage.average -Realtime -MaxSamples 1).Value}}
    }

    Let me know if you need more info on any of the calculated properties.



  • 3.  RE: Power CLI script for reports

    Posted Oct 07, 2011 08:39 AM

    Hi LucD,

    Thankyou it is good script.Thankyou for helping me...

    I have one more qury...

    Please Guide me how to export this into microsoft excel sheet and also how to generate report according to date.

    Eg:- I want to generate avg cpu ,avg memory and diskused from 1st september 2011 to 15th september 2011.

    How to do this.Please guide me.



  • 4.  RE: Power CLI script for reports

    Posted Oct 07, 2011 09:03 AM

    A few minor changes to the original script.

    • we store the results in a variable ($report) and export the contents of that variable to a CSV file
    • for each VM we get the statistics (in 1 call to minimize the runtime of the script)
    • for CPU and Memory we extract the metric we are interested in and calculate the average

    $start = [datetime]"1 sep 2011" 
    $finish
    = $start.AddDays(14) $report = @() foreach($cluster in Get-Cluster)){     foreach($vm in (Get-VM -Location $cluster)){         $stats = Get-Stat -Entity $vm -Start $start -Finish $finish -Stat "cpu.usage.average","mem.usage.average" -ErrorAction SilentlyContinue
           
    $report += select -InputObject $vm -Property @{N="Cluster";E={$cluster.Name}},             @{N="ESX Hostname";E={$_.Host.Name}},             @{N="VM Name";E={$_.Name}},             @{N="VM IP address";E={[string]::Join(',',$_.Guest.IPAddress)}},             @{N="Note";E={$_.Description}},             @{N="Allocated CPU";E={$_.NumCPU}},             @{N="Allocated Memory (GB)";E={[math]::Round($_.MemoryMB/1KB,1)}},             @{N="Allocated Disk (GB)";E={[math]::Round(($_.HardDisks | Measure-Object -Sum -Property CapacityKB).Sum/1MB,1)}},             @{N="Used Disk (GB)";E={[math]::Round($_.UsedSpaceGB,1)}},             @{N="Avg CPU Usage (%)";E={[math]::Round(($stats | where {$_.MetricId -eq "cpu.usage.average"} | Measure-Object -Property Value -Average).Average,1)}},             @{N="Avg Memory Usage (%)";E={[math]::Round(($stats | where {$_.MetricId -eq "mem.usage.average"} | Measure-Object -Property Value -Average).Average,1)}}     } } $report | Export-Csv "C:\report.csv" -NoTypeInformation -UseCulture


  • 5.  RE: Power CLI script for reports

    Posted Oct 07, 2011 09:20 AM

    Iam getting error.Please find attached error 



  • 6.  RE: Power CLI script for reports
    Best Answer

    Posted Oct 07, 2011 09:25 AM

    Looks like you miss the first line that says

    $start = [datetime]"1 sep 2011" 

    Perhaps something went wrong in the copy/paste.

    I attached the script in any case.



  • 7.  RE: Power CLI script for reports

    Posted Oct 07, 2011 09:39 AM

    Hi LucD,

    Thankyou for the script it worked for me.

    I want to learn powercli scripting can you please share any tutorials or tutorial link.

    Thankyou for helping me.I will try this production environment .

    Victor



  • 8.  RE: Power CLI script for reports

    Posted Oct 07, 2011 09:42 AM

    You can find many learning resources in the My PS Library post.



  • 9.  RE: Power CLI script for reports

    Posted Oct 07, 2011 10:35 AM

    Hi Luc,

    sorry to trouble you .I have one more requirement can help me.

    for the same script I want to get OS type,peek cpu,peek memory and Datastore name attached to VM.

    the output will like

    clustername,ESXHostname,VMname,VMIP address,Note,OS Type,Allocated cpu,Allocated memory(GB),Allocated disk,Used disk(GB),Datastorename,Avg CPU usage(%),Avg Memory Usage(%),Peek CPU(%),peek memory(%)

    Please gudie me on this.

    Thankyou,

    Victor



  • 10.  RE: Power CLI script for reports

    Posted Oct 07, 2011 01:25 PM

    Try this

    $start = [datetime]"1 sep 2011" 
    $finish
    = $start.AddDays(14)

    $metrics = "cpu.usage.average","mem.usage.average"
    $report = @()
    foreach($cluster in Get-Cluster ){
       
    foreach($vm in (Get-VM -Location $cluster)){
           
    $stats = Get-Stat -Entity $vm -Start $start -Finish $finish -Stat $metrics -ErrorAction SilentlyContinue
           
    $report += select -InputObject $vm -Property @{N="Cluster";E={$cluster.Name}},
                @{N
    ="ESX Hostname";E={$_.Host.Name}},
                @{N
    ="VM Name";E={$_.Name}},
                @{N
    ="VM IP address";E={[string]::Join(',',$_.Guest.IPAddress)}},
                @{N
    ="Note";E={$_.Description}},
                @{N
    ="Allocated CPU";E={$_.NumCPU}},
                @{N
    ="Allocated Memory (GB)";E={[math]::Round($_.MemoryMB/1KB,1)}},
                @{N
    ="Allocated Disk (GB)";E={[math]::Round(($_.HardDisks | Measure-Object -Sum -Property CapacityKB).Sum/1MB,1)}},
                @{N
    ="Datastore Name";E={[string]::Join(',',($_.HardDisks | %{$_.Filename.Split(']')[0].TrimStart('[')}))}},
                @{N
    ="Used Disk (GB)";E={[math]::Round($_.UsedSpaceGB,1)}},
                @{N
    ="Avg CPU Usage (%)";E={[math]::Round(($stats | where {$_.MetricId -eq "cpu.usage.average"} | Measure-Object -Property Value -Average).Average,1)}},
                @{N
    ="Avg Memory Usage (%)";E={[math]::Round(($stats | where {$_.MetricId -eq "mem.usage.average"} | Measure-Object -Property Value -Average).Average,1)}},
                @{N
    ="Max CPU Usage (%)";E={[math]::Round(($stats | where {$_.MetricId -eq "cpu.usage.average"} | Measure-Object -Property Value -Maximum).Maximum,1)}},
                @{N
    ="Max Memory Usage (%)";E={[math]::Round(($stats | where {$_.MetricId -eq "mem.usage.average"} | Measure-Object -Property Value -Maximum).Maximum,1)}}
        }
    }

    $report | Export-Csv "C:\report.csv" -NoTypeInformation -UseCulture


  • 11.  RE: Power CLI script for reports

    Posted Oct 10, 2011 07:08 AM

    Hi Luc,

    Getting below error.

    Get-Cluster : 10/10/2011 12:37:49 PM    Get-Cluster        Cluster with name 'XYZ' was not found, using the specified filter(s
    At E:\tes1.ps1:6 char:32
    + foreach($cluster in Get-Cluster  <<<< XYZ){
    Export-Csv : A parameter cannot be found that matches parameter name 'UseCulture'.
    At E:\tes1.ps1:25 char:67
    + $report | Export-Csv "C:\report.csv" -NoTypeInformation -UseCulture <<<<

    Do i need to remove XYZ ?Please advice.

    Thanks,

    Victor



  • 12.  RE: Power CLI script for reports

    Posted Oct 10, 2011 08:48 AM

    Oops, that was my test cluster.

    The code above is corrected.



  • 13.  RE: Power CLI script for reports

    Posted Oct 10, 2011 09:01 AM

    please find attached error 



  • 14.  RE: Power CLI script for reports

    Posted Oct 10, 2011 09:08 AM

    Just tested the script and for me it works.

    Would you mind attaching the script as a file, so I can have a look ?

    Something might have gone wrong during the copy/paste.



  • 15.  RE: Power CLI script for reports

    Posted Oct 10, 2011 11:48 AM

    Please find the script .



  • 16.  RE: Power CLI script for reports

    Posted Oct 10, 2011 01:36 PM

    There was a <CR><LF> missing.

    Try the attached copy.



  • 17.  RE: Power CLI script for reports

    Posted Oct 11, 2011 12:51 PM

    Thanks Lucd it is working fine.Thankyou for helping me.

    Iam not getting OS used in VM how to add this in script.



  • 18.  RE: Power CLI script for reports

    Posted Oct 11, 2011 01:23 PM

    Hello, victorarok-

    To include the guest OS in the output, you could add a line in the Select statement that LucD set you up with.  The line is a calculated property for the OS Full Name.  Add the following line to the calculated properties section (where all of the "@{N="<something>";E=..." lines are):

    ...
    @{N
    ="OSFullName";E={$_.Guest.OSFullName}},
    ...

    Note:  this property relies on VMware Tools to get the info, so if tools are not running in a given VM, this property will be empty.



  • 19.  RE: Power CLI script for reports

    Posted Oct 12, 2011 05:01 AM

    Thankyou



  • 20.  RE: Power CLI script for reports

    Posted Oct 20, 2011 06:37 PM

    How would one run this script in only two datacenters?



  • 21.  RE: Power CLI script for reports

    Posted Jul 20, 2012 12:46 PM

    To run Luc's script in only two datacenters, you can change the line:

    foreach($cluster in Get-Cluster){
    
    

    into:

    foreach($cluster in (Get-datacenter -Name "Datacenter1","Datacenter2" | Get-Cluster)){
    
    



  • 22.  RE: Power CLI script for reports

    Posted Jul 20, 2012 12:23 PM

    hi i have an requirment for  I need to show in my values in Ghz instad on Mhz

    for eg get-vlsuter "cluster name " | get-vm-host

    it will show all values in Mhz & MB .. I want all values in Ghz & gb how I do that

    CpuUsage CpuTotal  Memory  Memory
         Mhz      Mhz UsageMB TotalMB
    -------- -------- ------- -------

    Rgds..HeM



  • 23.  RE: Power CLI script for reports

    Posted Jul 20, 2012 12:41 PM

    To convert the values to Ghz instead of Mhz you can use calculated properties. Like this:

    Get-Cluster "cluster name " |
    Get-VMHost |
    Select-Object -Property Name,
    @{Name="CpuTotalGhz";Expression={$_.CpuTotalMhz/1000}},
    @{Name="CpuUsageGhz";Expression={$_.CpuUsageMhz/1000}},
    MemoryTotalMB,MemoryUsageMB
    
    

    Regards, Robert