Batch File ISO 8601 Date Format – Version 2.0

16th March, 2013 7 comments

Last year I wrote about using the ISO 8601 date format in Batch Files. One of the issues I had was that times before 10:00 were not preceded with a zero.

The solution was quite simple, in that you can use an in-built replace function. For example, the following creates a variable called MyTime that has any spaces (ASCII 32) replaced with a zero (0).

SET MyTime=%time: =0%

So I’ve used my previous code to generate the ISO date into a variable and then replaced any spaces within that variable with a zero. I’ve also update the code to use the correct delimiters for ISO 8601 and given a few examples that you can copy and paste into your own code.

@ECHO OFF
CLS
ECHO ISO 8601 date and time formats in Batch files.
ECHO The following are generated from the system date (%date%) and time (%time%).
ECHO.


REM ISO 8601 Date and Time in extended format (YYYY-MM-DDTHH:MM:SS)
SET isodt=%date:~6,4%-%date:~3,2%-%date:~0,2%T%time:~0,2%:%time:~3,2%:%time:~6,2%
SET isodt=%isodt: =0%
ECHO ISO 8601 Date and Time in extended format (YYYY-MM-DDTHH:MM:SS): %isodt%

REM ISO 8601 Date and Time in basic format (YYYYMMDDTHHMMSS)
SET isodt=%date:~6,4%%date:~3,2%%date:~0,2%T%time:~0,2%%time:~3,2%%time:~6,2%
SET isodt=%isodt: =0%
ECHO ISO 8601 Date and Time in basic format (YYYYMMDDTHHMMSS): %isodt%


REM ISO 8601 Date in extended format (YYYY-MM-DD)
SET isodt=%date:~6,4%-%date:~3,2%-%date:~0,2%
SET isodt=%isodt: =0%
ECHO ISO 8601 Date in extended format (YYYY-MM-DD): %isodt%

REM ISO 8601 Date in basic format (YYYYMMDD)
SET isodt=%date:~6,4%%date:~3,2%%date:~0,2%
SET isodt=%isodt: =0%
ECHO ISO 8601 Date in basic format (YYYYMMDD): %isodt%


REM ISO 8601 Time in extended format (HH:MM:SS)
SET isodt=%time:~0,2%:%time:~3,2%:%time:~6,2%
SET isodt=%isodt: =0%
ECHO ISO 8601 Time in extended format (HH:MM:SS): %isodt%

REM ISO 8601 Time in basic format (HHMMSS)
SET isodt=%time:~0,2%%time:~3,2%%time:~6,2%
SET isodt=%isodt: =0%
ECHO ISO 8601 Time in basic format (HHMMSS): %isodt%


REM ISO 8601 Date and Time (not including seconds) in basic format (YYYYMMDDTHHMM)
SET isodt=%date:~6,4%%date:~3,2%%date:~0,2%T%time:~0,2%%time:~3,2%
SET isodt=%isodt: =0%
ECHO ISO 8601 Date and Time (not including seconds) in basic format (YYYYMMDDTHHMM): %isodt%
Advertisements
Categories: Batch File, Programming Tags: , ,

Save PowerPoint slides in a specific size

17th February, 2013 Leave a comment

One of my current projects at work is to investigate a screen saver to display images whether or not there is a user logged on or not (there will be a blog on the entire process shortly).

As part of this I wanted to create some sample images and text to demonstrate the system. Whilst I’m no expert in graphic design, I like to use PowerPoint 2010 as it has some really nice effects and can produce some great Web 2.0 style graphics.

One of the areas that keeps causing me issues is that all the measurement are in cm or inches, rather than pixels. This means that I have to guess how large to make the graphic, usually resulting in some tweaking afterwards.

After a bit of investigation I found a couple of useful articles:

Improve PowerPoint’s GIF, BMP, PNG, JPG export resolution
Save PowerPoint Slides as Images

Essentially, the number of pixels is calculated by multiplying the slide width or height by the screen DPI (or Dots Per Inch). This seems to vary depending on which version of PowerPoint you are using (and even by service pack), however, it is usually 72, 96 or 120.

Brilliant! Problem solved…. Well, not quite. As I’m in the UK, Microsoft Office has defaulted to the metric measurement, so I have to perform an additional calculation to convert to centimetres.

So here’s the formula:

length = (Pixels/96) * 2.54

For example, if we wanted 1024 pixels, we would calculate the length by dividing 1024 by 96 (resulting in 10.666 inches), then multiply by 2.54 to give 27.093 cm. Here’s a table with some common size that you may find useful:

Pixels Inches Cm Ratio
Width Height Width Height Width Height W/H
320 240 3.33 2.50 8.47 6.35 1.33
480 320 5.00 3.33 12.70 8.47 1.50
640 480 6.67 5.00 16.93 12.70 1.33
800 600 8.33 6.25 21.17 15.88 1.33
1024 768 10.67 8.00 27.09 20.32 1.33
1080 720 11.25 7.50 28.58 19.05 1.50
1280 720 13.33 7.50 33.87 19.05 1.78
1280 1024 13.33 10.67 33.87 27.09 1.25
Categories: PowerPoint Tags: ,

System Documentation Framework

22nd December, 2012 Leave a comment

Within IT, one of the things we know we should do, but never seem to have the time is System Documentation. One of the worst aspects is where to start!
Below is a framework that could be used for system documentation and assisting in capturing all the relevant information and ensuring that all aspects of the system are considered (e.g. Disaster Recovery, etc.). As it’s a framework, not all areas will necessarily need to be completed for each system.

Wherever possible, include the supplier’s documentation rather than writing your own version, but it is worth recording the configuration changes to make the system operate in your own environment.

System Documentation Framework

Contact Information

  • Supplier Details
    Include Supplier Name, Address, Telephone, Web Site and any other useful information.
  • Supplier Support
    The supplier’s Helpdesk telephone number and email. Also include any additional information that may be asked for (such as a serial or support number). It is also worth including support agreement information (e.g. their SLA, if they work 24 hours, etc.) and this will assist giving estimated time to fix for our internal customers.
  • Internal Engineers
    List the Primary and Secondary engineers who installed and support the system. This saves time asking who knows about the system. You may also wish to list System Support staff if it is appropriate (i.e. If they maintain a desktop client installation).
  • Internal Customer information
    This should include who “owns” the system and the internal customer base (this will allow us to know who should be contacted if there is downtime).

System Overview

  • Visio Topology
    A visual representation of the system.
  • Overview
    Include Server names, IP addresses and descriptions of the components of the system.
  • Service Accounts
    Include the usernames of account used in the system and what access rights these have.
  • Interfaces
    If the system interfaces with other system (either to send or receive information), specify how the interface works.

Installation

  • Prerequisites
    List any prerequisites that are required before the system can be installed.
  • Installation
    The supplier should supply full installation instructions, however, any changes to the default options or any configuration details (such as SQL Server names) should be recorded here.

Shutdown and Start-Up Sequences

  • Shutdown Sequence
    List any actions that need to be taken to shut the system down.
  • Start-up Sequence
    List any special actions that need to be taken when the system is started.

Business As Usual

  • System Specific Procedures
    Describe any maintenance procedures or any other system specific procedures that need to be performed.
  • How To’s/Knowledge Base
    List any procedures that may need to be performed to correct the system, or configuration issues.

Recovery Procedure

  • Backup Policy
    Include how often the system(s) are backed up and the reasons for this (i.e. a web server that does not store data, may not need to be backed up).
  • Backup Process
    Describe how the backup process works. For example: The internal backup of SQL Server is used to create a BAK file of the database at 17:00 each day and NetBackup copies this to tape at 22:00.
  • Restore Process
    Describe the restore process and any additional configuration changes that need to be made after this process.

 

Categories: Uncategorized

NDR for disabled Active Directory accounts

10th December, 2012 Leave a comment

When staff leave the organisation, are on long term sick, or take a sabbatical we disable their account in Active Directory. This also used to stop emails being delivered to their mailbox (returning a None Delivery Report [NDR] to the sender), which was very useful to let other staff know they were no longer available and stopped important emails sitting in mailboxes that were no longer checked.

Unfortunately, one of the Service Packs (for Exchange 2003, if I recall correctly) “fixed” this issue. Our workaround was to restrict disabled accounts to only accept emails from themselves. Whilst this can be done via the GUI, a script is a lot quicker.

The PowerShell script below adds restrictions to disabled accounts, the removal is removed on accounts that are re-enabled.

# Constants to modify multi-valued AD attributes.
$ADS_PROPERTY_CLEAR = 1
$ADS_PROPERTY_UPDATE = 2
$ADS_PROPERTY_APPEND = 3
$ADS_PROPERTY_DELETE = 4

# LDAP path to start search from
$RootOU = "LDAP://OU=User Accounts,DC=domain,DC=net"

Write-Host "Accounts that are disabled but accepting emails"

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]$RootOU)
$Search.PageSize = 1000
$search.filter = "(&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=2)(!authOrig=*)(|(homeMDB=*)(msExchHomeServerName=*))(!sAMAccountName=command*))"
$results = $search.FindAll()

foreach($result in $results){
   $User = $result.GetDirectoryEntry()
   $distinguishedName = $User.distinguishedName
   $distinguishedName
   $User.PutEx($ADS_PROPERTY_UPDATE,"authOrig",@($distinguishedName))
   $User.SetInfo()
}

Write-Host "Accounts that are enabled but not accepting emails"

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]$RootOU)
$Search.PageSize = 1000
$search.filter = "(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(authOrig=*)(|(homeMDB=*)(msExchHomeServerName=*)))"
$results = $search.FindAll()

foreach($result in $results){
   $User = $result.GetDirectoryEntry()
   $distinguishedName = $User.distinguishedName
   $distinguishedName
   $User.PutEx($ADS_PROPERTY_CLEAR,"authOrig",$null)
   $User.SetInfo()
}
Categories: Accounts, PowerShell

PowerShell Script to export data from Active Directory

9th December, 2012 Leave a comment

I was asked to assist a vendor who was working with our Unix/Oracle team. He was trying to import data from Active Directory into an Oracle database.

 

I’m no expert on Oracle, but it appears that the LDAP connector simply returned blobs of data, this then had to be parsed, but there seems to be no order to the data returned. The was before we starting looking at converting the objectGUID or seeing if we’d hit the 1000 record AD limit (which SQL Server suffers from).

 

I’d recently been on a PowerShell course delivered by Microsoft, so I thought I’d write a quick script to export the data. In this case, we output the result to a CSV file, but it is possible to update the database directly. Anyway, here’s the script we used:

 

# LDAP path to start search from
$RootOU = "LDAP://OU=Regional Accounts,OU=User Accounts,DC=domain,DC=net"

# CSV path
$CSVpath = "C:\TEMP\ADusers.csv"

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]$RootOU)
$Search.PageSize = 1000
$search.filter = “(&(objectCategory=person)(objectClass=user))"
$results = $search.FindAll()

$myData = @()
foreach($result in $results){
   $User = $result.GetDirectoryEntry()
   $myData += ( $User | Select-Object -Property @{Name="objectGUID";Expression={($_.objectGUID | foreach { $ofs="" } { "{0:X2}" -f $_})}},
   @{Name="sAMAccountName";Expression={$_.sAMAccountName}},
   @{Name="homeDirectory";Expression={$_.homeDirectory}},
   @{Name="mail";Expression={$_.mail}},
   @{Name="sn";Expression={$_.sn}},
   @{Name="givenName";Expression={$_.givenName}},
   @{Name="mailNickName";Expression={$_.mailNickName}},
   @{Name="title";Expression={$_.title}},
   @{Name="postOfficeBox";Expression={$_.postOfficeBox}},
   @{Name="accountexpires";Expression={[datetime]::fromfiletime($_.ConvertLargeIntegerToInt64($_.accountexpires[0])).ToString("s")}},
   @{Name="employeeNumber";Expression={$_.employeeNumber}}
)
}
$myData | Export-csv -path $CSVpath -notype

# Or, you can output to a grid
#$myData | Out-GridView
Categories: Accounts, PowerShell

Check List – Move SQL Database

8th December, 2012 Leave a comment

I’ve been tasked with moving almost 100 SQL databases from our old SQL Cluster to our brand new SQL Servers. To assist me, I’ve created a checklist, which may be useful to others…

Check List – Move SQL Database

This check list is to assist in moving a database from one SQL server to another.

  • Identify application connection logon details
  • Create logins
  • Create destination folders for MDF and LDF files
  • Take frontend application off line
  • Backup Database
  • Take Database Offline
  • Copy backup file to new SQL server
  • Restore Database
  • Change Database SQL Compatibility Level
  • Re-create connection logon details
  • Update application connection logon details
  • Delete the original Database

Identify application connection details

Identify how the application connects to SQL. If a SQL user account is used, obtain the password (this may be in a connection string in the application). If it’s not available, discuss with Application support about creating a new password. Check if other accounts have access to the database.

Create logins

Create logins for Windows Users/Groups or SQL logins. Check language settings.

Create destination folders for MDF and LDF files

Create the folders when the database files will be stored.

Take frontend application offline

If possible, take the frontend application offline. This will stop new data being written to the database.

Backup Database

Name the backup file similar to “<DatabaseName>_PreMove.BAK” so it can be identified from other database that may also be being move.

Take Database Offline

Right click the database, highlight Tasks, and then click Take Offline.

Copy backup file to new SQL server

Copy the BAK files to the new SQL server.

Restore Database

Create subfolders for the database with the Database and Log folders. Ensure that the file paths restore to these new locations.

Change Database SQL Compatibility Level

Right click the database. Select Options from the Select a page menu and change the Compatibility Level to the highest number (unless the vendor dictates otherwise).

Re-create connection logon details

Create a new login and ensure the default database is selected, but do not perform any user mappings. Select the database and open a New Query window.  Enter the following command:

sp_change_users_login 'auto_fix', 'NewLoginUserName'

This is not required for Active Directory users or groups, as the SID remains the same, so it automatically associated with the database.

Update application connection string

Ensure that the Server Team or Application Support team have updated the frontend connection to the SQL server and tested the functionality.

Delete the original Database

Delete the Database (which should be offline) or the original SQL Server.

Categories: Uncategorized

Batch File ISO 8601 Date Format

18th November, 2012 1 comment

UPDATED: See Batch File ISO 8601 Date Format – Version 2.0

The following batch file creates a variable to hold the date and time in ISO 8601 date format (http://en.wikipedia.org/wiki/ISO_8601). It’s dependant on the way the date and time is presented based on the locale of the system, so it may need to be modified for machines outside of the UK.

Additionally, I’ve noticed that if the time is before 10:00 am, then the hour does not have a leading zero – this could have an impact if you wanted to use this to name a file, for example.

@echo off
cls
echo Get the current date and time in YYYY-MM-DD-HH-MM-SS format
SET isodt=%date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
REM
echo %isodt%
echo %date%
echo %time%
Categories: Batch File, Programming