VMware Reporting Work Around

[clarinets101]

Ok, I'm reposting this to maybe start fresh, clarify what I need to accomplish, and get an answer I can work with.

Currently VMware reporting is awful when it comes to finding desktop pool usage on our view connection server.

What I would like to accomplish is to build a reporting script that runs on a scheduled task. This script would need to run in the back ground and write to a csv file, access file, or sql data base not sure on how I want the output to be just yet. I have a script written that aquires the data very crudely and does not always finish or hangs up. I would like to make it more reliable. right now i also have a good bit of stuff hard coded in the script but would like to make it more flexible by having "inf" files with different settings for the script to pull from.

Here is the current script:

expandcollapse popup

$date = @Mon & "/" & @mday & "/" & @year
$hour = @HOUR
$number = 1
$file = @mon & "-" & @mday & "-" & @YEAR

if Not fileexists ( "c:\reports\" & $file & ".xlsx") Then
run ( "C:\Program Files\Microsoft Office\Office12\excel.exe")
winwaitactive ( "Microsoft Excel - Book1")
send ( "Date")
send ( "{tab}")
send ( "Time")
send ( "{tab}")
send ( "Pool")
send ( "{tab}")
send ( "Sessions")
send ( "{tab}")
send ( "Total")
send ( "{enter}")
send ( "^s")
winwaitactive ( "Save As")
sleep (2000)
send ( "c:\reports\" & $file & ".xlsx")
send ( "{enter}")
winclose ( "Microsoft Excel - Book1")
winwaitclose ( "Microsoft Excel - Book1")
EndIf


$pool1 = "PSAcct"
$pool2 = "PSADMINISTRATION"
$pool3 = "PSHEALTHWORKS"
$pool4 = "PSHealthWorksPOS"
$pool5 = "PSHIM"
$pool6 = "PSHIMRemote"
$pool7 = "PSHospice"
$pool8 = "PSIT"
$pool9 = "PSLab"
$pool10 = "PSMaintenance"
$pool11 = "PSMaintenanceOffice"
$pool12 = "PSChamberlin"
$pool13 = "PSMaterials"
$pool14 = "PSOncology"
$pool15 = "PSOR"
$pool16 = "PSPathology"
$pool17 = "PSPFS"
$pool18 = "PSPharmacy"
$pool19 = "PSRadiology"
$pool20 = "PSRadiologyPersistent"
$pool21 = "PSRADVDI"
$pool22 = "PSClassroom"
$pool23 = "PSReceptionist"
$pool24 = "PSRemote"
$pool25 = "PSSantaRosa"
$pool26 = "PSSCRIPTBAR"
$pool27 = "PSSUMMIT"
$pool28 = "PSClinical"
$pool29 = "PSCounsel"
$pool30 = "PSDietary"
$pool31 = "PSDietaryFloating"
$pool32 = "PSEVS"
$pool33 = "PSGiftShop"


run ( "powershell.exe")
WinWaitActive ( "Administrator: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe")
send ( "import-module c:\uadv_vmware_view.psm1")
send ( "{enter}")
send ( "r")
send ( "{enter}")
send ( "connect-viewconnserver -user username -password password -domain domain -viewconnserver viewconnserver")
send ( "{enter}")
send ( "r")
send ( "{enter}")
sleep ( 5000)
send ( "get-pools > c:\pools.txt")
send ( "{enter}")
send ( "r")
send ( "{enter}")
send ( "exit")
send ( "{enter}")
winwaitnotactive ( "Administrator: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe")
run ( "C:\Program Files\Microsoft Office\Office12\excel.exe c:\reports\" & $file & ".xlsx")
winwaitactive ( "Microsoft Excel - " & $file & ".xlsx")
run ( "notepad c:\pools.txt")
$pool = $pool1
$number = 1
While 1
if $number < 36 then
WinActivate ( "pools.txt - Notepad")
winwaitactive ( "pools.txt - Notepad")
sleep (500)
send ( "^{home}")
send ("^f")
send ( $pool)
sleep (1000)
send ("{enter}")
send ("{esc}")
sleep (500)
send ( "{up 5}")
send ( "^{left}")
send ( "+{end}")
send ( "^c")
$total = clipget ()
send ( "{down 27}")
sleep (1000)
send ( "+{end}")
send ( "^c")
$used = clipget ()
;send ("^c")
;sleep (500)
winactivate ( "Microsoft Excel - " & $file & ".xlsx")
winwaitactive ( "Microsoft Excel - " & $file & ".xlsx")
sleep (500)
send ( $date)
send ( "{tab}")
send ( $hour & ":00:00")
send ( "{tab}")
send ( $pool)
send ( "{tab}")
send ( $used)
send ( "{tab}")
send ( $total)
sleep (500)
send ( "{enter}")
sleep (500)
$number += 1
if $number = 2 Then
$pool = $pool2
endif
if $number = 3 Then
$pool = $pool3
endif
if $number = 4 Then
$pool = $pool4
endif
if $number = 5 Then
$pool = $pool5
endif
if $number = 6 Then
$pool = $pool6
endif
if $number = 7 Then
$pool = $pool7
endif
if $number = 8 Then
$pool = $pool8
endif
if $number = 9 Then
$pool = $pool9
endif
if $number = 10 Then
$pool = $pool10
endif
if $number = 11 Then
$pool = $pool11
endif
if $number = 12 Then
$pool = $pool12
endif
if $number = 13 Then
$pool = $pool13
endif
if $number = 14 Then
$pool = $pool14
endif
if $number = 15 Then
$pool = $pool15
endif
if $number = 16 Then
$pool = $pool16
endif
if $number = 17 Then
$pool = $pool17
endif
if $number = 18 Then
$pool = $pool18
endif
if $number = 19 Then
$pool = $pool19
endif
if $number = 20 Then
$pool = $pool20
endif
if $number = 21 Then
$pool = $pool21
endif
if $number = 22 Then
$pool = $pool22
endif
if $number = 23 Then
$pool = $pool23
endif
if $number = 24 Then
$pool = $pool24
endif
if $number = 25 Then
$pool = $pool25
endif
if $number = 26 Then
$pool = $pool26
endif
if $number = 27 Then
$pool = $pool27
endif
if $number = 28 Then
$pool = $pool28
endif
if $number = 29 Then
$pool = $pool29
endif
if $number = 30 Then
$pool = $pool30
endif
if $number = 31 Then
$pool = $pool31
endif
if $number = 32 Then
$pool = $pool32
endif
if $number = 33 Then
$pool = $pool33
endif

if $number = 34 Then
winactivate ( "Microsoft Excel - " & $file & ".xlsx")
winwaitactive ( "Microsoft Excel - " & $file & ".xlsx")
send ("^s")
winclose ( "Microsoft Excel - " & $file & ".xlsx")
WinActivate ( "pools.txt - Notepad")
winwaitactive ( "pools.txt - Notepad")
WinClose ( "pools.txt - Notepad")
Exit
EndIf
EndIf
WEnd

So basically the script runs powershell and writes the output to a txt file then manually opens up excel and finds/copies/pastes information. it's very crude and unreliable but serving the purpose but i would like to take it the next level if at all possible. I would like a couple of config files that the script looks at, one containing a list of my pools, and the other the connection info that is used in powershell. I am really trying to get this going smoother than what it is so i can do a better job of monitory my pool usage.

I appreciate all help that is given in this project.

Thank you

Nicholas

[BrewManNH]

There is a UDF for working with Excel that won't require the use of Send, which is extremely unreliable if anyone is using the computer, and unusable if the computer is locked. Also, using Notepad and Send to update a text file is not the way to go about it, I would look at FileOpen, FileRead, FileWriteLine etc. for that part of it.

I'm not sure what the Powershell script is doing, so I can't comment on that part of it.