Excel Macro to Convert to CamelHumpNotation


Warning: sizeof(): Parameter must be an array or an object that implements Countable in H:\root\home\svermaak-001\www\ITtelligenceBlog\wp-content\plugins\ad-injection\ad-injection.php on line 824

Warning: count(): Parameter must be an array or an object that implements Countable in H:\root\home\svermaak-001\www\ITtelligenceBlog\wp-content\plugins\ad-injection\ad-injection.php on line 831

This is a macro that convert values to CamelHumpNotation

Sub FixCamelHump()
'
' FixCamelHump Macro
'
    Dim strValue As String

    Range("A1").Select
    strValue = ActiveCell.Value
    Do While strValue <> ""
        ActiveCell.Value = FixThisString(strValue)
        ActiveCell.Offset(1, 0).Select
        strValue = ActiveCell.Value
    Loop
End Sub

Private Function FixThisString(strValue As String)
    Dim intCnt As Integer
    Dim blnStartOfWord As Boolean
    Dim strBuild As String
    Dim strCurrentChar As String

    For intCnt = 1 To Len(strValue)
        strCurrentChar = Mid(strValue, intCnt, 1)
        If IsLetter(strCurrentChar) = True Then
            If blnStartOfWord = False Then
                blnStartOfWord = True
                strBuild = strBuild & UCase(strCurrentChar)
            Else
                strBuild = strBuild & LCase(strCurrentChar)
            End If
        Else
            If blnStartOfWord = True Then
                blnStartOfWord = False
            End If
            strBuild = strBuild & strCurrentChar
        End If
    Next
    FixThisString = strBuild
End Function

Private Function IsLetter(strChar As String) As Boolean
    If Asc(strChar) >= 97 And Asc(strChar) <= 122 Or Asc(strChar) >= 65 And Asc(strChar) <= 90 Then
        IsLetter = True
        Exit Function
    End If
    IsLetter = False
End Function

Generic InputBox for CMDs

Usage: CScript.exe //NOLOGO InputBox.vbs /SendTo:SOMEAPP.BAT /prompt:”YOUR PROMPT GOES HERE” /title:”YOUR TITLE GOES HERE”

Example: CScript.exe //NOLOGO InputBox.vbs /SendTo:RUN.BAT /prompt:”Please enter you name” /title:”Name?”

Value supplied will be send to RUN.BAT as %1

Attachment(s):

VBS implementation of REG.exe for use in GPOs

Usage: REG.vbs /Action:ADD|/Action:DELETE /Key:REGISTRYKEY [/Value:REGISTRYVALUE] [/DATA:REGISTRYVALUEDATA] [/TYPE:REGISTRYVALUETYPE]

Examples:
REG.vbs /Action:Add /Key:HKLM\SOFTWARE\NewKey
REG.vbs /Action:Add /Key:HKLMSOFTWARENewKey /Value:New_REG_SZ /Data:Value /Type:REG_SZ
REG.vbs /Action:Add /Key:HKLMSOFTWARENewKey /Value:New_REG_Binary /Data:”0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0″ /Type:REG_BINARY
REG.vbs /Action:Add /Key:HKLMSOFTWARENewKey /Value:New_REG_DWORD /Data:0 /Type:REG_DWORD
REG.vbs /Action:Add /Key:HKLMSOFTWARENewKey /Value:New_REG_EXPAND_SZ /Data:Value /Type:REG_EXPAND_SZ
REG.vbs /Action:Add /Key:HKLMSOFTWARENewKey /Value:New_REG_MULTI_SZ /Data:”Value1,Value2,Value3″ /Type:REG_MULTI_SZ
REG.vbs /Action:Delete /Key:HKLMSOFTWARENewKey /Value:New_REG_SZ
REG.vbs /Action:Delete /Key:HKLMSOFTWARENewKey

Attachment(s):

How to start a disk cleanup on remote computer

If you want to start a disk cleanup with cleanmgr.exe, a sageset needs to exist.
Then following batch first replicates the settings that the command “cleanmgr /sageset:777” with all options selected would have create. After that it executes “cleanmgr /sagerun:777”.
For more info on cleanmgr.exe see http://cr.tl/11hm

@Echo Off
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Active Setup Temp Folders" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Compress old files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Content Indexer Cleaner" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Downloaded Program Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Internet Cache Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Memory Dump Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Microsoft_Event_Reporting_2.0_Temp_Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Offline Pages Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Old ChkDsk Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Recycle Bin" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Remote Desktop Cache Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Setup Log Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\Temporary Files" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
REG ADD "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VolumeCaches\WebClient and WebPublisher Cache" /v "StateFlags0777" /t REG_DWORD /d 00000002 /f
CleanMgr /sagerun:777

This batch file can then be executed on remote host with PSExec from Sysinternals
PSExec.exe \\RemoteHost -c -d -f DiskCleanup.bat

How to copy large files over extremely slow and utilised networks (Without BITS)

Tools required

7za.exe – Standalone 7z command-line tool
RoboCopy.exe – Robust copying tool from MS Resource Kit
PSExec.exe – Remote process execution tool from Sysinternals

Steps

1. Compress and span data that you wish to transmit. Compressing obviously reduces the size of data that needs to be transferred and spanning data over a number of small files means that the checkpoint for each copy is reached easier and fewer bits are retransmitted.

7za.exe a -v102400 [ARCHIVENAME].7z “[PATHTOCOMPRESS]”

2. RoboCopy spanned archives to target host The /IPG interpacket gap setting of a 100 seems to reduce the amount of recopies. RoboCopy is a good tool for copying files in general but it still couldn’t transfer the large files that where required without breaking it into 100KB chunks.

RoboCopy.exe “\[TARGETHOST]C$Temp” *.7z.* /zb /w:3 /ipg:100

3. RoboCopy.exe 7za.exe to target host. This standalone is required to extract spanned archive.

RoboCopy .exe “\[TARGETHOST]C$Windows” 7za.exe /zb /w:3 /ipg:100

4. Execute 7za.exe remotely with PSExec.exe.

Psexec.exe \ [TARGETHOST] -d 7za.exe e C:Temp[ARCHIVENAME].7z.001 -aoa -o”PATHTODECOMPRESS”

Automated in a batch

@Echo Off
del *.7z.*
7za a -v102400 %2.7z "%3"
robocopy . "\%1c$Temp" *.7z.* /zb /w:3 /ipg:100
robocopy . "\%1c$Windows" 7za.exe /zb /w:3 /ipg:100
psexec \%1 -d 7za e C:Temp%2.7z.001 -aoa -o"%4"
</span></em>

Usage: CopyData.bat HOSTNAME ARCHIVENAME SOURCEFOLDER DESTINATIONFOLDER

Enumerate All Empty Active Directory Groups


Warning: count(): Parameter must be an array or an object that implements Countable in H:\root\home\svermaak-001\www\ITtelligenceBlog\wp-content\plugins\list-attachments-shortcode\list-attachments-shortcode.php on line 123

Enumerate All Empty Active Directory Groups

The following script was created to enumerate all the empty groups that exist in an Active Directory.

This output of the script can be piped to a text file.

Basic steps are
1) Create connection to Active Directory domain
2) Create recordset from query, filtering in only empty groups
3) Enumerate through recordset, displaying name of group
4) Cleanup

Option Explicit

On Error Resume Next

Dim objCommand
Dim objConnection
Dim objRootDSE
Dim strDNSDomain
Dim strBase
Dim objSystemInfo
Dim strDomain
Dim strFilter
Dim strAttributes
Dim strQuery
Dim objRecordset
Dim strGroupName

Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")

objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection

Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = ""

Set objSystemInfo = CreateObject("ADSystemInfo")
strDomain = objSystemInfo.DomainShortName

strFilter = "(&amp;(objectCategory=group)(!member=*))"

strAttributes = "sAMAccountName"

strQuery = strBase &amp; ";" &amp; strFilter &amp; ";" &amp; strAttributes &amp; ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

Set objRecordset = objCommand.Execute

Do Until objRecordset.EOF
strGroupName = objRecordset.Fields("sAMAccountName").Value
WScript.Echo strGroupName
objRecordset.MoveNext
Loop

objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objSystemInfo = Nothing
Set objRootDSE = Nothing
Set objConnection = Nothing
Set objCommand = Nothing

Attachment(s):

Recreate DNS Hosts From Export File Into a Microsoft DNS Server

Recreate DNS Hosts From Export File Into a Microsoft DNS Server

The following script was created to add hosts, from a list of exported hosts and IPs originally from a unrelated DNS server’s zone, into the specified Microsoft DNS server’s DNS zone.

The basic usage of the script is:
CScript AddDNSHost.vbs /DNSServer:DNSServer /DNSZone:DNSZone /HostName:HostName /HostIP:HostIP
And an example:
AddDNSHost.vbs /DNSServer:192.168.0.1 /DNSZone:DNSZone.local /HostName:MyComputer /HostIP:192.168.1.123

A very simple way of constructing multiple commands can be achieved with practically any speadsheet application where column A holds the list of host names, column B holds their respected IP addresses and column C the following command (starting from row 1):

=”AddDNSHost.vbs /DNSServer:192.168.0.1 /DNSZone:DNSZone.local /HostName:” & A1 & ” /HostIP:” & B1
The above command can be copied once for each row.

The resulting constructed command can then be directly pasted into a command prompt

On Error Resume Next

strDNSServer = Wscript.Arguments.Named("DNSServer")
strDNSZone = Wscript.Arguments.Named("DNSZone")
strHostName = Wscript.Arguments.Named("HostName")
strHostIP = Wscript.Arguments.Named("HostIP")

If Len(Trim(strDNSServer)) &gt; 0 And Len(Trim(strDNSZone)) &gt; 0 And Len(Trim(strHostName)) &gt; 0 And Len(Trim(strHostIP)) &gt; 0 Then
If Right(UCase(strHostName), Len(strDNSZone) + 1) "." &amp; UCase(strDNSZone) Then
strHostName = strHostName &amp; "." &amp; strDNSZone
End If
intRecordClass = 1
intTTL = 600

strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\" &amp; strComputer &amp; "rootMicrosoftDNS")
Set objItem = objWMIService.Get("MicrosoftDNS_AType")
intReturn = objItem.CreateInstanceFromPropertyData(strDNSServer, strDNSZone, strHostName, intRecordClass, intTTL, strHostIP)
If Err.Number = 0 And intReturn = 0 Then
WScript.Echo strHostName &amp; vbTab &amp; "Added"
Else
WScript.Echo strHostName &amp; vbTab &amp; "Failed"
End If
End If

Enumerate All Active Directory Users’ ProxyAddresses

Enumerate All Active Directory Users’ ProxyAddresses

The following script was created to enumerate all the various addresses an Active Directory users might have.

This output of the script can be piped to a text file which in turn can be imported into a database to generate various reports.

Basic steps are
1) Create connection to Active Directory domain
2) Create recordset from query, filtering in only user accounts
3) Enumerate through recordset, display combination of sAMAccountName and proxyAddress
4) Cleanup

Option Explicit

On Error Resume Next

Dim objCommand
Dim objConnection
Dim objRootDSE
Dim strDNSDomain
Dim strBase
Dim objSystemInfo
Dim strDomain
Dim strFilter
Dim strAttributes
Dim strQuery
Dim objRecordset
Dim strUserName
Dim strCN
Dim objUser
Dim arrproxyAddresses
Dim strproxyAddresses

Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")

objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection

Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = ""

Set objSystemInfo = CreateObject("ADSystemInfo")
strDomain = objSystemInfo.DomainShortName

strFilter = "(&amp;(objectCategory=Person)(objectClass=User))"

strAttributes = "sAMAccountName,cn,proxyAddresses"

strQuery = strBase &amp; ";" &amp; strFilter &amp; ";" &amp; strAttributes &amp; ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

Set objRecordset = objCommand.Execute

Do Until objRecordset.EOF
strUserName = objRecordset.Fields("sAMAccountName").Value
strCN = objRecordset.Fields("cn").value
arrproxyAddresses = objRecordset.Fields("proxyAddresses").value
If IsArray(arrproxyAddresses) = True Then
For Each strproxyAddresses In arrproxyAddresses
WScript.Echo strUserName &amp; vbTab &amp; strproxyAddresses
Next
End If
objRecordset.MoveNext
Loop

objRecordset.Close
objConnection.Close

set objUser = Nothing
Set objRecordset = Nothing
Set objSystemInfo = Nothing
Set objRootDSE = Nothing
Set objConnection = Nothing
Set objCommand = Nothing

Attachment(s):

DoesServiceExist – Detect if service exist on remote host

DoesServiceExist – Detect if service exist on remote host

This script can be used to detect whether or not a service exist on a remote host.
I originally created it to determine if WDS is installed (ServiceName = WDSServer)

Usage: CScript.exe //nologo DoesServiceExist.vbs /Computer:HOSTNAME /ServiceName:SERVICENAME

Example: CScript.exe //nologo DoesServiceExist.vbs /Computer:CompanyServer01 /ServiceName:WDSServer

Option Explicit

On Error Resume Next

Dim strComputer
Dim strServiceName
Dim objComputer
Dim objService

strComputer = Wscript.Arguments.Named("Computer")
strServiceName = Wscript.Arguments.Named("ServiceName")

If Len(Trim(strComputer)) &gt; 0 And Len(Trim(strServiceName)) &gt; 0 Then
If IsOnline(strComputer) = True Then
Set objComputer = GetObject("WinNT://" & strComputer & ",computer")

If Err.Number = 0 Then
Set objService = objComputer.GetObject("service", strServiceName)

If Err.Number = 0 Then
WScript.Echo strComputer & vbTab & "Services Status" & vbTab & "Exists"
Else
WScript.Echo strComputer & vbTab & "Services Status" & vbTab & "Does not exist"
End If
Else
WScript.Echo strComputer & vbTab & "Services Status" & vbTab & "Unknown"
End If
Else
WScript.Echo strComputer & vbTab & "Services Status" & vbTab & "Offline"
End If
Else
WScript.Echo "Usage: cscript.exe //nologo DoesServiceExist.vbs /Computer:HOSTNAME /ServiceName:SERVICENAME"
End If

Function IsOnline(strComputer)
Dim objPing
Dim objStatus

Set objPing = GetObject("winmgmts:" & Chr(123) & "impersonationLevel=impersonate" & Chr(125) & "").ExecQuery("select * from Win32_PingStatus where address = '" & strComputer & "'")

For Each objStatus in objPing
If IsNull(objStatus.StatusCode) or objStatus.StatusCode0 Then
'Nothing
Else
IsOnline = True
End If
Next
End Function

Attachment(s):

InstallWDS – Installing Windows Deployment Services (WDS) from a batch file

Installing Windows Deployment Services (WDS) from a batch file

I needed to install WDS on 300+ servers but found that the server build did not have a local copy of OS setup or service pack files. I also wanted to set the paths back to the original values.

I started by adding all files required by WDS to the DFS and I created the following batch file.

@ECHO OFF

REG EXPORT "HKLMSOFTWAREMicrosoftWindowsCurrentVersionSetup" %WINDIR%InstallWDS.reg /y
REG ADD "HKLMSOFTWAREMicrosoftWindowsCurrentVersionSetup" /v "SourcePath" /t REG_SZ /d "\SERVERNAMESHARE" /f
REG ADD "HKLMSOFTWAREMicrosoftWindowsCurrentVersionSetup" /v "ServicePackSourcePath" /t REG_SZ /d "\SERVERNAMESHARE" /f

VER | Find "Microsoft Windows [Version 5.2." &gt; nul
If %ERRORLEVEL% == 0 GoTo Win2003
VER | Find "Microsoft Windows [Version 6.0." &gt; nul
If %ERRORLEVEL% == 0 GoTo Win2008

GoTo CleanUp

:Win2003
ECHO [Components] &gt; %WINDIR%InstallWDS.inf
ECHO RemInst = on &gt;&gt; %WINDIR%InstallWDS.inf
Sysocmgr.exe /i:sysoc.inf /u:%WINDIR%InstallWDS.inf
GoTo CleanUp

:Win2008
ServerManagerCmd -install WDS
GoTo CleanUp

:CleanUp
REG IMPORT %WINDIR%InstallWDS.reg
DEL %WINDIR%InstallWDS.inf
DEL %WINDIR%InstallWDS.reg

Attachment(s):