Exporting Virtual Infrastructure Information to MS Word

The below is a script that will export various information into Microsoft Word using PowerShell and the VI Toolkit, this is completely risk free and does not alter anything, just feed it your VI server name and sit back, it can also be used as a scheduled task and even set to email you a word attachment.

It even throws in a few graphs and pie charts for good measure, almost all information can be sent to the functions for you to expand on the report easily.

Comments are welcome, the good the bad and the ugly !

Usage: report.ps1 YOURVISERVER

See below or download here:

Reporting Script

48 thoughts on “Exporting Virtual Infrastructure Information to MS Word

  1. Mark

    Great Script and really clever but I can’t get the charts to work on a 64 bit 2008 R2 server with office 2007. Such a shame

  2. chucki

    I replaced the instances of:

    $series = ([array]$chart.charts)0].SeriesCollection.Add(0)

    as described from “Stu” in March 2011. Some Graphs are OK but others are blank.
    I am using vSphere 5.1 and Word 2010.

    The Report is pretty cool, but many things are missing or not OK. Did you work again on the Script already? Something new to share with us 🙂 ???

  3. DanielShao

    hi , is there any final solution to the problem of blanket graph generated with the script ?

  4. Cruzito

    Hi everyone,

    I am new to powershell and a scripting virgin, so I was wondering if someone can give me the stepts on how to actually run the script above.

    I have vmware PowerCLI or windows powershell

  5. Bipin

    Also getting below error.

    Property ‘Caption’ cannot be found on this object; make sure it exists and is s
    ettable.
    At E:\VI_Report.ps1:101 char:10
    + $series. <<<< Caption = "$Caption (last 24hrs)"
    + CategoryInfo : InvalidOperation: (Caption:String) [], RuntimeEx
    ception
    + FullyQualifiedErrorId : PropertyNotFound

    You cannot call a method on a null-valued expression.
    At E:\VI_Report.ps1:103 char:17
    + $series.SetData <<<< (1, -1, $categories)
    + CategoryInfo : InvalidOperation: (SetData:String) [], RuntimeEx
    ception
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\VI_Report.ps1:104 char:17
    + $series.SetData <<<< (2, -1, $values)
    + CategoryInfo : InvalidOperation: (SetData:String) [], RuntimeEx
    ception
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\VI_Report.ps1:106 char:22
    + $chart.ExportPicture <<<< ($filename, "jpg", 900, 600)
    + CategoryInfo : InvalidOperation: (ExportPicture:String) [], Run
    timeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "AddPicture" with "1" argument(s): "This is not a valid file
    name.
    Try one or more of the following:
    * Check the path to make sure it was typed correctly.
    * Select a file from the list of files and folders."
    At E:\VI_Report.ps1:111 char:55
    + $msWord.Application.Selection.InlineShapes.AddPicture <<<
    Null
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

    Remove-Item : Cannot find path ‘C:\Program Files (x86)\VMware\Infrastructure\vS
    phere PowerCLI\Chart.jpg’ because it does not exist.
    At E:\VI_Report.ps1:112 char:13
    + Remove-Item <<<< $filename
    + CategoryInfo : ObjectNotFound: (C:\Program File…erCLI\Chart.j
    pg:String) [Remove-Item], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.Remov
    eItemCommand

  6. Bipin

    I am using Office 2007 & getting below error, plese suggest.

    You cannot call a method on a null-valued expression.
    At E:\VI_Report.ps1:122 char:14
    + $chart.Clear <<<< ()
    + CategoryInfo : InvalidOperation: (Clear:String) [], RuntimeExce
    ption
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At E:\VI_Report.ps1:123 char:24
    + $c = $chart.charts.Add <<<< (0)
    + CategoryInfo : InvalidOperation: (Add:String) [], RuntimeExcept
    ion
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Property 'Type' cannot be found on this object; make sure it exists and is set
    able.
    At E:\VI_Report.ps1:125 char:5
    + $c. <<<< Type = 58
    + CategoryInfo : InvalidOperation: (Type:String) [], RuntimeExcep
    tion
    + FullyQualifiedErrorId : PropertyNotFound

    Property 'HasTitle' cannot be found on this object; make sure it exists and is
    settable.
    At E:\VI_Report.ps1:126 char:7
    + $c. <<<< HasTitle = "True"
    + CategoryInfo : InvalidOperation: (HasTitle:String) [], RuntimeE
    xception
    + FullyQualifiedErrorId : PropertyNotFound

    Property 'HasLegend' cannot be found on this object; make sure it exists and i
    settable.
    At E:\VI_Report.ps1:127 char:5
    + $c. <<<< HasLegend = "True"
    + CategoryInfo : InvalidOperation: (HasLegend:String) [], Runtime
    Exception
    + FullyQualifiedErrorId : PropertyNotFound

    Cannot index into a null array.
    At E:\VI_Report.ps1:128 char:36
    + $series = ([array] $chart.charts)[ <<<< 0].SeriesCollection.Add(0)
    + CategoryInfo : InvalidOperation: (0:Int32) [], RuntimeException
    + FullyQualifiedErrorId : NullArray

    You cannot call a method on a null-valued expression.
    At E:\VI_Report.ps1:129 char:41
    + $dl = $series.DataLabelsCollection.Add <<<< ()
    + CategoryInfo : InvalidOperation: (Add:String) [], RuntimeExcept
    ion
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Property 'HasValue' cannot be found on this object; make sure it exists and is
    settable.
    At E:\VI_Report.ps1:130 char:6
    + $dl. <<<< HasValue = "True"
    + CategoryInfo : InvalidOperation: (HasValue:String) [], RuntimeE
    xception
    + FullyQualifiedErrorId : PropertyNotFound

  7. Stu

    Hi Al,

    First off, I gotta say hats off to you for writing this script. It’s class! 🙂

    I’m running the new version of the VMWare PowerCLI, and I was initially getting lots of errors (You cannot call a method on a null-valued expression) when running the script.

    But then I read all the way through this blog and found that

    Replacing all instances of:

    $series = ([array]$chart.charts)0].SeriesCollection.Add(0)

    In the script with:

    $chartsEntry = ([array] $chart.charts)[0]
    $chartEntry = $chartsEntry.Add(0)
    $series = $chartEntry.SeriesCollection.Add(0)

    Fixed the issues.

    However, like SeaCros above me, all of the charts show up as “Chart Title”.
    Do you know how we can rectify this?

    Thanks,

    Stu

  8. SeaCros

    I am not sure why i get the error below. The script seems to run fine but i get this. Also all of my charts show up but none of the titles are listed it just says Chart Title

    Unable to index into an object of type VMware.VimAutomation.Client20.VMHostImpl.
    At C:\scripts\viReport_2.ps1:273 char:15
    + if ($contents[ <<<< 0] -eq $null){ + CategoryInfo : InvalidOperation: (0:Int32) [], RuntimeException + FullyQualifiedErrorId : CannotIndex Unable to index into an object of type VMware.VimAutomation.Client20.VMHostImpl. At C:\scripts\viReport_2.ps1:286 char:15 + if ($contents[ <<<< 0] -eq $null){ + CategoryInfo : InvalidOperation: (0:Int32) [], RuntimeException + FullyQualifiedErrorId : CannotIndex Thanks S

  9. VanT

    I had exact same error msg . Fix ==>Take out the [ref].

    As below extract, it worked for me.

    My environmenents: powershell v1, Office Web Combo 2003 SP3
    $filename = ‘C:\TEMP\Word_VMReport_’ + $VIServer + $Date.Day + “-” + $Date.Month + “-” + $Date.Year + ‘.doc’
    $wordDoc.SaveAs($filename)

  10. Chris

    Hi, i am trying to use the script and run through it to the end then get an error??

    Argument: ‘1’ should not be a System.Management.Automation.PSReference. Do not use [ref].
    At :line:460 char:15
    + $wordDoc.SaveAs <<<< ([ref]$filename)

    Any suggestions?

    thanks

  11. Ismar

    Can anybody tell me why the script runs fine but the charts are not named properly? All charts have “Chart Title” as the title. I had to replace the following in two places to get the charts to show up:

    $series = ([array]$chart.charts)0].SeriesCollection.Add(0)

    $chartsEntry = ([array] $chart.charts)[0]
    $chartEntry = $chartsEntry.Add(0)
    $series = $chartEntry.SeriesCollection.Add(0)

    Thank you so much and great script!

  12. Tin

    Has anyone got the charts section working on Powershell v2 (version that comes with Windows 7 & Server 2008). This appears to throw up the error with the line below as has been mentioned in other comments above
    $series = ([array] $chart.charts)[0].SeriesCollection.Add(0)

    With the original powershell it works fine. I have built a new Windows XP box as a test & installed PS on it & it worked. Soon as I installed PS v2 CTP3 I got the above error.
    I have tried with with the above mentioned lines in PSv2
    $chartsEntry = ([array] $chart.charts)[0]
    $chartEntry = $chartsEntry.Add(0)
    $series = $chartEntry.SeriesCollection.Add(0)
    but i get two charts in the one box & it doesn’t bring in the caption for the charts either.
    Any suggestions would be appreciated.

  13. James

    @Virtu-Al

    Apologies for confusion. I like the output from this report http://teckinfo.blogspot.com/2008/09/datastore-capacity.html and dont really like the pie charts for the datastores in the report in exporting to msword. I have removed the pie charts from exporting to ms word and would like to add the report from http://teckinfo.blogspot.com/2008/09/datastore-capacity.html to it. I have got it emailing me allready so its not a big deal.

    Thanks for all of these awesome reports. It has really allowed me to clean up our environment and become proactive instead of reactive to issues.

  14. Virtu-Al

    @James

    Sorry I dont understand what you are trying to do, do you just want a report with the datastore graphs and thats it ? Do you want word or jpg or other ?

  15. James

    James :@Virtu-Al
    I am a complete noob to this powershell coding. I have been trying to get this datastore graph to go into a word document so I can email it like this report is. What I would love is to get the datastore graph included with this report.

    Discovered that I could comment out the invoke item and add the mailer commands from this report and send the jpg as an attachment. This will work fine although I would like to see the datastore graph included in this word document attachment if possible.

  16. James

    @Virtu-Al

    I am a complete noob to this powershell coding. I have been trying to get this datastore graph to go into a word document so I can email it like this report is. What I would love is to get the datastore graph included with this report.

  17. Aaron

    Thanks Alan,
    One other question. I have a VI server that we have multiple Datacenters created on, each with multiple VM Guests, but when i run the report, it lumps all of the virtual machines in one table. Is there any way to add an additional column to the VM Infomation table that would include things like DataCenter, and if possible the IP address that the virtual machine is using, if it is available?

    Thanks much, i continually come back to this script for documentation.

    app

  18. Virtu-Al

    If you do a ‘Get-Help Connect-ViServer -full you will see that you need to use a -Port parameter to specifiy a port, something like this should work:

    Connect-VIServer ssv-mgmt -Port 8843

    Alan

  19. Aaron

    Hi there, me again. I have a problem with this script all of the sudden. I am trying to connect to a VI-Server that is not on the standard port of 443, it is on 8843, but all of the sudden, since I updated my VMWare powershell stuff, i am getting the following error:

    Connect-VIServer : Invalid URI: A port was expected because of there is a colon (‘:’) present but the port could not be parsed.
    At line:1 char:17
    + connect-VIServer <<<< ssv-mgmt:8843

    Do you have any thoughts as to why this is happening?

    Thanks much

    app

  20. tmcquaig

    I continue to get errors like this.

    Creating Top 5 Virtual Machines CPU Usage %Average bar chart…Please Wait
    New-Object : Retrieving the COM class factory for component with CLSID {0002E55
    D-0000-0000-C000-000000000046} failed due to the following error: 80040154.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:67 char:21
    + $chart = new-object <<<< -com OWC11.ChartSpace.11
    You cannot call a method on a null-valued expression.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:68 char:14
    + $chart.Clear( <<<< )
    You cannot call a method on a null-valued expression.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:69 char:24
    + $c = $chart.charts.Add( <<<< 0)
    Property 'Type' cannot be found on this object; make sure it exists and is sett
    able.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:71 char:5
    + $c.T <<<< ype = 4
    Property 'HasTitle' cannot be found on this object; make sure it exists and is
    settable.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:72 char:5
    + $c.H <<<< asTitle = "True"
    Cannot index into a null array.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:73 char:41
    + $chartsEntry = ([array] $chart.charts)[0 <<<< ]
    You cannot call a method on a null-valued expression.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:74 char:32
    + $chartEntry = $chartsEntry.Add( <<<< 0)
    You cannot call a method on a null-valued expression.
    At C:\Program Files (x86)\VMware\Infrastructure\VIToolkitForWindows\Scripts\Rep
    ort.ps1:75 char:44
    + $series = $chartEntry.SeriesCollection.Add( <<<< 0)

  21. dstewen

    I can’t get this script to automatically save the document. I am using Office 2007 on Windows XP.

    Each time I run the script word prompts for the name to save the file as.

  22. Dmitriy [kmdm] Komin

    For users who have error like that:
    "
    You cannot call a method on a null-valued expression.
    At H:\Downloads\scripting\Report.ps1:75 char:59
    + $series = ([array] $chart.charts)[0].SeriesCollection.Add <<<< (0)
    "

    Please replace the code:

    "
    $series = ([array]$chart.charts)0].SeriesCollection.Add(0)
    "

    by following one:

    "
    $chartsEntry = ([array] $chart.charts)[0]
    $chartEntry = $chartsEntry.Add(0)
    $series = $chartEntry.SeriesCollection.Add(0)
    "

    Enjoy 🙂

  23. Virtu-Al

    the simple answer is to add a

    -username myuser -password mypass

    to the connect-viserver command.

  24. Aaron Perrault

    One more, how would i embed the username and password in the script, or pass them to it if running it as a scheduled task?

  25. Aaron Perrault

    Never mind. it should have read.
    $filename = ‘C:\VMReport–‘ + $VIServer + ‘.doc’
    It works now

    Thanks for the great script.

    app

  26. Aaron Perrault

    Duh. I was trying to run it through the PowerGui Script Editor and it threw an error and didn’t finish so i assumed it would not work. I used the “run in External PowerShell Window” and even though it threw an error, it generated the report for me. One last thing though. If I want to set this up so the VM that I am running it against is included in the document name, how would i do that. I tried changing the line
    $filename = ‘C:\VMReport.doc’
    to
    $filename = ‘C:\VMReport–‘ +$VIServer ‘.doc’ and it threw an error right from the start.

    Thanks for the help.

    app

  27. Virtu-Al

    Actually, im slightly better than I thought, if you run it against a host it works fine, you will be prompted for the username and password to add that host and I got a slight error at the start but the document produces fine. Let me know how it goes.

  28. Aaron Perrault

    would it be too much to ask for a new version, or at least point me in the right direction.

    app

  29. Virtu-Al

    Sure, all you would need to do is comment out or remove the VI specific areas, do you know how to do this or would you like me to send you a version ?

  30. Aaron Perrault

    Hey Virtu-al. I have another question for you on this. We have a couple of stand-alone ESX servers, not connected to a Infrastructure Server. is it possible to get the information provided in this script to get that data as well, or does it have to be connected to a VI Server?

    thanks

    app

  31. Virtu-Al

    Shouldnt be a problem, Im a bit busy over the next couple of days but drop me a mail and I will send you the code when done.

  32. R

    GREAT SCRIPT MAN!!! Is there a way to combine the datastore graphs as one 100% bar graph instead of having a separate graph for each datastore? I’d like each datastore to be a %used/%free bar in a single graph?

    Thanks!

  33. Virtu-Al

    Yeah, you can either change the $numtoreturn value for each of the graphs it creates or in the function which creates the graph there is a line that says ‘$myCol | Sort-Object Value -Descending | Select-Object -First $numtoreturn | foreach-object {‘ if you delete the ‘ | Select-Object -First $numtoreturn’ that will display all.

    Funny enough I was thinking of adding a html output option.

  34. Aaron Perrault

    Thanks, worked like a charm. One question, you have the “Top 5 graphs” Is there any way to get all of the VMs in those? I haven’t scoured the code to see if it was listed there yet.

    thanks for the help. A way to get this to an HTML file automatically would be great.

    Thanks

    app

  35. Virtu-Al

    Hi Aaron, you can use the office web components in the link, there is not a 2007 version, the 2003 version works fine. hope all works ok for you. Comments welcome !

  36. Aaron Perrault

    This looks like a great script. I have one problem, I am running a full install of Office 2007, and when i run it, i get that the script requires Office Web Components to run correctly, and then it gives me a link to the Office 2003 versions. if i install the 2007 versions if they are available, will this still run properly?

    app

  37. Virtu-Al

    Thanks for the testing you have done, as it turns out there is something freeky about mixed versions of office so if you get this error please try and run on another PC with a same version office, perhaps even a VM!

  38. jfreichert

    Well, I’m using Word 2003, but Excel 2007. Can’t go to full 2007 without Outlook 2007 and can’t go there without our Exchange upgrade (which is next month)

  39. Virtu-Al

    Hi, thanks for the comments, MS changed the way it works in the different versions, what version of office are you using ?

  40. jfreichert

    Awesome script. I have one problem through the graphs are not compiling properly. During the run, I get these errors in powershell:

    You cannot call a method on a null-valued expression.
    At H:\Downloads\scripting\Report.ps1:75 char:59
    + $series = ([array] $chart.charts)[0].SeriesCollection.Add <<<< (0)

    This happens at various points in the run. Looks like whenever it is creating the chart object in the word doc. The Word doc and tables show up in the final doc, but the charts are blank.

    Any ideas?

Leave a Reply to VanT

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.