Jump to content

Outputting to csv/excel format help

barakdulac
 Share

Recommended Posts

barakdulac Seeker

barakdulac

Posted
Posted

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

 

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...