Pulling data out of a text file to build a report from

clarinets101 · August 10, 2012

I am pulling data from our vmware view environment to build some reports for our pool usage, because vmware doesn't have a way to run statistics on pool usage. I'm using powershell cmdlets to pull the data out and can save it into a txt format. What i'd like to do is have it run a scan against the data i pull with a list of my pools either hard coded in the script or pull from another file to find different information related to the search.

Here is an example of the data I'm pulling with my cmdlets:

dataSourceCount : 3

type : Floating

nonPersistentType : True

idJS : gotoSummaryPage

folderId : /

id : PSHealthWorks

vmCount : 3

selected : False

individualType : False

persistentType : False

deleteing : False

checkPrivilege : False

name : PSHealthWorks

provisioningEnabled : True

provisionedType : True

provisionErrMsg :

vcUserName : support-vmware

vcManaged : True

deliveryModel : Automated Pool

poolId : PSHealthWorks

vcServerName : psmcvcentervdi.sumter.phoebe.com

entitled : True

enabled : True

vcId : 1d783d30-8ad3-4b7e-8d5b-1cfceeb11ef1

poolName : PSHealthWorks

listType : False

persistence : Floating

sessionCount : 1

manualType : False

noDefault : False

unmanaged : False

offlineSessionCount : 0

hasDefaultDesktop : False

source : vCenter (linked clone)

withError : False

terminalServicesType : False

totalSessionsDisplay : 1 Remote

disabled : False

dataSourceCount : 1

type : Floating

nonPersistentType : True

idJS : gotoSummaryPage

folderId : /

id : PSHealthWorksPOS

vmCount : 1

selected : False

individualType : False

persistentType : False

deleteing : False

checkPrivilege : False

name : PSHealthWorksPOS

provisioningEnabled : True

provisionedType : True

provisionErrMsg :

vcUserName : support-vmware

vcManaged : True

deliveryModel : Automated Pool

poolId : PSHealthWorksPOS

vcServerName : psmcvcentervdi.sumter.phoebe.com

entitled : True

enabled : True

vcId : 1d783d30-8ad3-4b7e-8d5b-1cfceeb11ef1

poolName : PSHealthWorksPOS

listType : False

persistence : Floating

sessionCount : 0

manualType : False

noDefault : False

unmanaged : False

offlineSessionCount : 0

hasDefaultDesktop : False

source : vCenter (linked clone)

withError : False

terminalServicesType : False

totalSessionsDisplay : 0 Remote

disabled : False

dataSourceCount : 14

type : Dedicated

nonPersistentType : False

idJS : gotoSummaryPage

folderId : /

id : PSHIM

vmCount : 14

selected : False

individualType : False

persistentType : True

deleteing : False

checkPrivilege : False

name : PSHIM

provisioningEnabled : True

provisionedType : True

provisionErrMsg :

vcUserName : support-vmware

vcManaged : True

deliveryModel : Automated Pool

poolId : PSHIM

vcServerName : psmcvcentervdi.sumter.phoebe.com

entitled : True

enabled : True

vcId : 1d783d30-8ad3-4b7e-8d5b-1cfceeb11ef1

poolName : PSHIM

listType : False

persistence : Dedicated

sessionCount : 11

manualType : False

noDefault : False

unmanaged : False

offlineSessionCount : 0

hasDefaultDesktop : False

source : vCenter

withError : False

terminalServicesType : False

totalSessionsDisplay : 11 Remote, 0 Local

disabled : False

I would like a csv file to be made to look a lot like this:

a header row of pool name, total vms, sessions with the values underneath

so i would need the values of poolid, datasourcecount, sessioncount

So i would have a list of my pools preferably in a separate file that the script looks at, finds the related data in the text file and outputs it into the csv file.

i'm hoping I am making sense in all of this please ask questions if i'm not being clear.

jdelaney · August 10, 2012

I can't get one unified regexp to work, but this creates 3 arrays

#include <Array.au3>

$text = "dataSourceCount     : 3" & @crlf & _
"type                : Floating" & @crlf & _
"nonPersistentType : True" & @crlf & _
"idJS                : gotoSummaryPage" & @crlf & _
"folderId            : /" & @crlf & _
"id              : PSHealthWorks" & @crlf & _
"vmCount             : 3" & @crlf & _
"selected            : False" & @crlf & _
"individualType  : False" & @crlf & _
"persistentType  : False" & @crlf & _
"deleteing       : False" & @crlf & _
"checkPrivilege  : False" & @crlf & _
"name                : PSHealthWorks" & @crlf & _
"provisioningEnabled : True" & @crlf & _
"provisionedType     : True" & @crlf & _
"provisionErrMsg     : " & @crlf & _
"vcUserName      : support-vmware" & @crlf & _
"vcManaged       : True" & @crlf & _
"deliveryModel   : Automated Pool" & @crlf & _
"poolId          : PSHealthWorks" & @crlf & _
"vcServerName        : psmcvcentervdi.sumter.phoebe.com" & @crlf & _
"entitled            : True" & @crlf & _
"enabled             : True" & @crlf & _
"vcId                : 1d783d30-8ad3-4b7e-8d5b-1cfceeb11ef1" & @crlf & _
"poolName            : PSHealthWorks" & @crlf & _
"listType            : False" & @crlf & _
"persistence         : Floating" & @crlf & _
"sessionCount        : 1" & @crlf & _
"manualType      : False" & @crlf & _
"noDefault       : False" & @crlf & _
"unmanaged       : False" & @crlf & _
"offlineSessionCount : 0" & @crlf & _
"hasDefaultDesktop : False" & @crlf & _
"source          : vCenter (linked clone)" & @crlf & _
"withError       : False" & @crlf & _
"terminalServicesType : False" & @crlf & _
"totalSessionsDisplay : 1 Remote" & @crlf & _
"disabled            : False" & @crlf & _
" " & @crlf & _
"dataSourceCount     : 1" & @crlf & _
"type                : Floating" & @crlf & _
"nonPersistentType : True" & @crlf & _
"idJS                : gotoSummaryPage" & @crlf & _
"folderId            : /" & @crlf & _
"id              : PSHealthWorksPOS" & @crlf & _
"vmCount             : 1" & @crlf & _
"selected            : False" & @crlf & _
"individualType  : False" & @crlf & _
"persistentType  : False" & @crlf & _
"deleteing       : False" & @crlf & _
"checkPrivilege  : False" & @crlf & _
"name                : PSHealthWorksPOS" & @crlf & _
"provisioningEnabled : True" & @crlf & _
"provisionedType     : True" & @crlf & _
"provisionErrMsg     : " & @crlf & _
"vcUserName      : support-vmware" & @crlf & _
"vcManaged       : True" & @crlf & _
"deliveryModel   : Automated Pool" & @crlf & _
"poolId          : PSHealthWorksPOS" & @crlf & _
"vcServerName        : psmcvcentervdi.sumter.phoebe.com" & @crlf & _
"entitled            : True" & @crlf & _
"enabled             : True" & @crlf & _
"vcId                : 1d783d30-8ad3-4b7e-8d5b-1cfceeb11ef1" & @crlf & _
"poolName            : PSHealthWorksPOS" & @crlf & _
"listType            : False" & @crlf & _
"persistence         : Floating" & @crlf & _
"sessionCount        : 0" & @crlf & _
"manualType      : False" & @crlf & _
"noDefault       : False" & @crlf & _
"unmanaged       : False" & @crlf & _
"offlineSessionCount : 0" & @crlf & _
"hasDefaultDesktop : False" & @crlf & _
"source          : vCenter (linked clone)" & @crlf & _
"withError       : False" & @crlf & _
"terminalServicesType : False" & @crlf & _
"totalSessionsDisplay : 0 Remote" & @crlf & _
"disabled            : False" & @crlf & _
"dataSourceCount     : 14" & @crlf & _
"type                : Dedicated" & @crlf & _
"nonPersistentType : False" & @crlf & _
"idJS                : gotoSummaryPage" & @crlf & _
"folderId            : /" & @crlf & _
"id              : PSHIM" & @crlf & _
"vmCount             : 14" & @crlf & _
"selected            : False" & @crlf & _
"individualType  : False" & @crlf & _
"persistentType  : True" & @crlf & _
"deleteing       : False" & @crlf & _
"checkPrivilege  : False" & @crlf & _
"name                : PSHIM" & @crlf & _
"provisioningEnabled : True" & @crlf & _
"provisionedType     : True" & @crlf & _
"provisionErrMsg     : " & @crlf & _
"vcUserName      : support-vmware" & @crlf & _
"vcManaged       : True" & @crlf & _
"deliveryModel   : Automated Pool" & @crlf & _
"poolId          : PSHIM" & @crlf & _
"vcServerName        : psmcvcentervdi.sumter.phoebe.com" & @crlf & _
"entitled            : True" & @crlf & _
"enabled             : True" & @crlf & _
"vcId                : 1d783d30-8ad3-4b7e-8d5b-1cfceeb11ef1" & @crlf & _
"poolName            : PSHIM" & @crlf & _
"listType            : False" & @crlf & _
"persistence         : Dedicated" & @crlf & _
"sessionCount        : 11" & @crlf & _
"manualType      : False" & @crlf & _
"noDefault       : False" & @crlf & _
"unmanaged       : False" & @crlf & _
"offlineSessionCount : 0" & @crlf & _
"hasDefaultDesktop : False" & @crlf & _
"source          : vCenter" & @crlf & _
"withError       : False" & @crlf & _
"terminalServicesType : False" & @crlf & _
"totalSessionsDisplay : 11 Remote, 0 Local" & @crlf & _
"disabled            : False"
;StringRegExp ( $text, "datasourceCount.*:(.*)[.*|/s], 3 )
$atest = StringRegExp ( $text, "(?i)[s]?datasourceCounts.*:(.*)rn", 3 )
$atest2 = StringRegExp ( $text, "(?i)[s]?poolIds.*:(.*)", 3 )
$atest3 = StringRegExp ( $text, "(?i)[s]sessionCounts.*:(.*)rn", 3 )
_ArrayDisplay ($atest )
_ArrayDisplay ($atest2 )
_ArrayDisplay ($atest3 )

Use those arrays to create whatever you need.

Edited August 10, 2012 by jdelaney

jdelaney · August 10, 2012

It's ugly, others will be able to help, but here is how to return it to one, one dimensional array of repeating datasourcecount, poolid, sessioncount...

$combined = StringRegExp ( $text, "(?i)[s]?datasourceCounts+:s+(.*)s|poolIds+:s+(.*)s|[s]sessionCounts+:s+(.*)s",3);+poolIds+:s+(.*)rn", 3 )
For $i = UBound ( $combined ) -1 To 0 Step -1
If $combined[$i] = "" Then _ArrayDelete ( $combined, $i )
NExt
_ArrayDisplay ($combined)

Array printout:

[0]|3

[1]|PSHealthWorks

[2]|1

[3]|1

[4]|PSHealthWorksPOS

[5]|0

[6]|14

[7]|PSHIM

[8]|11

or, here is an array, that you can then use to create a CSV by _FileWriteFromArray:

; Get the values needed, groups of (datasroucecount/spoolID/sessionCount)
$combined = StringRegExpReplace ( $text, "dataSourceCounts+:s+(.*)|spoolIds+:s+(.*)|[s]sessionCounts+:s+(.*)|(?:[V|s])","$1$2$3")
; Split by CRLF
$atest4 = StringSplit ( $combined, @crlf )
; Find the total groups
$iGroups = Round((UBound($atest4)-2)/3)
Dim $aData[$iGroups+1]
$aData[0] = "dataSourceCount,spoolId,sessionCount"
For $i = 1 To $iGroups
 $id = ($i-1) * $iGroups
 $data1 = $atest4[$id+1]
 $data2 = $atest4[$id+2]
 $data3 = $atest4[$id+3]
 $aData[$i] = $data1 & "," & $data2 & "," & $data3
Next
_ArrayDisplay ( $aData)

Edited August 10, 2012 by jdelaney

iamtheky · August 10, 2012

or split the text into an array. copying your wall from post 1 into a .txt as is, the below script should msgbox the elements Jdelaney isolated into their own array.

#include <array.au3>

$text = fileread("test.txt")

$textArray = stringsplit ($text , @LF , 2)

for $i = ubound($textArray) - 1 to 0 step -1
$textArray[$i] = StringstripWS($textArray[$i], 8)
Next

;~ _ArrayDisplay($textArray)

for $i = 0 to ubound($textArray) - 1
msgbox(0, '' , $textArray[$i] & @CRLF & $textArray[$i + 19] & @CRLF & $textArray[$i + 27])
$i += 38
next

Edited August 10, 2012 by boththose

clarinets101 · August 17, 2012

Ok, so I'm fairly new to AutoIT, I've been using it for about a year now for doing some basic copy and paste tasks, prompting for files/folders that need to have commands ran against. I'm far from being a pro but getting a handle on it but this one maybe out of my realm and may not be possible but i don't like to think so. I'm going to re-ask my question with a little more clarity and a better view on the scenario hopefully.

My current script that is running but I would like to make it seamless and able to run in the background:

$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 ( "{enter}")
send ( "^s")
send ( "{enter}")
winwaitactive ( "Save As")
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 = "PSOncClinical"
$pool15 = "PSOncology"
$pool16 = "PSOR"
$pool17 = "PSPathology"
$pool18 = "PSPFS"
$pool19 = "PSPharmacy"
$pool20 = "PSRadiology"
$pool21 = "PSRadiologyPersistent"
$pool22 = "PSRADVDI"
$pool23 = "PSClassroom"
$pool24 = "PSReceptionist"
$pool25 = "PSRemote"
$pool26 = "PSSantaRosa"
$pool27 = "PSSCRIPTBAR"
$pool28 = "PSSUMMIT"
$pool29 = "PSClinical"
$pool30 = "PSCounsel"
$pool31 = "PSDietary"
$pool32 = "PSDietaryFloating"
$pool33 = "PSEVS"
$pool34 = "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 sumter -viewconnserver vcs-sumtervdi")
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 ( "{down 22}")
sleep (500)
send ( "+{end}")
send ( "^c")
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 ("^v")
sleep (500)
send ( "{enter}")
$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
$pool = $pool34
endif
if $number = 35 Then
winactivate ( "Microsoft Excel - " & $file & ".xlsx")
winwaitactive ( "Microsoft Excel - " & $file & ".xlsx")
send ("^s")
winclose ( "Microsoft Excel - " & $file & ".xlsx")
;send ("!{f4}"
WinActivate ( "pools.txt - Notepad")
winwaitactive ( "pools.txt - Notepad")
WinClose ( "pools.txt - Notepad")
Exit
EndIf
EndIf
WEnd

I am attaching a copy of a pools.txt

So essentially what happens is it first checks to see if a file the same name as the current date exists and creates it if it does not. Then it launches a powershell and runs several commands, even importing a custom module then exports the results to a txt file. next it launches the txt file and the date.xlsx file and does a find then copy and paste for the info and does it for the list that I hard-coded into the script. i have this run hourly right now but would probably have it run more frequently if it could run in the background. Ultimately what i would like to have is in a "working directory" that can be hard coded into the script i would just have to make sure i put all the files in it, have a list of all my pools so that i can update it if i need to, a File with server/username/password information so i could use this on different brokers in our organization and have the script refer to that when it does it's searching for the info. I'm hoping i'm making sense of all of this and posting what i'm doing with my script will give some insight to what i'm trying to accomplish.Thank you,Nicholas

pools.txt

Edited August 17, 2012 by clarinets101

jdelaney · August 17, 2012

To just chime in on one of your topics "run in the background", you can use the _Excel functions, and set state to not visible when creating/attaching/opening the excel document.

I would also use the _excel functions to write into excel, rather than sending tab

clarinets101 · August 18, 2012

Ok but how would i accomplish searching the text file for the data in the background?

Sign In

Pulling data out of a text file to build a report from

Recommended Posts

clarinets101

Link to comment

Share on other sites

jdelaney

Link to comment

Share on other sites

jdelaney

Link to comment

Share on other sites

iamtheky

Link to comment

Share on other sites

clarinets101

Link to comment

Share on other sites

jdelaney

Link to comment

Share on other sites

clarinets101

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta