16. May 2012 by Bennett.

Pretty simple script I wrote a while back. Moves files between two paths. For example, to sweep files from a DMZ server. Optionally renames files on the target system by appending a date code. This is to avoid name collisions. Pay close attention to $FileAge, which sets a lower limit on the age of files to copy and $RenameDelay, which sets an upper limit on the age of files to rename in the destination path. Change the copy-item method to the move-item method before going to production.

set-StrictMode -version 2

$FileAge = 30 #age of files in minutes
$RenameDelay = 5 #delay value for renaming files
$Source = “\\server1\path1\” #source folder
$Destination =”\\server2\path2\” #destination folder

$Past = (Get-Date).AddMinutes($FileAge * -1)
$Past2 = (Get-Date).AddMinutes($RenameDelay * -1)

#first pass - move files form source to destination
$List = dir $Source -recurse | Where {$_.LastWriteTime -lt $Past -and $_.name -notlike “someString*” -and $_.name -notlike “SomeOtherString*”}
if ($list -ne $null){
write-host $list.count “items found to move”
foreach($file in $list){
#$file.Fullname
If ($file.PSIsContainer -eq $false) {
$MoveDirectory = (join-path -path $Destination -childpath $file.DirectoryName.SubString(14))
new-item -path $MoveDirectory -type directory -ErrorAction:SilentlyContinue #create destination folder if it doesn’t already exist
}
$MovePath = (join-path -path $Destination -childpath $file.FullName.SubString(15))
copy-item -Path $file.FullName -destination $MovePath -force #move file to destination folder
}
}
else {write-host “no matching items found to move”}

#second pass - rename files in destination
$List = dir $destination -recurse | Where {$_.CreationTime -gt $Past2 -and $_.PSIsContainer -eq $false -and $_.name -notlike “SomeString*” -and $_.name -notlike “SomeOtherString*” }
if ($list -ne $null){
write-host $list.count “items found to rename”
foreach($file in $list){
$file = ( rename-item -force –path $file.Fullname –Newname ( $file.basename + (get-date -format ‘_yyyy-MM-dd_hhmm’) + $file.extension))
}
}
else {write-host “no matching items found to rename”}

set-StrictMode -Off

Posted in Powershell, Windows OS | Print | No Comments »

14. April 2011 by admin.

Perhaps this should be categorized as a stupid people trick.  On the other hand, it’s a good, cheap way to deduplicate files assuming you’re running SQL Server and LogParser.  This post assumes at least a basic knowledge of T-SQL and LogParser command line syntax.

Basically, you use LogParser to create a two-column table containing file paths and MD5 hashes like this:

LogParser “SELECT path, HASHMD5_FILE(Path) AS MD5 INTO T_FILE_INFO FROM D:\some_path\*.*” -i:FS  -o:SQL  -database:files -driver:”SQL Server”  -createTable:ON

The -o parameter directs LogParser to write the output to a SQL Server database table.

The T-SQL  ranking function makes it very easy to find and pick out duplicate rows.  Instead of finding duplicate rows, I use it to generate statements to delete duplicate files.  The entire script looks something like this:


USE [TempDB]
GO

– Create table to store file paths and MD5 hashes
IF  EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N’[dbo].[#T_FILE_INFO]’) AND TYPE IN (N’U’))
DROP TABLE [dbo].[#T_FILE_INFO]
GO
CREATE TABLE [dbo].[#T_FILE_INFO](
[PATH] [varchar](512) NULL,
[MD5] [varchar](255) NULL
) ON [PRIMARY]
GO
– run LogParser to populate table.  Either run from CMD prompt or use XP_CMDSHELL

– note: this step can be time consuming, since LogParser must generate an MD5 has for every file specified

EXEC xp_cmdshell @LogParserCMD;

SELECT ‘DEL “‘ + PATH + ‘”‘
FROM tblCTE
WHERE Ranking > 1
AND MD5 IS NOT NULL;
– Review the result set and run it from a command prompt

– clean up after yourself
DROP TABLE #T_FILE_INFO

Posted in Utilities, T-SQL, Windows OS | Print | No Comments »

20. January 2010 by admin.

Here’s an extremely concise PowerShell script to remove old files.  You could use it to enforce a policy that keeps 90 days of ETL input files on a server.  Files older than that would purged with this script.  The script would be invoked by a SQLAgent CMDExec task or a Windows Task Scheduler job.

#Powershell Script to delete files older than a certain age

$intFileAge = 90  #age of files in days

$strFilePath = “c:\archive” #path to clean up

#create filter to exclude folders and files newer than specified age

Filter Select-FileAge 

      {

      param($days)

      If ($_.PSisContainer) {}

              # Exclude folders from result set

      ElseIf ($_.LastWriteTime -lt (Get-Date).AddDays($days * -1))

            {$_}

      }

      get-Childitem -recurse $strFilePath | Select-FileAge $intFileAge ‘CreationTime’ | remove-item 

Posted in Utilities, Windows OS | Print | 3 Comments »

11. June 2009 by Bennett.

You can use LogParser (a free, unsupported utility from Microsoft) to quickly import a CSV file into a SQL Server table using syntax like this:

logparser “SELECT * INTO MyTable FROM d:\MyFile.csv” -i:csv -o:SQL -server:MyServer -database:MyDatabase -createTable:ON

There are many other uses for LogParser.  Two of the most common are analyzing Windows Event Logs and IIS logs. 

Posted in Windows OS, SQL Server | Print | No Comments »

17. December 2008 by Bennett.

You may run across the situation where you need to import an existing Perfmon (or System Monitor) binary log file into and Excel spreadsheet or SQL database.  The obvious first step is to convert it to CSV format.  It took googling numerous forums to figure out how to do this, so I thought that I’d document it here for future reference.

The relog.exe utility, which ships with the OS, has the ability to convert between Perfom log file formats and merge several log files into one file.  It has some nice filtering capabilities including filtering by time, counters, and nth record.  Example syntax for simply converting between file formats:

relog -f csv inputfile.blg -o outputFile.csv

You can display all the options by simply typing relog -? at the command prompt.

Posted in Utilities, Windows OS, Uncategorized | Print | 1 Comment »

9. June 2008 by Bennett.

The TCP Chimmney Offload feature was introduced in Windows Server 2003 SP2.  It can offload certain functions from the OS to the network adapter card.  A lot of our newer HP Servers have a Broadcomm Ethernet chipset that supports this function.   It isn’t without its issues.  To determine if it is in use you can run the netstat –nt command like this:

C:\>netstat -nt

 Active Connections

   Proto  Local Address          Foreign Address        State           Offload State
  TCP    192.168.4.97:445        192.168.4.13:2167       ESTABLISHED     InHost
  TCP    192.168.4.97:1433       192.168.4.95:3560       ESTABLISHED     InHost
  TCP    192.168.4.97:1433       192.168.4.95:3617       ESTABLISHED     InHost

The last column shows the offload status.  In this example the offload state is shown as InHost for all connections, so TCP offload is not enabled.

Posted in Windows OS | Print | No Comments »

4. May 2008 by Bennett.

Today while applying patches to a couple of mission-critical servers, I was reminded of something:  reboots issued through the terminal services client are not always successful.  The server can get stuck in an odd state where it is running but terminal services is not working.  For me the workaround has been to issue a remote reboot command using the shutdown utility:

shutdown /r  /m \\server_name_to_reboot /t 0  /f

Posted in Windows OS | Print | No Comments »

10. April 2008 by Bennett.

It’s been a while since I worked on my cluster lab. The first thing that I did was to tear down the old cluster and start over from scratch. The first lesson from this round of testing came when the cluster administrator scanned the hardware resoruces and threw a warning message that stated that only one adapter was found on the node. This warning is caused by fact that the two cluster nodes are interconnected via a crossover cable and the second node is shut down. With the node shut down, there is no physical layer link on the Ethernet card, so the OS thinks that the cable is disconnected.

I got past the error by simply plugging the cluster member’s private network connection into a spare switch that I had lying around. This provided a physical layer link which was what the OS needed to report a valid network connection. After that I rescanned the resources and the cluster installation proceeded.

Posted in Windows OS | Print | No Comments »

31. March 2008 by Bennett.

There is an EMC whitepaper titled Using diskpar and diskpart to align partitions on Windows Basic and Dynamic Disks. This paper describes how legacy Windows issues cause a single disk I/O to span multiple tracks. This puts an unnecessary load on the SAN and reduces performance. The fix is to align the partition. Below is a sample session using the DISKPART utility. Comments are in red.

Invoke Diskpart

C:\>diskpart

Microsoft DiskPart version 5.2.3790.3959

Copyright (C) 1999-2001 Microsoft Corporation.

On computer: MYTESTBOX

You must select the disk to act on

DISKPART> select disk 5

Disk 5 is now the selected disk.

Create a partition using the EMC-recommended alignment offset

DISKPART> create partition primary align=64

DiskPart succeeded in creating the specified partition.

Select the partition / volume that you just created

DISKPART> select volume=0

Volume 0 is the selected volume.

Assign a drive letter

DISKPART> assign letter=o

DiskPart successfully assigned the drive letter or mount point.

Exit Diskpart

DISKPART> exit

Leaving DiskPart…

C:\>

You can now use the disk management MMC to format the partition

Posted in Windows OS, Performance | Print | 6 Comments »

3. March 2008 by Bennett.

Performance logging will not start on its own at boot time. Here’s how to get around that problem.

 

Applies to: W2k3, XP

1. Using the Performance MMC, create a set of counter logs.
2. Use a specific name to save the settings, e.g., SQLPERF.
3. At the command prompt, try starting and stopping performance logging using the logman.exe utility:
   1. logman start sqlperf
   2. logman stop sqlperf
4. Set the Windows registry to automatically start performance counters at system startup
   1. Open the Registry Editor, regedit.exe.
   2. Navigate to HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
   3. Add a string value. A good name would be logman.
   4. Set the value equal to logman start sqlperf (or the command in step 4.a. that successfully started performance logging).

References:

1. http://support.microsoft.com/kb/303133
2. http://www.jsifaq.com/SF/Tips/Tip.aspx?id=4455

Posted in Windows OS, Performance | Print | No Comments »

24. January 2008 by Bennett.

A new version of the SQL Server 2005 Best Practices Analyzer (BPA) is here.

On a related note, the BPA reported several operating system issues that I was unaware of, including an issue where SQL Server’s working set gets paged to disk during large file copies. More info and a link to the Technet article discussing this issue can be found here.

Posted in Windows OS, SQL Server | Print | No Comments »

15. September 2007 by admin.

SET mydate=%DATE:~4,2%_%DATE:~7,2%_%DATE:~10,4%
echo mydate >> somefile.txt

Posted in Windows OS | Print | No Comments »

16. April 2007 by Bennett.

I recently built an Windows Server 2003 cluster lab using a couple of PCs, two Adaptec AHA2940UW controllers and an old SCSI drive. When configuring the SCSI HBAs, it is important to remember to disable SCSI resets.

Note that when shopping for surplus SCSI HBAs, the AHA2940UW is probably the oldest card that you can use. It has a flash-programmable BIOS.   You can download the last BIOS version produced for that card from Adaptec’s website.  The older 2940 (without the U in the part number) uses pluggable EPROMS.

Posted in Windows OS | Print | No Comments »

20. February 2007 by Bennett.

While working on a client’s Exchange server, I stumbled across an interesting knowledgebase article: SMTP tar pit feature for Microsoft Windows Server 2003. Tarpits insert a delay into certain SMTP communications that are associated with spam or with other unwanted traffic. They work by slowing all responses that contain SMTP protocol 5.x.x error codes. For the Microsoft SMTP server the delay value is configurable. There are some caveats as to whether or not this will be effective in your environment and whether or not it will slow legitimate traffic. It is definitely worth checking out.

On a related note, it would be interesting to put a LaBrea Tarpit on the top and bottom IPs of an Internet facing subnet. For example, if I owned subnet 207.103.204.0/24, I would bind addresses 207.103.204.1 and 207.103.204.254 to a LaBrea host. Zombie computers often do linear address space scans looking for victim machines. Having a tarpit at either end of the subnet could could significantly slow zombie activity. It would also tend to clean up much of that junk that you see in your HTTP or IDS logs.

Posted in Windows OS | Print | No Comments »

18. February 2007 by Bennett.

Wouldn’t it be nice to have the time once a week to run the dcdiag against all of your domain controllers and review the results? (yeah, right) Well, I wrote a script to automate that process. It works like this: First there is a subroutine that enumerates the DCs and puts the result into an array of strings. Next there is a function that runs DCDiag against the list of DCs looking for the string “failure”. If that string is found it concatenates the server name and failure string to a global string variable. At the end of this function, if a failure was found, the function returns “True”. If the function returns true, the script e-mails an alert with the necessary information.

Posted in Windows OS | Print | No Comments »