Frédéric WAUQUIER

Le site personnel de Frédéric WAUQUIER

Create Outlook tasks from all mails into a specific folder

without comments

This macro create a new task for each mail in a specific folder (with attachment of original mail)

Option Compare Text

Public Sub Main()

Dim objNS As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim Msg As Outlook.MailItem
Dim strID As String
Dim olMail As Outlook.MailItem
Dim objTask As Outlook.TaskItem
Dim regex
Dim matches, customSubject, subject

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = "/wf (.*)"
regex.IgnoreCase = True
regex.Global = True

Set objNS = GetNamespace("MAPI")
Set objFolder = objNS.Folders("frederic.wauquier@flyinggroup.aero")
Set objFolder = objFolder.Folders("Boîte de réception")
Set objFolder = objFolder.Folders("To Tasks")

For Each MyMail In objFolder.Items
  If TypeName(MyMail) = "MailItem" Then
    strID = MyMail.EntryID
    Set olMail = objNS.GetItemFromID(strID)
    Set objTask = Application.CreateItem(olTaskItem)
    objTask.Attachments.Add MyMail

    Set matches = regex.Execute(olMail.Body)
    If matches.Count <> 0 Then
      customSubject = matches(0).submatches(0)
    Else
      customSubject = ""
    End If
    If customSubject <> "" Then
      subject = customSubject
    Else
      subject = olMail.subject
    End If
    With objTask
      .subject = subject
      .categories = "@NEW"
      .Body = olMail.Body
      .RTFBody = olMail.RTFBody
      .Sensitivity = olMail.Sensitivity
      .Importance = olMail.Importance
      .ContactNames = olMail.Sender.Name
      .ToDoTaskOrdinal = olMail.ToDoTaskOrdinal
    End With
    objTask.Save
    Set objTask = Nothing
    Set olMail = Nothing
  End If
Next MyMail
End Sub

Written by Frédéric WAUQUIER

mars 28th, 2014 at 2:41

Cleanup Xbmc mySql database

without comments

Remove incorrect movies

Delete FROM `movie` WHERE IdFile not in (select idFile from files);
Delete FROM `episode` WHERE IdFile not in (select idFile from files);
Delete FROM `musicvideo` WHERE IdFile not in (select idFile from files);

Remove Sets without movies

delete from sets where idset not in (select idset from movie where not idset is null);

List of sets with only one movie

select * from sets where idset in (SELECT DISTINCT idSet FROM movie WHERE not idset is null group by idset having count(*)<2);

Remove sets with less than 3 movies referred

update movie
	left join 
		(SELECT DISTINCT idSet 
        		FROM movie 
                	WHERE not idset is null 
                	group by idset 
                	having count(*)<3) as P 
                on P.idSet=movie.idSet
       	set movie.idSet=null
        where (not movie.idset is null)
        	and (not P.idSet is null);

delete from sets where idset not in (select idset from movie where not idset is null);

Full Set Optimization

update sets set strSet=trim(replace(strSet,'(',''));
update sets set strSet=trim(replace(strSet,')',''));
update sets set strSet=trim(replace(strSet,'''',' '));
update sets set strSet=trim(replace(strSet,'\\',''));
update sets set strSet=trim(replace(strSet,'Collection',''));
update sets set strSet=trim(replace(strSet,'Trilogie',''));
update sets set strSet=trim(replace(strSet,'Saga',''));

update movie
	left join sets on sets.idSet=movie.idSet
       	set movie.idSet=null
        where (not movie.idset is null) and (trim(sets.strSet)='');
        
update movie
	left join (SELECT DISTINCT idSet 
        		FROM movie 
                        WHERE not idset is null 
                        Group by idset  	
                        having count(*)<3) as P 
                on P.idSet=movie.idSet
       	set movie.idSet=null
        where (not movie.idset is null) and (not P.idSet is null);

update movie
	left join (select s1.idSet, s2.idSet AS idSetOk 
            		from sets AS s1
	 		join (select strSet,min(idSet) as idSet 
                        		from sets 
                                        group by strSet) as s2
         			on s1.strSet=s2.strSet
           		having s1.Idset<>s2.idSet) as P 
                on P.idSet=movie.idSet
       	set movie.idSet=P.idSetOk
        where (not movie.idset is null) 
        and (not P.idSet is null)
        and (movie.idSet<>P.idSetOk);
        
delete from sets where idset not in (select idset from movie where not idset is null);

select * from sets limit 0,1000;

Written by Frédéric WAUQUIER

septembre 21st, 2013 at 2:16

Posted in Non classé

Update name of Cisco Ip Phone In SpiceWorks

without comments

Update devices set name=replace(description,'Cisco IP ','')
where device_type='VoipDevice'
and description like 'Cisco IP Phone %';

select name,manufacturer,model,server_name as Address from devices
where device_type='VoipDevice'
and description like 'Cisco IP Phone %' order by Address;

Written by Frédéric WAUQUIER

août 22nd, 2013 at 11:05

Posted in SpiceWorks

List of SQL server instance in SpiceWorks

without comments

Create a new report with ‘Build this report using SQL’ checked and copy following SQL to generate report

SELECT Distinct devices.name as ComputerName
       ,Software.Name as SqlVersionInstalled
       ,Software_installations.Version
       ,microsoft_sql_servers.name as InstanceName
       ,devices.Operating_system as OS
       ,devices.number_of_processors as NumberOfProcessor
       ,devices.processor_type as ProcessorType
       ,devices.memory/(1024*1024) as Memory

       FROM microsoft_sql_servers
            JOIN devices ON microsoft_sql_servers.computer_id = devices.id
            JOIN Software_installations ON devices.id=Software_installations.computer_id
            JOIN Software on Software_installations.Software_id=software.id
            Where Software.Name like 'Microsoft SQL Server%Edition'
       Order by     devices.name,microsoft_sql_servers.name
            

Written by Frédéric WAUQUIER

juin 19th, 2013 at 11:35

Convert all audio files recursively to mp3 using ffmpeg and PowerShell

without comments

$objParent = Get-ChildItem . -Include *.aac, *.flac, *.m4p, *.ogg, *.ra, *.rm, *.ram, *.raw, *.wav, *.wma -recurse 
foreach ($child in $objParent) 
{
	$previousName= $child.FullName 
	$newName= $child.DirectoryName + "\" + $child.BaseName + ".mp3" 

	$inputArgs='-i '+'"'+$previousName+'"'
	$destArgs=' '+'"'+$newName+'"'
	$convertArgs=' -q:a 2'

	$arguments = $inputArgs+$convertArgs+$destArgs
	invoke-expression "& 'C:\Program Files\FFmpeg\bin\ffmpeg.exe' $arguments"

    $mp3file = get-item $newName

    if ($mp3file.Length -gt 0)
	{
        echo "----- removing $previousName"
        Remove-Item $previousName
    }
    else
    {
        echo "----- removing $newName"
        Remove-Item $newName        
    }
}

Written by Frédéric WAUQUIER

février 10th, 2013 at 12:58

Posted in PowerShell

Install Windows Updates on Windows Server 2008 R2 Core

without comments

Set updateSession = CreateObject("Microsoft.Update.Session")
Set updateSearcher = updateSession.CreateupdateSearcher()
 
WScript.Echo "Searching for updates..." & vbCRLF
 
Set searchResult = _
 updateSearcher.Search("IsInstalled=0 and Type='Software'")
 
WScript.Echo "List of applicable items on the machine:"
 
For I = 0 To searchResult.Updates.Count-1
 Set update = searchResult.Updates.Item(I)
 WScript.Echo I + 1 & "> " & update.Title
 Next
 
If searchResult.Updates.Count = 0 Then
 WScript.Echo "There are no applicable updates."
 WScript.Quit
 End If
 
WScript.Echo vbCRLF & "Creating collection of updates to download:"
 
Set updatesToDownload = CreateObject("Microsoft.Update.UpdateColl")
 
For I = 0 to searchResult.Updates.Count-1
 Set update = searchResult.Updates.Item(I)
 WScript.Echo I + 1 & "> adding: " & update.Title
 updatesToDownload.Add(update)
 Next
 
WScript.Echo vbCRLF & "Downloading updates..."
 
Set downloader = updateSession.CreateUpdateDownloader()
 downloader.Updates = updatesToDownload
 downloader.Download()
 
WScript.Echo  vbCRLF & "List of downloaded updates:"
 
For I = 0 To searchResult.Updates.Count-1
 Set update = searchResult.Updates.Item(I)
 If update.IsDownloaded Then
 WScript.Echo I + 1 & "> " & update.Title
 End If
 Next
 
Set updatesToInstall = CreateObject("Microsoft.Update.UpdateColl")
 
WScript.Echo  vbCRLF & _
 "Creating collection of downloaded updates to install:"
 
For I = 0 To searchResult.Updates.Count-1
 set update = searchResult.Updates.Item(I)
 If update.IsDownloaded = true Then
 WScript.Echo I + 1 & "> adding:  " & update.Title
 updatesToInstall.Add(update)
 End If
 Next
 
WScript.Echo  vbCRLF & "Would you like to install updates now? (Y/N)"
 strInput = WScript.StdIn.Readline
 WScript.Echo
 
If (strInput = "N" or strInput = "n") Then
 WScript.Quit
 ElseIf (strInput = "Y" or strInput = "y") Then
 WScript.Echo "Installing updates..."
 Set installer = updateSession.CreateUpdateInstaller()
 installer.Updates = updatesToInstall
 Set installationResult = installer.Install()
 
'Output results of install
 WScript.Echo "Installation Result: " & _
 installationResult.ResultCode
 WScript.Echo "Reboot Required: " & _
 installationResult.RebootRequired & vbCRLF
 WScript.Echo "Listing of updates installed " & _
 "and individual installation results:"
 
For I = 0 to updatesToInstall.Count - 1
 WScript.Echo I + 1 & "> " & _
 updatesToInstall.Item(i).Title & _
 ": " & installationResult.GetUpdateResult(i).ResultCode
 Next
 End If

Copy the text found in the script, save it as WUA_SearchDownloadInstall.vbs in the system32 folder, and run:

cscript WUA_SearchDownloadInstall.vbs

Written by Frédéric WAUQUIER

janvier 26th, 2013 at 11:46

Posted in Serveur,Windows

Summary of SQL table components (Data, index, lob)

without comments

SELECT '['+sch.name+'].['+obj.name+']',
	max(s.row_count ) as [RowCount],
	SUM(s.used_page_count) *8   AS ObjectSizeKb,
	sum(s.in_row_used_page_count)*8  as DataSizeKb,
	sum(case when s.index_id in (0,1) then  s.in_row_used_page_count else 0 end )*8  as DataSizeKb,
	sum(case when not s.index_id in (0,1) then  s.in_row_used_page_count else 0 end )*8  as IndexSizeKb,
	sum(case when s.index_id in (0,1) then  1 else 0 end )   as DataCount,
	sum(case when not s.index_id in (0,1) then  1 else 0 end )   as IndexCount,
	sum(s.lob_used_page_count)*8  as LobSizeKb 
	 
	 
	FROM sys.dm_db_partition_stats  AS s 
	 Left JOIN sys.objects obj on obj.object_id = s.object_id 
	Left JOIN sys.schemas sch on obj.schema_id = sch.schema_id 
	WHERE sch.name<>'sys'
	GROUP BY sch.name,obj.name  
	order by sum(s.in_row_used_page_count) desc

Written by Frédéric WAUQUIER

décembre 17th, 2012 at 8:23

Size of all objects (Tables and Indexes) on a database

without comments

WITH StatsDetails as (
SELECT obj.name,
	i.name AS IndexName,
	s.index_id,
	sum(s.row_count ) as [RowCount],
	SUM(s.used_page_count) *8   AS ObjectSizeKb,
	sum(s.in_row_data_page_count)*8  as RowDataSizeKb,
	sum(s.in_row_reserved_page_count)*8  as RowReservedSizeKb,
	sum(s.in_row_used_page_count)*8  as RowUsedSizeKb,
	sum(s. lob_reserved_page_count)*8  as LobReservedSizeKb,
	sum(s.lob_used_page_count)*8  as LobUsedSizeKb 
	 
	 
	FROM sys.dm_db_partition_stats  AS s 
	Left JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	Left JOIN sys.objects obj on obj.object_id = i.object_id 
	Left JOIN sys.schemas sch on obj.schema_id = sch.schema_id 
--WHERE s.[object_id] = object_id('dbo.TableName')
	GROUP BY obj.name, i.name,s.index_id

)
select * from StatsDetails order by Name 

Written by Frédéric WAUQUIER

décembre 17th, 2012 at 8:21

Posted in SQL Serveur,TSQL

Script to shrink all user databases

without comments

--Script to shrink all databases
declare @db varchar(255)
declare c cursor for
select name from sys.databases where is_read_only=0 and state=0
and name not in ('master','model','tempdb','msdb')
open c
fetch c into @db
while @@fetch_status=0
begin
exec SP_dboption @db,'trunc. log on chkpt.','true'
DBCC shrinkdatabase (@db)
fetch next from c into @db
end
close c
deallocate c

Written by Frédéric WAUQUIER

décembre 17th, 2012 at 7:35

Posted in SQL Serveur,TSQL

Windows Cluster : Refresh VM resources after a re-added CSV volume

without comments

From Windows Powershell Modules on one of one of cluster node :
get-clusterresource -c <your-cluster-name-here> | where {$_.resourcetype.name -eq 'virtual machine configuration'} | Update-ClusterVirtualMachineConfiguration

Written by Frédéric WAUQUIER

septembre 21st, 2012 at 1:35

Posted in Non classé