barakdulac

Outputting to csv/excel format help

1 post in this topic

Hey you awesome people,

        I've enjoyed your expertise through other posts on the board and finally have one that I don't think has already been answered.

        The script below is designed to be run from USB to gather key system information manually and automatically and spit out a report for the PC and a .txt file that is comma delimited for easy import to excel.    

         Unfortuneatley, for some reason I can't get the csv file to create using RunWait dos commands or the FileWrite below...it just exits with no error.

         I am also not happy about having to write my wmic results to file and then reading and re-importing them...but other while loops failed when put within functions.

         Also...My filewrites are adding an extra row to the output :(

         Please help!

Joseph

 

 

PROBLEM

FileWrite($PCOnboardingSpreadsheet, $MAPLOCATION & ',' & $devicetype & ',' & $Make & ',' & $Model & ',' & $Serial & ',' & @IPAddress1 & ',' & $DHCPStatus & ',' & $networktype & ',' & @ComputerName & ',' & $Processor & ',' & $TotalMemory & ',' & $Userfullname & ',' & $SharedAccount & ',' & $WallJack & ',' & $OtherWallJack)

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile=ONBOARDING_PC_SURVEY_v4.exe
#AutoIt3Wrapper_Compile_Both=y
#AutoIt3Wrapper_UseX64=y
#AutoIt3Wrapper_Run_Tidy=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <Constants.au3>
#include <File.au3>

#comments-start

     ONBOARDING PC SURVEY
    Created by Joseph Garfield
    Born 5JUN2014
    Modded 13OCT2015
    PURPOSE:      To be run by the tech to manually enter some information needed to migrate, and to automagically grab the rest.
    DEPENDENCIES:  USB

#comments-end


;CUSTOM VARIABLES
Global $PCOnboardingTicket = @WorkingDir & "\" & @ComputerName & "_INFO.txt" ; Creates report on local dir/usb
Global $PCOnboardingSpreadsheet = @WorkingDir & "\CONSOLIDATED_ONBOARDING_PC_INFO.txt" ; Creates report on local dir/usb
Global $TempFile = @WorkingDir & "\TempFile.txt" ; Creates temp file to read variables from.
;Start Build Ticket
CreateBuildTicket()

Func CreateBuildTicket()
    RunWait(@ComSpec & ' /c ECHO. > ' & $PCOnboardingTicket)
    ; Create HEADER
    RunWait(@ComSpec & ' /c ECHO       - COMPANY HOSPITAL - Microsystems - PC ONBOARDING FORM - >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO       -  DATE AND TIME INITIATED - %DATE% %TIME% -                 >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO ______________________________________________________________________________ >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    ;Roll out
    Call("_Start")
EndFunc   ;==>CreateBuildTicket

Func _Start()
    ; Launch question to verify they want to perform USMT
    Local $iAnswer = MsgBox(BitOR($MB_OKCANCEL, $MB_SYSTEMMODAL, $MB_ICONINFORMATION, $MB_SETFOREGROUND), " Onboarding Survey v4", "Greetings from COMPANY Microsystems!" & @CRLF & @CRLF & "Use this tool to gather needed PC information to USB" & @CRLF & @CRLF & "It will take a few minutes to complete this process." & @CRLF & @CRLF & "Please press OK to continue.")
    If $iAnswer = 1 Then Call("_MULTIUSER") ; If they hit OK proceed to next step
    If $iAnswer = 2 Then Exit ; If they hit Cancel then Exit immediately
EndFunc   ;==>_Start

Func _MULTIUSER()
    Local $UserType = MsgBox(BitOR($MB_YESNO, $MB_SYSTEMMODAL, $MB_ICONQUESTION, $MB_SETFOREGROUND), " Onboarding Survey v4", "Is this PC shared by many users with a generic account?")
    If $UserType = 6 Then Call("_SHAREDACCOUNTNAME") ; If they hit Yes go to Shared Account Name
    If $UserType = 7 Then Call("_USERFULLNAME") ; If they hit No go to User Full Name
EndFunc   ;==>_MULTIUSER

;FOR GENERIC/SHARED PCs
Func _SHAREDACCOUNTNAME()
    Global $SharedAccount = InputBox(" Onboarding Survey v4", @CRLF & "Please enter shared account user name:", "", "", 350, 150)
    If @error = 1 Then Exit ; If they hit Cancel then Exit immediately
    If $SharedAccount = "" Then Call("_SHAREDACCOUNTNAME")
    Call("_SHAREDACCOUNTPASSWORD")
EndFunc   ;==>_SHAREDACCOUNTNAME

Func _SHAREDACCOUNTPASSWORD()
    ; Asks the user to enter a password.
    Global $SharedAccountPassword = InputBox("Security Check", @CRLF & "Please enter the password for the shared account:   - " & $SharedAccount & " -", "", "*", 350, 160)
    If @error = 1 Then Exit
    Call("_DocumentSharedAccount")
EndFunc   ;==>_SHAREDACCOUNTPASSWORD

Func _DocumentSharedAccount()
    ; Enter User-provided info
    RunWait(@ComSpec & ' /c ECHO SHARED ACCOUNT : ' & $SharedAccount & ' >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO SHARED PASSWORD: ' & $SharedAccountPassword & ' >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    Call("_MAPLOCATION")
EndFunc   ;==>_DocumentSharedAccount


;FOR SINGLE USER PCs
Func _USERFULLNAME()
    Global $Userfullname = InputBox(" Onboarding Survey v4", @CRLF & "Please enter the full name of the primary user of this PC.", "", "", 350, 150)
    If @error = 1 Then Exit ; If they hit Cancel then Exit immediately
    If $Userfullname = "" Then Call("_USERFULLNAME")
    Call("_USERNAME")
EndFunc   ;==>_USERFULLNAME

Func _USERNAME()
    Global $UserName = InputBox(" Onboarding Survey v4", @CRLF & "Please enter the COMPANY user name for:   - " & $Userfullname & " -", "", "", 400, 150)
    If @error = 1 Then Exit ; If they hit Cancel then Exit immediately
    If $UserName = "" Then Call("_USERNAME")
    Call("_USERPASSWORD")
EndFunc   ;==>_USERNAME

Func _USERPASSWORD()
    ; Asks the user to enter a password.
    Global $UserPassword = InputBox("Security Check", @CRLF & "Please enter the password for:    - " & $UserName & " -" & @CRLF, "", "*", 350, 160)
    If @error = 1 Then Exit
    If $UserPassword = "" Then Call("_USERPASSWORD")
    Call("_DocumentUserName")
EndFunc   ;==>_USERPASSWORD

Func _DocumentUserName()
    RunWait(@ComSpec & ' /c ECHO USER FULL NAME: ' & $Userfullname & ' >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO  USERNAME  : ' & $UserName & ' >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO  PASSWORD  : ' & $UserPassword & ' >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    Call("_MAPLOCATION")
EndFunc   ;==>_DocumentUserName

;PC INFORMATION QUESTIONS

Func _MAPLOCATION()
    Global $MAPLOCATION = InputBox(" Onboarding Survey v4", @CRLF & "Please enter the MAP LOCATION of this PC (from Floor Plan CS).", "", "", 350, 150)
    If @error = 1 Then Exit
    If $MAPLOCATION = "" Then Call("_MAPLOCATION")
    ; Grab old Computer Info
    RunWait(@ComSpec & ' /c ECHO __________________________ OLD COMPUTER INFORMATION __________________________ >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO MAP LOCATION    : ' & $MAPLOCATION & ' >> ' & $PCOnboardingTicket)
    Call("_DEVICETYPE")
EndFunc   ;==>_MAPLOCATION

Func _DEVICETYPE()
    Local $Desktop, $Laptop, $Tablet, $msg
    GUICreate("Device Type", 300, 100)

    Opt("GUICoordMode", 2)
    $Desktop = GUICtrlCreateButton("Desktop", 50, 10, 200)
    $Laptop = GUICtrlCreateButton("Laptop", -1, 0)
    $Tablet = GUICtrlCreateButton("Tablet", -1, 0)

    GUISetState()

    ; Run the GUI until the window is closed
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $Desktop
                Global $devicetype = "Desktop"
                RunWait(@ComSpec & ' /c ECHO DEVICE TYPE     : ' & $devicetype & ' >> ' & $PCOnboardingTicket)
                Call("_NETWORKTYPE")
            Case $msg = $Laptop
                Global $devicetype = "Laptop"
                RunWait(@ComSpec & ' /c ECHO DEVICE TYPE     : ' & $devicetype & ' >> ' & $PCOnboardingTicket)
                Call("_NETWORKTYPE")
            Case $msg = $Tablet
                Global $devicetype = "Tablet"
                RunWait(@ComSpec & ' /c ECHO DEVICE TYPE     : ' & $devicetype & ' >> ' & $PCOnboardingTicket)
                Call("_NETWORKTYPE")
        EndSelect
    WEnd
EndFunc   ;==>_DEVICETYPE

Func _NETWORKTYPE()
    Local $Wired, $Wireless, $Standalone, $msg
    GUICreate("Network Type", 300, 100)

    Opt("GUICoordMode", 2)
    $Wired = GUICtrlCreateButton("Wired", 50, 10, 200)
    $Wireless = GUICtrlCreateButton("Wireless", -1, 0)
    $Standalone = GUICtrlCreateButton("Standalone", -1, 0)

    GUISetState()

    ; Run the GUI until the window is closed
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $Wired
                Global $networktype = "Wired"
                RunWait(@ComSpec & ' /c ECHO NETWORK TYPE    : ' & $networktype & ' >> ' & $PCOnboardingTicket)
                Call("_WallJack")
            Case $msg = $Wireless
                Global $networktype = "Wireless"
                RunWait(@ComSpec & ' /c ECHO NETWORK TYPE    : ' & $networktype & ' >> ' & $PCOnboardingTicket)
                Call("_OtherWallJack")
            Case $msg = $Standalone
                Global $networktype = "Standalone"
                RunWait(@ComSpec & ' /c ECHO NETWORK TYPE    : ' & $networktype & ' >> ' & $PCOnboardingTicket)
                Call("_OtherWallJack")
        EndSelect
    WEnd
EndFunc   ;==>_NETWORKTYPE

Func _WallJack()
    Global $WallJack = InputBox(" Onboarding Survey v4", @CRLF & "Please enter the WALL JACK # this PC is using.", "", "", 350, 150)
    If $WallJack = "" Then Call("_WallJack")
    RunWait(@ComSpec & ' /c ECHO WALL JACK       : ' & $WallJack & ' >> ' & $PCOnboardingTicket)
    If @error = 1 Then Exit
    Call("_OtherWallJack")
EndFunc   ;==>_WallJack

Func _OtherWallJack()
    Global $OtherWallJack = InputBox(" Onboarding Survey v4", @CRLF & "Please list any other WALL JACKS on the wall plate.", "", "", 350, 150)
    RunWait(@ComSpec & ' /c ECHO OTHER WALL JACKS: ' & $OtherWallJack & ' >> ' & $PCOnboardingTicket)
    If @error = 1 Then Exit
    Call("_ScanNotify")
EndFunc   ;==>_OtherWallJack

Func _ScanNotify()
    Local $iAnswer = MsgBox(BitOR($MB_OKCANCEL, $MB_SYSTEMMODAL, $MB_ICONWARNING, $MB_SETFOREGROUND), " Onboarding Survey v4", "Thank you for your information!" & @CRLF & @CRLF & "The survey will now scan your system to gather some final info." & @CRLF & @CRLF & "Depending on the speed of your computer this process can take several minutes" & @CRLF & @CRLF & "Please press OK to continue.")
    If $iAnswer = 2 Then Exit
    ProgressOn("Progress Meter", "Scanning System Settings...", "5%")
    Sleep(1000)
    Call("Make")
EndFunc   ;==>_ScanNotify

Func Make()
    ; Make
    RunWait(@ComSpec & ' /c wmic computersystem get Manufacturer | find /v /i "Manufacturer" > ' & $TempFile)
    Sleep(1000)
    Global $file = FileOpen($TempFile, 0)
    While 1
        Global $Make = FileRead($file, 50)
        If @error = -1 Then ExitLoop
        RunWait(@ComSpec & ' /c ECHO COMPUTER NAME   : ' & @ComputerName & ' >> ' & $PCOnboardingTicket)
        FileWriteLine($PCOnboardingTicket, "MAKE            : " & $Make)
    WEnd
    FileClose($file)
    Call("Model")
EndFunc   ;==>Make

Func Model()
    ; Model
    RunWait(@ComSpec & ' /c wmic computersystem get Model | find /v /i "Model" > ' & $TempFile)
    Sleep(1000)
    Global $file = FileOpen($TempFile, 0)
    While 1
        Global $Model = FileRead($file, 50)
        If @error = -1 Then ExitLoop
        FileWriteLine($PCOnboardingTicket, "MODEL           : " & $Model)
        ProgressSet("15", "15%", "Scanning System...")
    WEnd
    FileClose($file)
    Call("Serial")
EndFunc   ;==>Model

Func Serial()
    ;Serial
    RunWait(@ComSpec & ' /c wmic bios get serialnumber | find /v /i "SerialNumber" > ' & $TempFile)
    Sleep(1000)
    Global $file = FileOpen($TempFile, 0)
    While 1
        Global $Serial = FileRead($file, 50)
        If @error = -1 Then ExitLoop
        FileWriteLine($PCOnboardingTicket, "SERIAL          : " & $Serial)
    WEnd
    FileClose($file)
    Call("Processor")
EndFunc   ;==>Serial

Func Processor()
    ;Processor
    RunWait(@ComSpec & ' /c wmic cpu get Name | find /v /i "Name" > ' & $TempFile)
    Sleep(1000)
    Global $file = FileOpen($TempFile, 0)
    While 1
        Global $Processor = FileRead($file, 50)
        If @error = -1 Then ExitLoop
        FileWriteLine($PCOnboardingTicket, "PROCESSOR       : " & $Processor)
        ProgressSet("25", "25%", "Scanning System...")
        Sleep(1000)
    WEnd
    FileClose($file)
    Call("RAM")
EndFunc   ;==>Processor


Func RAM()
    ;RAM
    RunWait(@ComSpec & ' /c wmic computersystem get TotalPhysicalMemory | find /v /i "TotalPhysicalMemory" > ' & $TempFile)
    Sleep(1000)
    Global $file = FileOpen($TempFile, 0)
    While 1
        Global $TotalMemory = FileRead($file, 50)
        If @error = -1 Then ExitLoop
        FileWriteLine($PCOnboardingTicket, "Total Memory    : " & $TotalMemory)
        RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
        RunWait(@ComSpec & ' /c ECHO ______________________________ NETWORK CONFIG __________________________________ >> ' & $PCOnboardingTicket)
        RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    WEnd
    FileClose($file)
    Call("DHCPEnabled")
EndFunc   ;==>RAM

Func DHCPEnabled()
    ;DHCP Enabled
    RunWait(@ComSpec & ' /c wmic NICCONFIG WHERE IPEnabled=true GET DHCPEnabled | find /v /i "DHCPEnabled" > ' & $TempFile)
    Sleep(1000)
    Global $file = FileOpen($TempFile, 0)
    While 1
        Global $DHCPStatus = FileRead($file, 50)
        If @error = -1 Then ExitLoop
        RunWait(@ComSpec & ' /c ECHO IP ADDRESS: ' & @IPAddress1 & ' >> ' & $PCOnboardingTicket)
        FileWriteLine($PCOnboardingTicket, "DHCP Enabled?: " & $DHCPStatus)
    WEnd
    FileClose($file)
    Call("IPGateway")
EndFunc   ;==>DHCPEnabled

Func IPGateway()
    ;IP Gateway
    RunWait(@ComSpec & ' /cwmic NICCONFIG WHERE IPEnabled=true GET DefaultIPGateway | find /v /i "DefaultIPGateway" > ' & $TempFile)
    Sleep(1000)
    Global $file = FileOpen($TempFile, 0)
    While 1
        Global $Gateway = FileRead($file, 50)
        If @error = -1 Then ExitLoop
        FileWriteLine($PCOnboardingTicket, "IP Gateway: " & $Gateway)
        ProgressSet("50", "50%", "Scanning System...")
        Sleep(2000)
    WEnd
    FileClose($file)
    Call("_FinishBuildTicket")
EndFunc   ;==>IPGateway

FileCreate()

Func FileCreate()
    _FileCreate(@WorkingDir & 'ONBOARDING_PC_SPREADSHEET.csv')
    Call("_FinishBuildTicket")
EndFunc   ;==>FileCreate

Func _FinishBuildTicket()
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingSpreadsheet)
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO _________________________ MAPPED DRIVES AND PRINTERS _________________________ >> ' & $PCOnboardingTicket)
    ; Use wmic to get active mapped drives
    RunWait(@ComSpec & ' /c wmic netuse get LocalName,Persistent,RemotePath | find /v /i "FALSE" | sort >> ' & $PCOnboardingTicket)
    ; Use wmic to get printers, ports, and drivers
    RunWait(@ComSpec & ' /c wmic printer get name,PortName,DriverName | sort >>  ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO. >> ' & $PCOnboardingTicket)
    ProgressSet("75", "75%", "Compiling findings...")
    RunWait(@ComSpec & ' /c ECHO ________________________________ SOFTWARE ____________________________________ >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO.  >> ' & $PCOnboardingTicket)
    RunWait(@ComSpec & ' /c ECHO Name                                                                     Vendor                   Version >> ' & $PCOnboardingTicket)
    ; Use wmic to get installed apps
    ProgressSet("90", "90%", "Almost there...")
    Sleep(1000)
    RunWait(@ComSpec & ' /c wmic product get name,vendor,version | find /v "Name" | sort >> ' & $PCOnboardingTicket)
    FileWrite($PCOnboardingSpreadsheet, $MAPLOCATION & ',' & $devicetype & ',' & $Make & ',' & $Model & ',' & $Serial & ',' & @IPAddress1 & ',' & $DHCPStatus & ',' & $networktype & ',' & @ComputerName & ',' & $Processor & ',' & $TotalMemory & ',' & $Userfullname & ',' & $SharedAccount & ',' & $WallJack & ',' & $OtherWallJack)
    ProgressSet(100, "Done", "Complete")
    Sleep(2000)
    ProgressOff()
    RunWait(@ComSpec & ' /c ECHO.  >> ' & $PCOnboardingTicket)
    BlockInput(0)
    Call("_Finish")
EndFunc   ;==>_FinishBuildTicket

Func _Finish()
    Local $iAnswer = MsgBox(BitOR($MB_OK, $MB_SYSTEMMODAL, $MB_ICONINFORMATION, $MB_SETFOREGROUND), " Onboarding Survey v4", "Survey is complete!" & @CRLF & @CRLF & "Report saved to: " & $PCOnboardingTicket & @CRLF & @CRLF & "Thank you!")
    If $iAnswer = 1 Then Exit
EndFunc   ;==>_Finish

 

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now