Community

Exporting Get-NaEfficiency output to an Excel workbook

by NetApp Employee on ‎2011-07-29 05:31 AM - edited on ‎2014-09-25 01:06 PM by Community Manager

The Get-NaEfficiency cmdlet introduced in DataONTAP PowerShell Toolkit 1.5 provides an easy mechanism for gathering efficiency information.  Attached to this post is a script, Export-NaEfficiencyXls.ps1, that exports the data gathered by the Get-NaEfficiency cmdlet to an Excel workbook, complete with a handful of graphical representations of the data.

 

Usage:

.\Export-NaEfficiencyXls [-Filename <String>] [-Unit <Int>] [-Controller <NaController>] [-Quiet]

 

The filename parameter (optional) is the name to give the exported workbook.  If no name is provided, the workbook is saved as "ControllerName_na_efficiency_TimeStamp.xlsx".

The Unit parameter (optional) defines the units to use when displaying values in bytes.  Options: 1KB, 1MB, 1GB, 1TB, 1PB.  The default value is 1MB.

The Controller parameter (optional) allows you to specify which NaController object to run Get-NaEfficiency against.  The default value is the value saved in $global:CurrentNaController.

The Quiet switch allows you to prevent the launching of Excel at the script's completion.

 

The script creates four graphs in the "Charts" worksheet of the exported workbook.  The first chart is the "Aggregate Space" chart:

 

This chart provides a quick overview of how full your aggregates are.

 

The second chart included is the "Aggregate Snapshot Returns" chart:

This chart shows the snapshot returns of all the aggregates on the controller.  The snapshot returns value is estimated using the follow calculation:  Used Space - (Snapshot Used Space / # Of Snapshots).  We divide the snapshot used space by the number of snapshots to get an accurate savings calculation from aggregates with long-running snapshots that effectively consume more space than the space consumed by the data.

 

The third chart is the "Volume Efficiency" chart:

This chart shows the used percentage and the efficiency percentage of each volume contained on the controller.  The efficiency percentage is calculated by adding the efficiency returns from snapshots, deduplication, volume compression, and FlexClone Volumes to the used space and dividing by the capacity.

 

The final chart is the "Volume Efficiency Breakdown" chart:

This chart provides a visualization of the efficiency returns.  The used space is stacked with the returns from deduplication, snapshots, volume compression, and FlexClone Volumes so the Effective Used value of each volume is shown.

 

Feel free to modify the script to fit your needs, just be sure to share any cool changes with the community

 

EDIT 7/29/2011:  Posting updated script, special thanks to Glenn Sizemore for cleaning it up!

Comments
KEITH_TRAN on ‎2011-08-03 08:39 AM

Pre connect w/ Get-nacontroller

run script.

Getting errors running this:

Get-NaEfficiency : Could not connect to NAFiler01 on port 443 for protocol HTTPS.

At C:\excelFilerreport.ps1:70 char:32

+     $efficiency = Get-NaEfficiency <<<<  -Aggregate * -recurse -Controller $Controller

    + CategoryInfo          : InvalidOperation: (NAFiler01 :NaController) [Get-NaEfficiency], NaConnectionException

    + FullyQualifiedErrorId : ApiException,DataONTAP.PowerShell.SDK.Cmdlets.Toolkit.Efficiency.GetNaEfficiency

Running the command manually:

Get-NaEfficiency

Name                     Capacity         Used         Free    SnapUsage      Reserve      Returns EfficiencyPercentage

----                     --------         ----         ----    ---------      -------      ------- --------------------

aggr6123     192.0 GB       9.3 GB     182.7 GB     483.4 MB            0       9.3 GB                   9%

agre1231      192.0 GB       7.2 GB     184.8 GB     345.0 MB            0       7.2 GB                   7%

Get-NaEfficiency : Could not connect to NAFiler01 on port 443 for protocol HTTPS.

At line:1 char:17

+ Get-NaEfficiency <<<<

    + CategoryInfo          : InvalidOperation: (NAFiler01 :NaController) [Get-NaEfficiency], NaConnectionException

    + FullyQualifiedErrorId : ApiException,DataONTAP.PowerShell.SDK.Cmdlets.Toolkit.Efficiency.GetNaEfficiency

aggr656567     5.0 TB       3.7 TB       1.3 TB            0            0            0                  74%

Get-NaEfficiency : Could not connect to NAFiler01 on port 443 for protocol HTTPS.

At line:1 char:17

+ Get-NaEfficiency <<<<

    + CategoryInfo          : InvalidOperation: (NAFiler01 :NaController) [Get-NaEfficiency], NaConnectionException

    + FullyQualifiedErrorId : ApiException,DataONTAP.PowerShell.SDK.Cmdlets.Toolkit.Efficiency.GetNaEfficiency

aggr6123123     7.2 TB       5.1 TB       2.1 TB            0            0            0                  70%

Get-NaEfficiency : Could not connect to NAFiler01 on port 443 for protocol HTTPS.

At line:1 char:17

+ Get-NaEfficiency <<<<

    + CategoryInfo          : InvalidOperation: (NAFiler01 :NaController) [Get-NaEfficiency], NaConnectionException

    + FullyQualifiedErrorId : ApiException,DataONTAP.PowerShell.SDK.Cmdlets.Toolkit.Efficiency.GetNaEfficiency

aggr6111     5.5 TB       3.7 TB       1.8 TB            0            0            0                  67%

aggr6123     5.8 TB     963.7 GB       4.9 TB     168.0 KB            0     963.7 GB                  32%

Get-NaEfficiency : Could not connect to NAFiler01 on port 443 for protocol HTTPS.

At line:1 char:17

+ Get-NaEfficiency <<<<

    + CategoryInfo          : InvalidOperation: (NAFiler01 :NaController) [Get-NaEfficiency], NaConnectionException

    + FullyQualifiedErrorId : ApiException,DataONTAP.PowerShell.SDK.Cmdlets.Toolkit.Efficiency.GetNaEfficiency

aggr7896     7.2 TB       4.3 TB       2.8 TB            0            0            0                  60%

aggr67891     1.2 TB      47.4 GB       1.2 TB      98.8 MB            0      47.4 GB                   7%

aggr67892     52.0 GB      29.5 GB      22.5 GB      17.8 MB            0      29.5 GB                 113%

NetApp Employee on ‎2011-08-03 01:28 PM

Hi Keith,

It appears the connection is timing out.  The cmdlet executes several ZAPI calls, some of which can be quite time-consuming.  I would suggest bumping up the timeout value on the controller (the default in toolkit 1.5 is 60 seconds):

$global:CurrentNaController.TimeoutMsec = 600000

This should set the timeout to 10 minutes and (hopefully!) get rid of the error.

Hope that helps!

-Steven

bkalyvas1 on ‎2011-08-23 08:25 AM

This is a great script!  Thanks for posting this one!

-Bill    

on ‎2011-12-12 04:05 AM

I am relatively new to PS but it appears the cmdlet get-naefficiency has been taken out of the latest release of the toolkit. Is this the case?

on ‎2011-12-12 07:36 AM

Version 1.6 (which I believe is the latest) has it. 

Check your version by running Get-natoolkitversion. 

1.6 is here:  https://communities.netapp.com/docs/DOC-6138

on ‎2011-12-13 12:17 AM

Hi,

Thanks for getting back. The cmdlet you mentioned did identify I was

running an older version of the module - it was stored in a location that

was taking precedence over where I had put version 1.6. That is now fine

and I can see the get-naefficiency cmdlet. However, when I run the script

I am now getting the following error. Any ideas? Thanks in advance.

.\Export-NaEfficiencyXls.ps1 -unit 1GB -Controller xxxxxxxxxxx

Get-NaEfficiency : API invoke failed.

At C:\Users\jwhitw2\Documents\Scripts\Export-NaEfficiencyXls.ps1:70

char:32

+ $efficiency = Get-NaEfficiency <<<< -Aggregate * -recurse

-Controller $Controller

+ CategoryInfo : InvalidOperation:

(ukbrnseries01a:NaController) , NaException

+ FullyQualifiedErrorId :

ApiException,DataONTAP.PowerShell.SDK.Cmdlets.Toolkit.Efficiency.GetNaEfficiency

Jon

Jon Whitwham

IT Infrastructure Manager

Hallmark Cards

Office: 01274 252028

Email: jwhitw2@hallmark-uk.com

From: "bsti@plex.com"

<xdl-communities@netapp-community.hosted.jivesoftware.com>

To: Jon Whitwham <jwhitw2@hallmark-uk.com>

Date: 12/12/2011 15:41

Subject: Re: - Exporting

Get-NaEfficiency output to an Excel workbook

Exporting Get-NaEfficiency output to an Excel workbook

new comment by bsti@plex.com View all comments on this document

Version 1.6 (which I believe is the latest) has it.

Check your version by running Get-natoolkitversion.

1.6 is here: https://communities.netapp.com/docs/DOC-6138

Reply to this email to respond to bsti@plex.com's comment.

Registered Office: Bingley Road, Bradford, BD9 6SD

Registered in England no. 03414540

The information contained in this e-mail and any accompanying documents

may contain information that is confidential or otherwise protected from

disclosure. If you are not the intended recipient of this message, or if

this message has been addressed to you in error, please immediately

alert the sender by reply e-mail and then delete this message, including

any attachments. Any dissemination, distribution or other use of the

contents of this message by anyone other than the intended recipient is

strictly prohibited.

Unless specifically stated in this e mail, the agreement and content

of this correspondence is subject to contract.

No contract is implied by this correspondence, unless specifically

stated as a contract (purchase order or sales order)

Think of the environment; please don't print this e-mail unless you

really need to

on ‎2012-03-19 09:56 AM

This is a fantastic concept, and script. I ran across this by pure accident while looking for something else, but am in need of something EXACTLY like this to auto-generate charts that I have been doing by hand for VMWare inventory exports on NetApp storage. I have re-purposed this script for my own needs, but it fails generating the chart-

Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"

At H:\dev\PowerShell\Generate-VMChart.ps1:157 char:36

+     $range = $workbook.Worksheets.Item <<<< ("Aggregates").range("'VMs'!`$A`$1:`$A`$$VMEnd,'Aggregates'!`$D`$1:`$D`$$VMEnd")

    + CategoryInfo          : NotSpecified: (Smiley Happy [], GetValueInvocationException

    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

Exception calling "SetSourceData" with "2" argument(s): "Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"

At H:\dev\PowerShell\Generate-VMChart.ps1:164 char:22

+     $chart.SetSourceData <<<< ($range, 2)

    + CategoryInfo          : NotSpecified: (Smiley Happy [], MethodInvocationException

    + FullyQualifiedErrorId : ComMethodTargetInvocation

and it would be beautiful if I didn't have to do by hand-

2.PNG

vm1.png

Anyways, here is my first attempt at using the Excel com object-

http://dl.dropbox.com/u/35757698/Generate-VMChart.ps1

If anyone knows what I am doing wrong I'd appreciate it

NetApp Employee on ‎2012-03-19 11:51 AM

I took a quick look over the script, and it looks like it's failing on the $workbook.worksheets.Item("Aggregate") call.  I think the issue is the worksheet you've created is named "VMs", so it should be $workbook.worksheets.Item("VMs").  Try replacing that line with:

$range = $workbook.Worksheets.Item("VMs").range("'VMs'!`$A`$1:`$A`$$VMEnd,'VMs'!`$D`$1:`$D`$$VMEnd")

I suspect the second error will fix itself after this change.

I hope that helps!

-Steven

on ‎2012-03-19 03:08 PM

Good catch, thanks. I thought I had tried that with the remarked line above. Anyways, it generated the chart without error, but the chart is a mess. How did you determine the .range syntax? In excel a valid chart looks like-

=SERIES(Sheet5!$C$1,Sheet5!$A$2:$A$29,Sheet5!$C$2:$C$29,2)

=SERIES(Sheet5!$D$1,Sheet5!$A$2:$A$29,Sheet5!$D$2:$D$29,3)

But that's off a pivot chart, if I try to just generate a chart, I run into 'the maximum number of data series per chart is 255'

I think I need to generate the totals that excel is doing for me first, and then generate the chart. More to follow.

on ‎2012-11-02 03:04 AM

Hello

Someone could help me, when I run the script, I always get the same error.

I see that there is a problem to have to draw the graph

PS C:\NetAppToolKit> .\Export-NaEfficiencyXls.ps1
Excepción al obtener "Range": "Excepción de HRESULT: 0x800A03EC"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 254 Carácter: 56
+     $range = $workbook.Worksheets.Item("Aggregates").range <<<< ("'Aggregates'!`$A`$1:`$A`$$aggrEnd,'Aggregates'!`$C`
$1:`$D`$$aggrEnd")
    + CategoryInfo          : NotSpecified: (Smiley Happy [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

Excepción al llamar a "SetSourceData" con los argumentos "2": "Los tipos no coinciden. (Excepción de HRESULT: 0x8002000
5 (DISP_E_TYPEMISMATCH))"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 261 Carácter: 22
+     $chart.SetSourceData <<<< ($range, 2)
    + CategoryInfo          : NotSpecified: (Smiley Happy [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Excepción al obtener "Range": "Excepción de HRESULT: 0x800A03EC"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 273 Carácter: 56
+     $range = $workbook.Worksheets.Item("Aggregates").range <<<< ("'Aggregates'!`$A`$1:`$A`$$aggrEnd,'Aggregates'!`$L`
$1:`$L`$$aggrEnd")
    + CategoryInfo          : NotSpecified: (Smiley Happy [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

Excepción al llamar a "SetSourceData" con los argumentos "2": "Los tipos no coinciden. (Excepción de HRESULT: 0x8002000
5 (DISP_E_TYPEMISMATCH))"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 279 Carácter: 22
+     $chart.SetSourceData <<<< ($range, 2)
    + CategoryInfo          : NotSpecified: (Smiley Happy [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Excepción al obtener "Range": "Excepción de HRESULT: 0x800A03EC"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 292 Carácter: 53
+     $range = $workbook.Worksheets.Item("Volumes").range <<<< ("'Volumes'!`$A`$1:`$A`$$volEnd,'Volumes'!`$Y`$1:`$Y`$$v
olEnd,'Volumes'!`$E`$1:`$E`$$volEnd")
    + CategoryInfo          : NotSpecified: (Smiley Happy [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

Excepción al llamar a "SetSourceData" con los argumentos "2": "Los tipos no coinciden. (Excepción de HRESULT: 0x8002000
5 (DISP_E_TYPEMISMATCH))"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 299 Carácter: 22
+     $chart.SetSourceData <<<< ($range, 2)
    + CategoryInfo          : NotSpecified: (Smiley Happy [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Excepción al obtener "Range": "Excepción de HRESULT: 0x800A03EC"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 310 Carácter: 53
+     $range = $workbook.Worksheets.Item("Volumes").range <<<< ("'Volumes'!`$A`$1:`$A`$$volEnd,'Volumes'!`$F`$1:`$F`$$v
olEnd,'Volumes'!`$S`$1:`$S`$$volEnd,'Volumes'!`$T`$1:`$T`$$volEnd,'Volumes'!`$U`$1:`$U`$$volEnd,'Volumes'!`$V`$1:`$V`$$
volEnd")
    + CategoryInfo          : NotSpecified: (Smiley Happy [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

Excepción al llamar a "SetSourceData" con los argumentos "2": "Los tipos no coinciden. (Excepción de HRESULT: 0x8002000
5 (DISP_E_TYPEMISMATCH))"
En C:\NetAppToolKit\Export-NaEfficiencyXls.ps1: 316 Carácter: 22
+     $chart.SetSourceData <<<< ($range, 2)
    + CategoryInfo          : NotSpecified: (Smiley Happy [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

TKS

on ‎2012-11-19 02:55 AM

I have now got this to work. The problem many of us have, have to do with Excel in different languages i think, I run Excel in swedish for example. I changed all commas to an semi colon in those affected rows. Because different areas/range in swedish excel graphs are separated by semi colon, not commas.

From: $range = $workbook.Worksheets.Item("Aggregates").range("'Aggregates'!`$A`$1:`$A`$$aggrEnd,'Aggregates'!`$C`$1:`$D`$$aggrEnd")

To: $range = $workbook.Worksheets.Item("Aggregates").range("'Aggregates'!`$A`$1:`$A`$$aggrEnd;'Aggregates'!`$C`$1:`$D`$$aggrEnd")

To clearify where to change: $range = $workbook.Worksheets.Item("Aggregates").range("'Aggregates'!`$A`$1:`$A`$$aggrEnd;'Aggregates'!`$C`$1:`$D`$$aggrEnd")

Hope this help!

Great script btw!

on ‎2012-12-12 03:23 AM

Catherina I love you!

That was exact my problem! I used the script with German Excel and I got the same errors like you.

Then I replaced the commas with semicolons and voila now it works perfect!

Script author, please release a fixed version of your great script for non English users

Thanx a lot.

PS: I tested the script with toolkit version 2.2

on ‎2013-01-11 12:05 PM

Hi,

I am in an air-gap environment with no ms-office product installed.

Any chance re-writing for CSV only or comma delimited will work?

$excel = new-object -comobject excel.application.

I get the error "New-Object : Cannot load COM type Excel.Application."

You cannot instantiate an office object without having office installed.

Thanks

Jeff

Warning!

This NetApp Community is public and open website that is indexed by search engines such as Google. Participation in the NetApp Community is voluntary. All content posted on the NetApp Community is publicly viewable and available. This includes the rich text editor which is not encrypted for https.

In accordance to our Code of Conduct and Community Terms of Use DO NOT post or attach the following:

  • Software files (compressed or uncompressed)
  • Files that require an End User License Agreement (EULA)
  • Confidential information
  • Personal data you do not want publicly available
  • Another’s personally identifiable information
  • Copyrighted materials without the permission of the copyright owner

Files and content that do not abide by the Community Terms of Use or Code of Conduct will be removed. Continued non-compliance may result in NetApp Community account restrictions or termination.