PowerShell - Summary Of Files By Extension

Knowing in advance all the possible file types is a more efficient way to produce this type of report, but letting the code figure that out is more practical.  This report will start at the given $Target directory and summarize the number of files and total bytes by extension, in alphabetical order, to the whatever $ReportFile you select.

# Note: This report was written and tested using PowerShell V3
#
# Change the starting directory and report name to suit your needs
#
$Target = "C:\YourChoiceOfDirectory"
$ReportFile = "C:\YourChoiceOfReportName.txt"
$AllTypes = @()
#
# Write Report Header Lines
#
$Message = "All File Summary Report"
($Message) | Out-File $ReportFile
$Message = "                       "
($Message) | Out-File $ReportFile -Append
$Message = "FileType        Files                    Bytes"
($Message) | Out-File $ReportFile -Append
$Message = "---------  ----------  -----------------------"
($Message) | Out-File $ReportFile -Append
#
# Get every filename and produce an array of unique extensions
#
$List = Get-ChildItem $Target -File -Recurse
Foreach ($Item in $List) {
   $FileName= $Item.FullName
   $FileSplit = $FileName.split(“.”)
   $FilePieces = $FileSplit.Count
   $FileType = $FileSplit[$FilePieces-1]
   $AllTypes += $FileType
   }
$extensions = $AllTypes | Sort-Object -Unique
#
# Get a list of files for a specific extension, count the number of
# files and their total size and write that info to the report file.
# Repeat for each extension found.
#
Foreach ($ext in $extensions)  {
   $Filename = Get-ChildItem $Target -File *.$ext -Recurse
   $Files = 0; $Size = 0;
   Foreach ($Item in $Filename) {
     $Files++
     $Size = $Size + $Item.Length
      }
   $ext = $ext.PadRight(10,' ')
   $Files = $Files.ToString('N0').PadLeft(11,' ')
   $Size = $Size.ToString('N0').PadLeft(25,' ')
   $Message = "$ext$Files$Size"
   ($Message) | Out-File $ReportFile -Append

SQL Statements

At some point in the past I've used each of the following SQL statements to accomplish some required task and not wanting to Google Search for the same things twice, staring collecting them in a list for faster reference.  Some of these may not work with all versions of SQL Server and as new features are introduced may not be the best way to perform a specific task.  


Row Counts for Every Table in a Database

SELECT o.name, rows

FROM sysindexes i join sysobjects o on o.id=i.id

WHERE indid < 2 and type='U'

ORDER BY rows DESC

Get Information on All Columns for All Tables in a Database

SELECT table_schema, table_name, column_name, ordinal_position,

      column_default, data_type, character_maximum_length

FROM information_schema.columns

The Last Time a Table was Updated For All Tables in a Database

SELECT DISTINCT OBJECT_NAME(object_id,database_id) as TableName, last_user_update

FROM database name.sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('database name')

GROUP BY OBJECT_NAME(object_id,database_id), last_user_update

ORDER BY TableName

Find String in Any Object in a Database

SELECT DISTINCT so.name

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE sc.TEXT LIKE '%string%'

Backup a Database

BACKUP DATABASE databasename

TO DISK = 'path\name.bak';

Restore Database to Another Location

RESTORE DATABASE databasename

FROM DISK = 'path\name.bak'

WITH RECOVERY,

MOVE 'databasename_Data' TO 'newpath\databasename_Data.MDF'

MOVE 'databasename_Log'  TO 'newpath\databasename_Data.LDF'

Copy Results from a Query to a Pipe-Delimited File

BCP "SELECT * FROM [database].[owner].[table_name]" queryout filename -Uuserid -Ppassword -t"|" -c -S server_name\instance

Change User Password

EXEC sp_password 'old password', 'new password', 'userid’

Select Rows Between Two Datetimes

SELECT *

FROM tablename

WHERE field BETWEEN '10/15/2015 00:00:00.00'

               AND '10/15/2015 23:59:59.999'

Select Yesterday's Data

SELECT *

FROM table

WHERE date_field >= dateadd(day,datediff(day,1,GETDATE()),0)

  AND date_field < dateadd(day,datediff(day,0,GETDATE()),0)

Count the Number of Occurances of Each Value

SELECT DISTINCT column_name, count(column_name) as CountOf

FROM tablename

GROUP BY column_name

Count Rows By Year

SELECT DISTINCT YEAR(datetime_field) as Year, COUNT(*) as Rows

FROM tablename

GROUP BY YEAR(datetime_field)

ORDER BY YEAR(datetime_field) DESC

Update Rows Based on a Time Difference in Minutes

UPDATE tablename

SET field = field

WHERE DATEDIFF(MINUTE,datetime,CURRENT_TIMESTAMP) < minutes

Replace String in a Field

UPDATE tablename
SET field = REPLACE(field, 'text', 'newtext')

Replace Substring in a Column

UPDATE tablename

SET field = CAST(REPLACE(CAST(field as NVarchar(4000)),'string1','string2') AS NText)

WHERE field LIKE '%string1%'

Delete Rows from a Table Between Two Datetimes

DELETE FROM tablename

WHERE date_field BETWEEN 'mm/dd/yyyy 00:00:00.00' AND 'mm/dd/yyyy 23:59:59.999'

Left Join

SELECT A.field1, A.field2, B.field3

FROM tablename1 A

LEFT JOIN tablename2 B

ON A.field1=B.field1

AWK - Directory to Rows

This AWK script parses the output of the Windows "DIR *.* /s" command and puts in columnar format with column titles. This makes it easy to open in your favorite spreadsheet program using a fixed column format.

#

BEGIN {

   record="  ";                               # Start output record at 2 spaces

   for (i = 1; i <= 7; ++i) {record=record record;}   # Expand output record to 256 spaces

   record=repl(record,1,14,"Date");                   # Date goes starts at column 1

   record=repl(record,12,26,"Size");                  # File size starts at column 12

   record=repl(record,28,87,"Filename");              # File name starts in column 28

   record=repl(record,89,148,"Fullpath");             # Full path starts in column 89

   print record;                                      # Print the header line

}

function repl(s,f,t,v)                                 # Custom function to add data to the output record

{ return substr(s,1,f-1) sprintf("%-*s", t-f+1, v) substr(s,t+1) }

{

if ( substr($0,2,9) == "Directory" ) {                 # If this line contains the directory name,

   dir=substr($0,15,60);                              # save the name

   }

else if (substr($0,3,1) == "/" &&                   

        substr($0,6,1) == "/" &&

        substr($0,25,1) != "<")   { # If this line contains a file ...

   date=substr($0,1,10);                              # Date

   size=substr($0,24,15);                             # File size

   file=substr($0,40,60);                             # File name

   name=dir "\\" file;                                # Full path

   record="  ";                               # Start output record at 2 spaces

   for (i = 1; i <= 7; ++i) {record=record record;}   # Expand output record to 256 spaces

   record=repl(record,1,10,date);                     # Date goes starts at column 1

   record=repl(record,12,26,size);                    # File size starts at column 12

   record=repl(record,28,87,file);                    # File name starts in column 28

   record=repl(record,89,148,dir);                    # Full path starts in column 89

   print record;                                      # Print the output line

   }

}

AWK - Hex to Binary

This AWK code is used to translate data that has been exported from Wireshark using
File ... Export Packet Dissections ... As Plain Text ..., checking only the Packet Bytes box
in the Packet Format section (unchecking all others). These print lines are hex characters,
which this code will translate to binary. This can be useful to create a file (e.g. PDF, JPG)
from a packet capture.



BEGIN {BINMODE = 2;}                       # BINMODE of 2 sets writing output to binary mode

{ if ($1 > "0020") {                       # Ignore the lines starting with 0000, 0010 and 0020

   x=0;                                   # There are 16 bytes on each line; Begin at offset 0

   if ($1 == "0030") {x=6;}               # Ignore the first 6 bytes of the 0030 line

   while (x < 16) {                       # We start after bypassing the network header bytes

     n=0;                               # Initialize the binary number to output

     pos = 7+(x*3);                       # Calculate the position of the first hex byte

     hex = substr($0,pos,1);              # Grab the first hex byte

     if (hex == " ") {next;}              # If a blank is found, there are no more bytes

     if (hex <= "9") {n=hex*16;}          # Use multiplication for hex digits 0-9

     if (hex == "a") {n=160;}             # Assign the other hex digits to their binary numbers

     if (hex == "b") {n=176;}

     if (hex == "c") {n=192;}

     if (hex == "d") {n=208;}

     if (hex == "e") {n=224;}

     if (hex == "f") {n=240;}

     hex = substr($0,pos+1,1);            # Grab the second hex byte

     if (hex <= "9") {n=n+hex;}           # Use addition for hex digits 0-9

     if (hex == "a") {n=n+10;}            # Add the other hex digits to their binary numbers

     if (hex == "b") {n=n+11;}

     if (hex == "c") {n=n+12;}

     if (hex == "d") {n=n+13;}

     if (hex == "e") {n=n+14;}

     if (hex == "f") {n=n+15;}

                                          # It is required to print directly to a file using the

     printf "%c", n > "output.bin";       # “>” syntax to avoid AWK inserting a CR before each LF

     x++;                               # Go to the next hex byte on this line                   

     }

   }

}