Archiving windows event logs.

If you select the option in windows event logs to archive old event logs it will create files of the format that event viewer can open.

An event ID 1105 will be created as the first item in the new log if it is the security log, other logs such as forwarded events will cause a 105 event id in the system log.

event_ID_1105

By selecting to trigger a task when this event is generated we can automatically run a script that moves the archive files to a backup location. The log files compress well and putting them into an NTFS compressed folder should save a lot of space.

A simple cmd script that will move the archive files out.

@set moveto=F:\ServerBackups\SERVERNAME\EventLogs
@date /t 
@time /t 
@%homedir%
@cd "%systemroot%\system32\winevt\logs"
@echo event log has been archived due to size limit - now move to archive storage. 
cd
move archive*.evtx %moveto%

Another option is to use this powershell script, this will move all the archived event log files from the default location to another. It will then “cleanup” the archive location and remove all files more than 30 days old. However it will only remove the files that have been backed up (archive bit cleared).

The location and number of days can be specified on the command line (options to the task), the first option is the name of the directory to move the files (the subfolders, computername and eventlogs need to be created manually first) and the second the number of days old a file must be before it is removed.

The default location is d:\serverbackups and the default number of days is 30. Remember that files will only be removed if they have been backed up or had their archive bit cleared.

param( [string]$archivefolder='D:\ServerBackups', [int32]$limit=30 )
#script to archive event logs from log collecting server. 
$computername=$env:COMPUTERNAME
$sysroot=$env:SystemRoot
$attribute = [io.fileattributes]::archive
$limitdate=(Get-Date).AddDays(-$limit)
$archivefolder="$archivefolder\$computername\EventLogs"
$logfile="$archivefolder\ArchiveEventLogs.Log"
$eventlogfolder="$sysroot\system32\winevt\logs"
$myname=$MyInvocation.InvocationName
# test for existence of target folders
if (( test-path $archivefolder ) -and (test-path $eventlogfolder ) ) 
    { 

    $now=get-date
    Add-Content -Path $logfile "Script $myname started $now"

    #$myeventlogs= Get-ChildItem -path "$eventlogfolder\Arch*.evtx"

    $resultofmove=move-item -passthru -path "$eventlogfolder\Arch*.evtx" -destination $archivefolder

    if ( $resultofmove ) 
        {
        $resultofmove_count=$resultofmove.count
        Add-Content -path $logfile "$resultofmove_count files have been moved to Archive" 
        $resultofmove |  Add-Content -path $logfile 
        }
    else
        {
        Add-Content -path $logfile "No files found to archive." 
        }

    # Delete files older than the $limit.
    $myfiles= Get-ChildItem -Path $archivefolder | Where-Object { !$_.PSIsContainer -and $_.LastWriteTime -lt $limitdate  -and !( (get-itemproperty -path  $_.fullname ).attributes -band $attribute ) } 

    $myfiles_count=$myfiles.count
    if ( $myfiles ) { 
        $resultofdel = $myfiles | Remove-Item -Force
        Add-Content -path $logfile "Delete $myfiles_count log files that have been backed up and are older than $limitdate" 
        $myfiles  |  Add-Content -path $logfile 
        }
    else
        {
        Add-Content -path $logfile "No event log archives found to delete. Needed to be backed up and older than $limitdate" 
        }

    $now=get-date
    Add-Content -Path $logfile "Script $myname finished $now"

    #type $logfile 

    }
else
    {
    write-host -ForegroundColor red "ERROR  $archivefolder or $eventlogfolder do not exist, debugging written to  $env:TEMP\archivecrashlog.txt"
    $myline= $MyInvocation.line
    add-content -path "$env:TEMP\archivecrashlog.txt" "$myline"
    }
   
Posted in Hints and Tips, System Administration | Comments Off on Archiving windows event logs.

Backing up SQL Express Databases

A scheduled script such as this which has hard coded the name of the sql command file, set it to run in the folder containing the backups

echo %0 starting 
date /t 
time /t 
dir 
sqlcmd -S localhost\caserver -i "%programfiles%\custom\sql_express_database_name_backup.sql"

Which calls a SQL commands file containing this which contains the hard coded name of the database and location of backup files.

BACKUP DATABASE [My_Database] TO  DISK = N'E:\Backup\My_Database' WITH NOFORMAT, INIT,  NAME = N'My_Database-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Microsoft have a much more powerful script referenced here

Technet How to schedule and automate backups of SQL Server databases in SQL Server Express

Posted in Hints and Tips, System Administration | Comments Off on Backing up SQL Express Databases

Giving permissions in Exchange for users from a trusted domain.

After a recent consolidation we needed to give users in a seperate (but trusted) domain access to mailboxes on an exchange 2010 server.

The below code (copied from https://social.technet.microsoft.com/Forums/exchange/en-US/d2f051f0-9649-4ac0-8e82-9783a34d0eed/how-do-you-grant-full-mailbox-permissions-to-someone-in-a-different-domain-in-exchange-2010?forum=exchange2010 – by J. Duke Rogers Communicore Technologies & Triangle Forensics) does the job. I added the bit to get the Distinguished name from the mailbox (saves a lot of typing).

$mb= get-mailbox “mailbox_to_be_given_rights_on”

$dn = $mb.DistinguishedName

Add-ADPermission -Identity $dn -User ‘mydomain\mygroup’ -ExtendedRights ‘Send-as’

Add-MailboxPermission “mailbox_to_be_given_rights_on” -User “mydomain\mygroup” -AccessRights FullAccess

Posted in Powershell, VBS, VBA and other scripting., System Administration | Comments Off on Giving permissions in Exchange for users from a trusted domain.

get the total number of items in all folders of outlook mailbox store

If you are ever moving large amounts of email around it can be useful to be able to check that there is the correct number of emails in the old and new locations. This code generates a text file that can be used to compare the count of items in each folder. Just walks its way through the mailstore. includes public folders or PST files. Text file is in CSV format so it can be easily read and summarised in excel.


Sub CountAllFolders()

    Dim myfolder, mymyfolder, mydestfolder, mydestfolder1 As Folder
    Dim myisempty As Boolean
    mymsg = ""
    errorlist = ""
    
    
    
    
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    
    For x = 1 To myNameSpace.Folders.Count
        Set myfolder = myNameSpace.Folders(x)
        mymsg = mymsg + countthisfolder(myfolder)
    Next x
    
    'MsgBox mymsg
    
    
    'should be putting this somewhere cleverer
    FilePath = "c:\windows\temp\outlookfolderitemscount.csv"
    
    
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     Set objFile = objFSO.OpenTextFile(FilePath, 2, True)
     objFile.Write (mymsg)
     Set objFile = Nothing
     Set objFSO = Nothing
     
     
    'Stop
    'myfolder.Display
    

End Sub

Function countthisfolder(ByVal myfolders As Folder)
    mymsg = ""
   

    mymsg = """" + myfolders.FolderPath + """," + CStr(myfolders.Items.Count)  + vbCrLf
    For i = 1 To myfolders.Folders.Count
        mymsg = mymsg + countthisfolder(myfolders.Folders(i)) + vbCrLf
        Debug.Print myfolders.Folders(i).FolderPath
        
    Next i
    countthisfolder = mymsg
    
        
End Function

Posted in Hints and Tips, Powershell, VBS, VBA and other scripting. | Tagged | Comments Off on get the total number of items in all folders of outlook mailbox store

Self Signed Certificates.

Allows you to create longer lasting self signed certificates for testing

new-selfsignedcertificate in powershell is pretty good but doesnt allow more than 12 months expiration.

selfssl7 allows up to 50 years and 2048 bit size. Its default behavior is to create and install the certificate – be careful.

http://blogs.iis.net/thomad/setting-up-ssl-made-easy

Posted in Powershell, VBS, VBA and other scripting., System Administration | Comments Off on Self Signed Certificates.

export all mailboxes exchange 2010

To do a bulk mailboxexport on exchange 2010 for all mailboxes, it mus tbe to a share and the share needs appropriate permissions.

Filter the get-mailbox to get a subset of the mailboxes.

$a=get-mailbox
foreach ($u in $a) {
  $u.alias
  $ua=$u.alias
  new-mailboxexportrequest $ua -filepath \\mailserver\exportedemail\$ua.pst
  }
Posted in Powershell, VBS, VBA and other scripting., System Administration | Comments Off on export all mailboxes exchange 2010

excel get domain from email address

=RIGHT(C2,LEN(C2)-SEARCH("@",C2))
Posted in System Administration | Comments Off on excel get domain from email address

GetMailboxStatisticsAndEmail.PS1

Script to export a list of all the mailboxes in an organisation and send the results as attachments to an email.

Gives a list ordered by size and after that ordered by last logon time (to hilight old inactive mailboxes).

Also creates and attaches a CSV listing the mailboxes and the size of each one.

###Send mailbox statistics script
###
### to schedule this use PowerShell.exe -PSConsoleFile "C:\Program Files\Microsoft\Exchange Server\Bin\ExShell.psc1" -Command "./scriptname.ps1"
###
### Change the mail message values in this section


# this needs to be changed depending on version of exchange
Add-PSSnapin "Microsoft.Exchange.Management.PowerShell.Admin"


$FromAddress = "nanotera@nanotera.com.au"
$ToAddress = "nanotera@nanotera.com.au"
$MessageSubject = "Mailbox Size Report from $env:computername "
$MessageBody = "Attached is the current list of mailbox sizes."

### put your SMTP server here 

$SendingServer = "192.168.1.1"

$tempfilename= "$env:temp\listmailboxes1.txt"
$tempfilenamemk1= "$env:temp\listmailboxes1_"

Get-MailboxDatabase | sort-object "server","storagegroup" | Select Server, StorageGroupName, Name, @{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name="Size (MB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity ) ; $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1024KB; [math]::round($size, 2)}}, @{Name="No. Of Mbx";expression={(Get-Mailbox -Database $_.Identity -resultsize unlimited | Measure-Object).Count}} | Format-table -AutoSize | out-file -width 255 $tempfilename


foreach ($mbdatabase in Get-MailboxDatabase  | sort-object "server","storagegroup"  ) { 
echo "Mailboxes in $mbdatabase.identity sorted by size" | out-file -append -width 255 $tempfilename
###Now get the stats and store in a text file
Get-MailboxStatistics -database $mbdatabase.identity  | Sort-Object    @{Expression="Database";Descending=$false}  ,@{Expression="TotalItemSize";Descending=$true} | ft database, DisplayName, lastlogontime ,@{label="TotalItemSize(MB)";expression={$_.TotalItemSize.Value.tomb()}}, ItemCount | out-file -append -width 255 $tempfilename

echo "Mailboxes in $mbdatabase.identity sorted by last logon (oldest first)" | out-file -append -width 255 $tempfilename
Get-MailboxStatistics -database $mbdatabase.identity  | Sort-Object @{Expression="Database";Descending=$false}  ,@{Expression="Lastlogontime";Descending=$false}  | ft database, DisplayName, lastlogontime ,@{label="TotalItemSize(MB)";expression={$_.TotalItemSize.Value.tomb()}}, ItemCount | out-file -append -width 255 $tempfilename

}


###Create the mail message and add the statistics text file as an attachment
$SMTPMessage = New-Object System.Net.Mail.MailMessage $FromAddress, $ToAddress, $MessageSubject, $MessageBody 

$Attachment = New-Object Net.Mail.Attachment($tempfilename)
$SMTPMessage.Attachments.Add($Attachment)


# get them all in one CSV file per server

foreach ( $mbserver in get-mailboxserver ) {

	$mbs = Get-MailboxStatistics -server $mbserver
	$mbs1 = $mbs   | Sort-Object    @{Expression="Database";Descending=$false}  ,@{Expression="TotalItemSize";Descending=$true} 
	$tmpfile1=$tempfilenamemk1 + $mbserver + ".csv"
	$mbs1 | select database, DisplayName, lastlogontime ,@{label="TotalItemSize(MB)";expression={$_.TotalItemSize.Value.tomb()}}, ItemCount | export-csv -notypeinformation  -Path $tmpfile1
	$Attachment = New-Object Net.Mail.Attachment($tmpfile1 )
	$SMTPMessage.Attachments.Add($Attachment)

}


###Send the message
$SMTPClient = New-Object System.Net.Mail.SMTPClient $SendingServer
$SMTPClient.Send($SMTPMessage)

remove-variable SMTPclient
remove-variable SMTPmessage
Remove-Variable attachment

#should remove temp files
#remove-item "$tempfilename"


Posted in Powershell, VBS, VBA and other scripting., System Administration | Comments Off on GetMailboxStatisticsAndEmail.PS1

get_programs_installed_local.ps1

export a list of all software installed on this windows computer. writes to a csv file in the “current” folder.



#
# 
#

$COTSInstalledSoftware = Get-ChildItem HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\ | Get-ItemProperty

$COTSInstalledSoftware += Get-ChildItem HKCU:\Software\Microsoft\Windows\CurrentVersion\Uninstall\ | Get-ItemProperty

IF (Test-path HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\) {

   $COTSInstalledSoftware += Get-ChildItem HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\ | Get-ItemProperty

}

$CotsInstalledSoftware | where { $_.displayname -ne $null } |  select-object displayname | sort-object displayname | get-unique -asstring | export-csv .\allsoftware.csv


}

Posted in Powershell, VBS, VBA and other scripting. | Comments Off on get_programs_installed_local.ps1

Replace the icon lables “Computer” and “This PC” with something more useful.

This script will read the computers name and domain settings and rename the icon in the form computername.domain.local

This makes it much easier to be certain which desktop you are working on when using multiple remote RDP , mstsc connections.

I cant remember now where I found the “special” CLSID code for the registry entry, without that this wouldn’t be easy to do. It may have been on some of the technet discussions about this.

The registry functions were “borrowed” from http://ss64.com/vb/regwrite.html ,

It works on windows 7 and server 2008, server 2012 r2, windows 8.1

Windows 7 default name is “Computer”
Server 2008 default name is “Computer”
Windows 8.1 default name is “This PC”
Server 2012 default name is “This PC”

I found it annoying that a brand new server install was calling itself a PC 😉

I havent rolled it out in a domain anywhere (yet) but I like the idea of some standardised “useful” text there rather than the generic “computer” or “This PC”

'----------------------------------------------------------------------
' script to replace "Computer" name in windows explorer with the actual computer name.
'----------------------------------------------------------------------
 'dont use the computername from the environment as we want to query domain membership anyway.
 strOldName= readreg("HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\CLSID\{20D04FE0-3AEA-1069-A2D8-08002B30309D}\")
 ' get network name if one is available "
 Dim WshNetwork
 Set WshNetwork = CreateObject("WScript.Network")
 dim sysinfo
 Set sysInfo = CreateObject("ADSystemInfo")
 strNewName = lcase( WshNetwork.ComputerName  )
 ' on some computers get an error if the domain is not defined. just use the computername then. 
 On error resume next
 strNewName = strNewName + "." + sysInfo.DomainDNSName
 on error goto 0
 strMyName = Wscript.ScriptFullName
 result = writereg("HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\CLSID\{20D04FE0-3AEA-1069-A2D8-08002B30309D}\",strNewName,"REG_SZ")
 ' should do something with the result - ignore it for now. "

 strOldName= "This PC/My Computer label was """ + strOldName + """"
 strNewName = "Label now changed to """ + strNewName + """"
 nl=vbCRLF & vbCRLF 
 call wscript.echo(now & nl & strOldName & nl & strNewName & nl & strMyName )
 wscript.quit


' functions from  http://ss64.com http://ss64.com/vb/regwrite.html

Function WriteReg(RegPath, Value, RegType)
      'Regtype should be “REG_SZ” for string, “REG_DWORD” for a integer,…
      '”REG_BINARY” for a binary or boolean, and “REG_EXPAND_SZ” for an expandable string
      Dim objRegistry, Key
      Set objRegistry = CreateObject("Wscript.shell")

      Key = objRegistry.RegWrite(RegPath, Value, RegType)
      WriteReg = Key

end function

Function ReadReg(RegPath)
      Dim objRegistry, Key
      Set objRegistry = CreateObject("Wscript.shell")

      Key = objRegistry.RegRead(RegPath)
      ReadReg = Key
End Function
Posted in Uncategorized | Comments Off on Replace the icon lables “Computer” and “This PC” with something more useful.