Find the Service Tags of all Computers in your environment using Powershell and bat

Log into the domain controller or a workstation which has Active Directory Powershell module

Run the following command to create a list of computers in your environment

Get-ADComputer -Filter * |ft Name |Out-File computers.txt

Open notepad and create a bat file with the wmic call parsing the newly created computer text file.

wmic /NODE:@"c:\scripts\computers.txt"

bios get serialnumber > serialnumbers.txt

Execute the bat

Decrypt all files on a file Server effected by Cryptolocker with Powershell

Go to the fireye website to see if your version of Cryptolockers key has been found https://www.decryptcryptolocker.com/

Upload an unlockable file and wait for the email with the encryption key to be sent. Paste the encryption key into the key parameter for the script.

Save the script and the decryptolocker.exe command in the same folder path.

Change the parameter for the fileserver to match the effected directory

This script will traverse directories and parse through all items which match the encryption key, create a backup and then restore the file. Make sure you have adequate room on your fileserver to hold twice the amount of data.

If all the files are not successfully decrypted it is likely your site was hit by multiple instances of Cryptolocker and a separate key will be needed for the additional files.

After the files are verified as successfully decrypted go ahead and delete the backup copies.

$key = "-----BEGIN RSA PRIVATE KEY----- Key from email Pasted here -----END RSA PRIVATE KEY----- "

$list = (get-childitem  "\\fileserver\user shares" | where {$_.PSIsContainer} | select-object FullName)

foreach ($_ in $list) { echo "Yes" | .\Decryptolocker.exe --key "$key" -r $_.FullName }

Create a Report from SQL and email it in Powershell

Create a Report from SQL and email it in Powershell

 #Connection Strings

    $Database = "MSSQL"

$Server = "SQL-SERVER"

    #SMTP Relay Server

    $SMTPServer = "relay.mailserver.org"

    #Export File

    $AttachmentPath = "C:\Scripts\report.csv"

    # Connect to SQL and query data, extract data. The SQL Query Commandlet will take any Query in sqlcmd

    $SqlQuery = "SELECT Item1, Item2, Item3, Item4    FROM sometable where TimeStamp >= GETDATE() - 1 "

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd.CommandText = $SqlQuery

    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet

    $nRecs = $SqlAdapter.Fill($DataSet)

    $nRecs | Out-Null

    #Populate Temporary Table

    $objTable = $DataSet.Tables[0]

    #Export Data to CSV File

    $objTable | Export-CSV $AttachmentPath

    #Send SMTP Message

    $Mailer = new-object Net.Mail.SMTPclient($SMTPServer)

    $From = "reports@yourdomain.com"

    $To = "managers@yourdomain.com"

    $Subject = "Daily Report"

    $Body = "Attached is the Daily Report"

    $Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)

    $Msg.IsBodyHTML = $False

    $Attachment = new-object Net.Mail.Attachment($AttachmentPath)

    $Msg.attachments.add($Attachment)

    $Mailer.send($Msg)

XML Parsing in Powershell

Get the schema in xml

[xml]$xml = Get-Content .\acesdata.xml

Open up the file in excel as an XML Source Task Pane to find which node you are going to start parsing

In this example the Node will be Header and the fields I want to parse are Company, TransferDate, and EffectiveDate

Run the following command to parse the data

$xml.SelectNodes('//Header') | Select Company,TransferDate,EffectiveDate |Export-csv aces.csv