Archive for the ‘IT related’ Category

Determine SQL Version and Edition using Powershell and WMI

Monday, December 8th, 2014

This small Powershell script prompts for a computername and tries to determine the installed version and edition of SQL using WMI.

$comp = Read-Host "Enter Computername:"
Get-WmiObject -ComputerName $comp -Namespace root\Microsoft\SqlServer\ComputerManagement -Class SqlServiceAdvancedProperty | Where-Object {($_.PropertyName -eq 'VERSION') -or ($_.PropertyName -eq 'SKUNAME')} | Format-Table ServiceName, PropertyName, PropertyStrValue -AutoSize
Get-WmiObject -ComputerName $comp -Namespace root\Microsoft\SqlServer\ComputerManagement10 -Class SqlServiceAdvancedProperty | Where-Object {($_.PropertyName -eq 'VERSION') -or ($_.PropertyName -eq 'SKUNAME')} | Format-Table ServiceName, PropertyName, PropertyStrValue -AutoSize
Get-WmiObject -ComputerName $comp -Namespace root\Microsoft\SqlServer\ComputerManagement12 -Class SqlServiceAdvancedProperty | Where-Object {($_.PropertyName -eq 'VERSION') -or ($_.PropertyName -eq 'SKUNAME')} | Format-Table ServiceName, PropertyName, PropertyStrValue -AutoSize
Get-WmiObject -ComputerName $comp -Namespace root\Microsoft\SqlServer\ComputerManagement14 -Class SqlServiceAdvancedProperty | Where-Object {($_.PropertyName -eq 'VERSION') -or ($_.PropertyName -eq 'SKUNAME')} | Format-Table ServiceName, PropertyName, PropertyStrValue -AutoSize

Batch script to detect CD/DVD-rom drive

Friday, May 2nd, 2014

Batch script to detect CD/DVD-rom drive


REM Check if we have a CDROM drive
Setlocal EnableDelayedExpansion

echo list vol > diskpart.txt
diskpart /s diskpart.txt > dp_volumes.txt
set hascdrom=0
FOR /F "delims=," %%v in (dp_volumes.txt) do (
rem echo %%v
set str1=%%v
rem echo Line is !str1!
rem echo "x!str1:ROM=!"
rem echo "x!str1!"
if not "x!str1:ROM=!"=="x!str1!" set hascdrom=1
rem echo !hascdrom!
)
if "!hascdrom!"=="0" GOTO nocdrom

:cdrom
echo We have a CD/DVD-rom
goto :EOF

:nocdrom
echo No CD/DVD-rom drive available
goto: EOF

Domain Join using Powershell

Tuesday, November 13th, 2012

Example of how to join a computer (Windows 8) to a domain using PowerShell.


$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList @('ALTIRIS\srv_altiris', (ConvertTo-SecureString -AsPlainText -Force 'P@ssw0rd' ))
Add-Computer -DomainName altiris.local -OUPath 'OU=CORPORATE_COMPUTERS,OU=BELGIUM,DC=altiris,DC=local' -PassThru -Verbose -Credential $credential

CentOS – Launching VMWare Workstation VMs at boot

Sunday, January 23rd, 2011

I was searching for a solution to launch and suspend VMWare Workstation VMs on a CentOS 5-machine during boot/shutdown.
A script script for launching VirtualBox VMS during boot inspired me to create one for VMWare Workstation.
In this example I have teamed my VMs in a team /vms/my_team.vmtm, it could as well be a single VM (.vmx) that is being launched.
The VM Team will be launched with a different user account as VMWare not allows root to launch VMs.
During shutdown root however can suspend the VM Team, and this is also the action being used during system shutdown.

  1. Create a user vmuser
    useradd vmuser
    passwd vmuser
  2. Create the VMHeadless script. I have teamed all my VMS and will launch/suspend the team /vms/my_team.vmtm
    vi /etc/init.d/vmheadless

    #!/bin/bash
    ### BEGIN INIT INFO
    # Provides:          vmheadless.sh
    # Required-Start:    $remote_fs $syslog
    # Required-Stop:     $remote_fs $syslog
    # Default-Start:     2 3 4 5
    # Default-Stop:      0 1 6
    # chkconfig: 2345 99 20
    # Short-Description: Start VM
    # Description:       Start/stop the virtual machines
    ### END INIT INFO
     
    MACHINE_NAME="/vms/my_team.vmtm"
    VM_USER="vmuser"
    RETVAL=0
    LD_LIBRARY_PATH=/usr/lib/vmware:$LD_LIBRARY_PATH
    PATH=/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/bin
     
    id=`id -u`
    if  [ "$id" != "0" ] ; then
       echo "This script must be run as root" 1>&2
       exit 1
    fi
     
    # Load the VERBOSE setting and other rcS variables
     [ -f /etc/default/rcS ]  && . /etc/default/rcS
     
    # Load the VERBOSE setting and other rcS variables
    #. /lib/init/vars.sh
     
    # Define LSB log_* functions.
    # Depend on lsb-base (>= 3.0-6) to ensure that this file is present.
    . /lib/lsb/init-functions
     
    # Get function from functions library
    . /etc/init.d/functions
     
    # Load network defaults. Use this if there is any extra network stuff to do first.
     
    case "$1" in
    start)
       su $VM_USER -c "/usr/bin/vmrun start $MACHINE_NAME nogui"
            RETVAL=0
      echo "Started VM Headless RETVAL=($RETVAL)"
            ;;
    stop)
      /usr/bin/vmrun suspend "$MACHINE_NAME"
            RETVAL=$?
     echo "Suspended VM Headless RETVAL=($RETVAL)"
            ;;
    status)
      ps=`ps -ef | grep vmware-vmx | grep -v grep | wc -l`
      if  [ "$ps" -gt 0 ]  ; then
        echo "$ps VMS started"
        RETVAL=1
      else
        echo "VMS not running"
        RETVAL=0
      fi
            ;;
    restart)
      /usr/bin/vmrun suspend "$MACHINE_NAME" nogui
            RETVAL=$?
     echo "Suspended VM Headless RETVAL=($RETVAL)"
      su backup -c "/usr/bin/vmrun -T ws start $MACHINE_NAME nogui"
            RETVAL=$?
      echo "Started VM Headless RETVAL=($RETVAL)"
            ;;
    *)
            echo "Usage: $0 { start | stop }"
            RETVAL=1
            ;;
    esac
    exit $RETVAL
    
  3. Enable VMHeadless during boot
    chkconfig –add vmheadless
    chkonfig –level 2345 vmheadless on
  4. Launch the VMHeadless daemon, and thus launching the VM Team
    service vmheadless start

PS – Disable Unidentified network for VM nics

Wednesday, October 6th, 2010

This script adds the *NdisDeviceType registry value with value 1 to the keys of the VMWare network cards.
Afterwards it disables and enables the networkcards to let the changes take effect.

$nics = gci "HKLM:\SYSTEM\\CurrentControlSet\\Control\\Class\\{4D36E972-E325-11CE-BFC1-08002BE10318}"
$nics | foreach-object -process {
    $devInstanceId = $_.getvalue("DeviceInstanceID")
    if($devInstanceId -match "VMWARE"){
        Write-Host $_.Name
        $regpath = ( $_.name ) -replace("HKEY_LOCAL_MACHINE","HKLM:")
        new-itemproperty -path $regpath -name "*NdisDeviceType" -value 1 -PropertyType "DWORD"
    }
}
 
 $wminics = get-wmiobject win32_networkadapter | where-object {$_.name -like "*vmware*" }
 $wminics | foreach-object -process { 
        
     write-host -nonew "Disabling $($_.name) ... "
     $result = $_.Disable() 
     if ($result.ReturnValue -eq -0) { write-host " success." } else { write-host " failed." } 
        
     write-host -nonew "Enabling $($_.name) ... "
     $result = $_.Enable() 
     if ($result.ReturnValue -eq -0) { write-host " success." } else { write-host " failed." } 
 }

Outlook VBA – remove reminders

Friday, August 27th, 2010

I synchronize my Windows Mobile Device with my corporate-exchange account. This way my calendar stays nicely up to date and gives me reminders for the tasks I have to do.

However I find it rather a hassle that I receive reminders for certain activities like holidays. Therefore I have written a small macro that disables the reminders for all future calendar entries with a certain subject.

Private Sub RemoveReminders(sProject As String)
Dim fldMailbox As MAPIFolder
Dim fldCalendar As MAPIFolder
 
Dim objItem As AppointmentItem
Dim iCntr As Integer
iCntr = 0
 
Set fldMailbox = Session.Folders(sMailboxName)
Set fldCalendar = fldMailbox.Folders("Calendar")
Set mcolCalItems = fldCalendar.Items
 
For Each objItem In mcolCalItems
If objItem.start > Now() Then
If objItem.ReminderSet = True Then
    If InStr(objItem.Body, sProject) > 0 Then
    iCntr = iCntr + 1
With objItem
.ReminderSet = False
.Save
End With
 
objItem.Save
End If
End If
End If
Next
 
MsgBox "Modified " & iCntr & " calendar entries"
End Sub
 
Public Sub RemoveHolidayReminders()
RemoveReminders ("Holiday")
End Sub

ESXi 3.5 and Server 2003 dualboot

Sunday, January 31st, 2010

After some testing I managed to create a dualboot setup with ESXi 3.5 and Windows Server 2003.

  1. Install ESXi to harddisk 1
  2. Install Windows Server 2003 to harddisk 2. Delete the existing partition on harddisk 2 if ESXi has created one.
    On the first reboot during the installation you should get an error “NTLDR is missing”
  3. Boot with a live linux cd-rom, eg KNOPPIX
  4. (Re-)install syslinux bootloader to boot partition (Hypervisor0)
    sudo syslinux /dev/hda1
    file is read only. overwrite anyway (y/n)? y
    sudo mkdir /mnt/Hypervisor0
    sudo mount /dev/hda1 /mnt/Hypervisor0
    vi /mnt/Hypervisor0/syslinux.cfg
    
    

    original:

    default safeboot.c32
    
    

    modified:

    default menu.c32
    menu title Dual boot
    timeout 100
    label esx
    menu label ESXi 3.5
    COM32 safeboot.c32
    LABEL win
    MENU LABEL MS Windows Server 2003
    COM32 chain.c32
    APPEND boot ntldr=/NTLDR
    
    
  5. Copy syslinux modules menu.c32 and chain.c32 to boot partition (Hypervisor0)
    Use locate to find the folder on the live cd that contains the modules, eg locate menu.c32

    cp /usr/lib/syslinux/menu.c32 /mnt/Hypervisor0
    cp /usr/lib/syslinux/chain.c32 /mnt/Hypervisor0
    
    
  6. Reboot

Script software deployment in PowerShell

Sunday, January 10th, 2010

This is an example in PowerShell of how you can schedule the installation a software package by Altiris DS.
Instead of using the API, the command-line tool axsched is being called to schedule the software package.

function DeploySoftware
{
    param( [String] $pcname,  [String] $package,  [String] $nexttime,  [String] $folder)
    #Write-Host "Deploy $pcname '$package' /t $nexttime /f '$folder'"
    #\\ALTIRIS\express\axsched.exe "$pcname" "$package" / t "$nexttime" / f "$folder"
         
    $StartInfo = New-Object System.Diagnostics.ProcessStartInfo
    $StartInfo.CreateNoWindow = $true
    $StartInfo.UseShellExecute = $false
    $StartInfo.FileName = "\\ALTIRIS\express\axsched.exe"
    $StartInfo.WorkingDirectory = "C:\Temp"
    $StartInfo.Arguments = "`"$pcname`" `"$package`" /t `"$nexttime`" /f `"$folder`""
    $p =  [System.Diagnostics.Process] ::Start($StartInfo)
    $p.WaitForExit()
    if ($p.ExitCode -eq 0) { $TRUE } else { $FALSE }
}
#This function adds a number of minutes to the given date time and outputs the addition in the right format for AxSched.
function Get-ScheduleTime
{
    param( [System.DateTime]  $starttime,  [int] $minutes)
    #$minutes = 12
    $tspan = New-Object System.TimeSpan(0, 0, $minutes, 0)
    $addtime =  [System.DateTime] ::op_Addition($starttime, $tspan)
    #"2008-10-20 15:00"
    $y = $addtime.Year
    $mo = $addtime.Month
    $d = $addtime.Day
    $h = $addtime.Hour
    $mi = $addtime.Minute
    $schedtime = "$y"
    $schedtime += "-"
    $schedtime += TwoDecimal("$mo")
    $schedtime += "-"
    $schedtime += TwoDecimal("$d")
    $schedtime += " "
    $schedtime += TwoDecimal("$h")
    $schedtime += ":"
    $schedtime += TwoDecimal("$mi")
    $schedtime
}
#Simple function to convert a single decimal to a two decimal, eg 7 becomes 07
function TwoDecimal
{
    param( [String] $Number)
    while ($number.Length -lt 2) { $number = "0$number" }
    $number
}
$now =  [System.DateTime] ::Now
$pcname = "COMP007"
$jobname = "Install Outlook 2007"
$jobfolder = "Software"
#The installation of the package needs to be started after 5 minutes.
$nexttime = Get-ScheduleTime $now 5
DeploySoftware $pcname $jobname $nexttime $jobfolder

Batch-scripting

Friday, March 21st, 2008

Batch-scripting is not as powerful as WSH-scripting or PowerShell.
But with some tools you can perform some simple but useful actions.
One tool I often use is robocopy.

  1. Asking for values in a batch-script
    @echo off
    @set /P pctocheck=Enter name of pc:
    @cscript.exe "script.vbs" %pctocheck%
    pause
  2. Copy non-existing folders back to C-drive
    @echo off
    rem Check if the script was called with parameter RUN
    IF "%1"=="RUN" GOTO run
    rem Since we are not running the script with parameter RUN, start it using cmd whilst enabling delayed environment variable expansion (/V:ON)
    cmd /V:ON /C H:\copy_c-drive.cmd RUN
    GOTO :EOF
    :run
    echo ...Copying extra folders on C-drive
    rem List all existing files and directories on the C-drive, including hidden ones.
    dir /a:d /b C:\ >C:\WINDOWS\TEMP\rc_existsystemdirs.txt
    dir /a:hd /b C:\ >>C:\WINDOWS\TEMP\rc_existsystemdirs.txt
    dir /a:-d /b C:\ >C:\WINDOWS\TEMP\rc_existsystemfiles.txt
    dir /a:h-d /b C:\ >>C:\WINDOWS\TEMP\rc_existsystemfiles.txt
    rem Build up variables
    set existingdirs=
    FOR /F "delims=," %%i IN (C:\WINDOWS\TEMP\rc_existsystemdirs.txt) do set existingdirs=!existingdirs! /XD "%%i"
    set existingfiles=
    FOR /F "delims=," %%i IN (C:\WINDOWS\TEMP\rc_existsystemfiles.txt) do set existingfiles=!existingfiles! /XF "%%i"
    rem Do the copy thing
    robocopy /COPYALL /E X:\ C:\ /TEE /LOG+:C:\WINDOWS\TEMP\rc_system.log /XO /R:1 /W:3 %existingdirs% %existingfiles%

WSH Scripting

Friday, March 21st, 2008

Using WSH with WMI enables you to query lots of information of a system.
Here are some examples of code that I regularly use.

  1. Determine UpTime of a computer
    strComputer = InputBox("Enter computername","Determine Uptime",".")
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    Set colOperatingSystems = objWMIService.ExecQuery _
    ("Select * From Win32_PerfFormattedData_PerfOS_System")
    intSystemUptime = 0
    For Each objOS in colOperatingSystems
    Dim intSystemUptimeSec
    Dim intSystemUptimeMin
    Dim intSystemUptimeHour
    Dim intSystemUptimeDay
    intSystemUptimeDay = Int(objOS.SystemUpTime / 86400)
    intSystemUptimeHour = Int(objOS.SystemUpTime / 3600) - (intSystemUptimeDay*24)
    intSystemUptimeMin = Int(objOS.SystemUpTime / 60) - (intSystemUptimeHour*60) - (intSystemUptimeDay*24*60)
    intSystemUptimeSec = Int(objOS.SystemUpTime) - (intSystemUptimeMin*60) - (intSystemUptimeHour*60*60) - (intSystemUptimeDay*24*60*60)
    intSystemUptime = Int(objOS.SystemUpTime / 60)
    MsgBox("Uptime for " & strcomputer &  " = " & intSystemUptimeDay & "d " & intSystemUptimeHour & "h " & intSystemUptimeMin & "m " & intSystemUptimeSec & "s")
    Next
  2. Ping a computer
    strComputer = InputBox("Enter computername","Ping a computer",".")
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}")._
    ExecQuery("select * from Win32_PingStatus where address = '"_
    & strComputer & "'")
    For Each objStatus in objPing
    If IsNull(objStatus.StatusCode) or objStatus.StatusCode<>0 Then
    MsgBox("Ping failed")
    else
    MsgBox("Ping succeeded")
    End If
    Next
  3. Map a Networkdrive
    Set oNet = CreateObject("wscript.network")
    oNet.MapNetworkDrive "X:", "\\" & strComputer & "\c$"
  4. Get Computers from an OU in Active Directory
    Set ObjOU = GetObject("LDAP://OU=Desktops,OU=Computers,DC=corp,DC=contoso,DC=com")
    ObjOU.Filter = Array("Computer")
    For Each ObjComp in ObjOU
  5. List running processes
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" _
    & strComputer & "\root\cimv2")
    Set colProcess = objWMIService.ExecQuery _
    ("Select * from Win32_Process")
    For Each objProcess in colProcess
    WScript.Echo objProcess
    Next
  6. Count DiskErrors
    Set colLoggedEvents = objWMIService.ExecQuery _
    ("Select * from Win32_NTLogEvent Where Logfile = 'System'" _
    & " and SourceName = 'disk'")
    iDiskErrors = colLoggedEvents.count
  7. Show logged on user
    Set Users = objWMIService.InstancesOf("Win32_ComputerSystem")
    for each User in Users
    If isNull(User.UserName) then
    WScript.Echo "No User is logged on"
    else
    WScript.Echo User.UserName
    end if
    Next
  8. Enumerate all profiles stored in Documents and Settings
    Set objFolder = objFSO.GetFolder("\\" & strComputer & "\c$\Documents And Settings")
    valStoredprofiles = ""
    For Each Subfolder in objFolder.SubFolders
    If IsStandardUserProfile(SubFolder.Path) then
    arrPath = split(Subfolder.Path,"\")
    'sSize = Round(SubFolder.Size/1024/1024,2)
    'valStoredprofiles = valStoredprofiles & arrPath(UBound(arrPath,1)) & "  [" & sSize & "MB] " & " - "
    valStoredprofiles = valStoredprofiles & arrPath(UBound(arrPath,1)) & " - "
    end if
    Next
    Function IsStandardUserProfile(sFolder)
    Dim iMatches
    iMatches = 0
    If Instr(sFolder,"Administrator") > 0 then iMatches = iMatches + 1
    If Instr(sFolder,"All Users") > 0 then iMatches = iMatches + 1
    If Instr(sFolder,"Default User") > 0 then iMatches = iMatches + 1
    If Instr(sFolder,"LocalService") > 0 then iMatches = iMatches + 1
    If Instr(sFolder,"NetworkService") > 0 then iMatches = iMatches + 1
    If iMatches = 0 then
    IsStandardUserProfile= true
    Else
    IsStandardUserProfile= false
    end if
    End Function
  9. Read and write an XML-file
    Set objXML = CreateObject("Microsoft.XMLDOM")
    objXML.load "result.xml"
    'WScript.Echo objXML.parseError.errorCode
    If (objXML.parseError.errorCode <> 0) Then
    Dim myErr
    Set myErr = objXML.parseError
    MsgBox("You have error " & myErr.reason)
    Else
    'WScript.Echo objXML.xml
    'WScript.Echo objXML.documentElement.attributes.item(0).nodeValue
    Dim i
    i = 1
    ReDim PreServe arrPcs(i+1)
    Set pcs = objXML.getElementsByTagName("pc")
    'WScript.Echo pcs
    for each pc in pcs
    arrPcs(i-1) = pc.getAttribute("id") & ";" & pc.getAttribute("location")
    i=i+1
    ReDim PreServe arrPcs(i)
    next
    End If
    for each pc in pcs
    pcID = pc.getAttribute("id")
    if pcID = current PC then
    '<ip>192.168.1.19</ip>
    '<uptime>0</uptime>
    '<diskerrors>0</diskerrors>
    '<user>NONE</user>
    '<storedprofiles>0</storedprofiles>
    'WScript.Echo pc.childNodes.length
    pc.childNodes.Item(0).firstChild.nodeValue = valIP
    pc.childNodes.Item(2).firstChild.nodeValue = valUptime
    pc.childNodes.Item(3).firstChild.nodeValue = valDiskerrors
    'WScript.Echo valUser
    pc.childNodes.Item(4).firstChild.nodeValue = valUser
    pc.childNodes.Item(5).firstChild.nodeValue = valStoredprofiles
    exit for
    end if
    next
    objXML.documentElement.Attributes.Item(0).nodeValue = now
    objXML.save("result.xml")