This is a podcast from David Hoezler’s YouTube channel that reviews User Object extraction via Powershell.
Tag: PowerShell
List Mapped Drives of Remote Computers using Powershell
This Powershell script is pieced together from a handful of source (noted in script) that will return mapped drives of remote computers of ALL the users.
# cooked by Rich Kreider# bits and pieces from: https://gist.github.com/881412 # http://technet.microsoft.com/en-us/library/ff730940.aspx # http://andrew-henderson.me.uk/computing/powershellmenu/182-powershellgetremoteloggedonusername # http://serverfault.com/questions/81797/script-to-list-current-users-mapped-network-drives # http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_25075337.html # http://msdn.microsoft.com/en-us/library/windows/desktop/aa394194(v=vs.85).aspx # http://blogs.msdn.com/b/powershell/archive/2006/11/03/erroraction-and-errorvariable.aspx # http://blogs.msdn.com/b/dmuscett/archive/2009/05/27/get_2d00_wmicustom.aspx # http://poshcode.org/882 # http://blogs.technet.com/b/heyscriptingguy/archive/2005/10/27/how-can-i-determine-which-drives-are-mapped-to-network-shares.aspx #$objUser = New-Object System.Security.Principal.NTAccount("fabrikam", "jdoe") # get logged on user #$loggedOn = gwmi win32_computersystem -ComputerName . -namespace "rootcimv2" # the following gets username and sid #$objUser = new-object system.security.principal.ntaccount("fabrikam", #$loggedOn.username.split("")[1]) #$strSID = #$objUser.Translate([System.Security.Principal.SecurityIdentifier]) #"SID: "+$strSID.Value #"Username: "+$loggedOn.UserName.split("")[1] $saveAs = 'c:ManualMappings.csv' $ErrorActionPreference="SilentlyContinue" $USE_ADS=False # Set this to false if you don't have Quest ActiveRoleManagement snapin. You can download: http://www.quest.com/powershell/activeroles-server.aspx function getData($data) { write-host "`tEnumerating SubKeys" foreach ($key in $HKU.GetSubKeyNames()) { $keys = $HKU.OpenSubKey($key + 'Network') # Move on to the next $key if the 'Network' sub key is empty or non-existent if ($keys.SubKeyCount -le 0) { continue } # Iterate through each drive letter (a corresponding key name) and output drive mapping foreach ($driveletter in $keys.GetSubKeyNames()) { $values = $keys.OpenSubKey($driveletter) # Attempt to translate the SID into a friendly username try { $sid = New-Object System.Security.Principal.SecurityIdentifier($key) $user = $sid.Translate([System.Security.Principal.NTAccount]) } # Fallback to displaying SID catch { $user = $key } # Output result to file specified in $saveAs $matches.name+",$user,$driveletter," + $values.GetValue('RemotePath') | Add-Content $saveAs } } write-host "`tDone" } if ($USE_ADS -eq $TRUE) { add-pssnapin quest.activeroles.admanagement $computers = get-qadcomputer | foreach-object {$_.name} } else { $computers=switch -regex (net view) { "^(? S+)s+" { $matches.name }} } foreach ($computer in $computers) { write-host "Trying " $computer $HKU = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('Users', $computer) if ($? -eq $FALSE) # $? is a Boolean value that holds the status of the last operation: success or failure. { write-host "`tRemoteRegistry Connect Fail" # maybe remote registry wasn't enabled, try enabling and run again write-host "`tAttempting to start RemoteRegistry" (gwmi win32_service -filter "Name='RemoteRegistry'" -computer $computer -ea silentlycontinue).invokemethod("StartService",$null) | out-null if ($? -eq $FALSE) { write-host "`tNot able to start RemoteRegistry" } else { write-host "`tRemoteRegistry Started" $HKU = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('Users', $computer) if ($? -eq $FALSE) { write-host "`tFailed to Connect and Start RemoteRegistry" } else { write-host "`tRemoteRegistry Connected" $computer+",N/A,N/A,N/A" | add-content $saveAs } } } else { write-host "`tRemoteRegistry Connected" getData($HKU.GetSubKeyNames()) } } #end-foreach
There’s probably a handful of issues with this but I at least wanted to get the script up so I could reference it. I’ll clean it up when I get some free time and make it work faster/better but I just don’t have the time right now.
Visualizing SQLIO: Disk benchmark results using a PivotChart
This is a quick video of what I was working on for a little today. I created a batch script that will benchmark a disk(s) using SQLIO and record the output to a single file (or multiple, if necessary). After benchmarking completes, I run SQLIOResults and choose the output file that was created by my batch script.
Once SQLIOResults parses the SQLIO output and finishes inserting it into Excel, I create a PivotChart to compare the disk(s) IOPS and MB/s at each of the different test levels.
Hopefully someone else will find this useful. Also, if anyone knows how I can create a pivot chart in Powershell (if there’s a COM call), please let me know so I can completely automate this!
SQLIO Scripts and Graphs
One of probably hundreds of SQL scripts out there. This is what I made and use… and I like it.
There are a lot of other scripts out there that are available and even an SQLIO GUI. Here are a few I looked at myself.
SQLIOToolSet
SQLIO GUI (screenshot)
SQLIO.Scripts
My script is a basic DOS batch file that automates collecting statistics on a few different drives when you’re testing. You need to make sure you have created the %TESTFILE% on the root of the drives you want to test. I create, and I suspect most others do as well, a testfile.dat on the root of each drive I test.
You’ll need to create this before running the script or it will break!
If you want to extend this script, it’s easy. All the testing is put into a function called RUN_BENCHMARK which is called and passed a drive letter argument. So if you want to add more tests, feel free. Just mimic the existing code in that stanza and you should be ok.
@echo off setlocal : This script will benchmark a HDD using SQLIO and store results in the : RESULTS_LOGFILE_NAME%_%X.txt where X is the drive letter(s) benchmarked : in the current path and TIME is the timestamp the script was run. : YOU MUST HAVE CREATED THE %TESTFILE% ON EACH DRIVE. : BUGFIX: Set time; strip colon and spaces. Was erroring on on spaces. set STRTIME=%TIME::=% set STRTIME=%STRTIME: =% : Set duration of each test in seconds set DURATION=30 : Set the buffering. N=none, Y=all, H=hdwr, S=sfwr set BUFFERING=H : Set latencies from (S=system, P=processor) timer set LATENCY=S : Filename of test file created already. You can specify a full path if needed. If spaces : exist in path or name, use quotes. set TESTFILE=testfile.dat : The log file name (prepend) - log file is stored in the same location this script is run : from. You could specifiy a path if you wanted. set RESULTS_LOGFILE_NAME=%COMPUTERNAME%_%STRTIME%_results : Drive(s) to run the tests on. Separate each drive with a comma. set TEST_DRIVES=E,F,G : Set the log to either 1-combine or 0-separate. If separated, a file will be generated : for each TEST_DRIVES disk. set COMBINE_LOG=1 : I should do some error checking, but I'm not going to. : Future plans: IF [%TEST_DRIVES%]==[] call:MissingDrive & goto EOF cls color 1F echo Benchmark of drive(s) %TEST_DRIVES% start: %DATE% %TIME% echo. echo. echo This will take some time. The background will become green once the echo benchmarking completes. Go grab some coffee and read up on the latest echo news of the world. echo. echo. for /D %%D in (%TEST_DRIVES%) do (call:RUN_BENCHMARK %%D) GOTO END :RUN_BENCHMARK IF [%1]==[] call:MissingDrive IF [%COMBINE_LOG%]==[1] ( set APPEND=COMBINED ) ELSE ( set APPEND=%1 ) echo Benchmark [%1] begin: %DATE% %TIME% echo [%1] RANDOM WRITES (64K) sqlio -d%1 -B%BUFFERING% -kW -frandom -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] RANDOM READS (64K) sqlio -d%1 -B%BUFFERING% -kR -frandom -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o2 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o4 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o8 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o16 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o32 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o64 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o128 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] SEQUENTIAL READS (64K) sqlio -d%1 -B%BUFFERING% -kR -fsequential -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o2 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o4 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o8 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o16 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o32 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o64 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o128 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] SEQUENTIAL WRITES (8K blocks to mimic SQL Logfiles) sqlio -d%1 -B%BUFFERING% -kW -fsequential -t1 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t2 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t4 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o2 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o4 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o8 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o16 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o32 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o64 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o128 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo Benchmark [%1] complete: %DATE% %TIME% echo. echo. GOTO EOF :MissingDrive color CF echo Missing drive. Specify %%TEST_DRIVE%% variable please. goto EOF :END color 2F echo Benchmark of drive(s) [%TEST_DRIVES%] completed: %DATE% %TIME% endlocal :EOF