Author Archives: positivedb

SQL Full-Text – Advanced queries


Full-text

Advanced queries
To see current database existing Full-Text catalogs, type:

SELECT sftc.name
,sftc.is_default
FROM sys.fulltext_catalogs sftc
ORDER BY sftc.[name]

To see current database existing Stoplists, type:

SELECT sfts.stoplist_id
,sfts.name
FROM sys.fulltext_stoplists sfts

To see current database Full-Text catalog in detail, type:

SELECT sftc.name AS [Catalog Name]
,st.name AS [Table Name]
,si.name AS [Unique Index Name]
,CASE sfti.is_enabled 
WHEN 1 THEN 'Enabled'
ELSE 'Disabled'
END AS [Is Enabled]
,sfti.change_tracking_state_desc AS [Change Tracking]
,sftsl.name AS [Stoplist Name]
FROM sys.fulltext_indexes sfti
INNER JOIN sys.fulltext_catalogs sftc
ON sfti.fulltext_catalog_id = sftc.fulltext_catalog_id
INNER JOIN sys.tables st 
ON st.object_id = sfti.object_id
INNER JOIN sys.indexes si 
ON sfti.unique_index_id = si.index_id
AND sfti.object_id = si.object_id
LEFT OUTER JOIN sys.fulltext_stoplists sftsl
ON sfti.stoplist_id = sftsl.stoplist_id

To see current database all Stopwords from all Stoplists, type:

SELECT sftsl.name AS [Stoplist Name]
,sftsw.stopword AS [Stopword]
,ssl.alias AS [Language Alias]
,ssl.name AS [Language Name]
,ssl.lcid AS [Language LCID]
FROM sys.fulltext_stopwords sftsw
INNER JOIN sys.fulltext_stoplists sftsl 
ON sftsw.stoplist_id = sftsl.stoplist_id
INNER JOIN master.sys.syslanguages ssl
ON sftsw.lcid = ssl.language_id

To see system default Stopwords, type:

SELECT sftstw.stopword
,sftl.name
FROM sys.fulltext_system_stopwords sftssw
INNER JOIN sys.fulltext_languages sftl
ON sftssw.lcid = sftl.language_id

To see all ‘understandable’ document that can be used in varbinary datatype, type:

SELECT sftdt.document_type
,sftdt.path
,sftdt.version
,sftdt.manufacturer 
FROM sys.fulltext_document_types sftdt

To see current database Full-Text index fragments, type:

SELECT st.name AS [Table Name]
,sftif.data_size
, sftif.row_count
,CASE sftif.status
WHEN 0 THEN 'Newly created and not yet used'
WHEN 1 THEN 'Being used for insert'
WHEN 4 THEN 'Closed ready for query'
WHEN 6 THEN 'Being used for merge inpurt and ready for query'
WHEN 8 THEN 'Marked for deletion. Will not be used for query and merge source'
ELSE 'Unknown status code'
END
FROM sys.fulltext_index_fragments sftif
INNER JOIN sys.tables st
ON sftif.table_id = st.object_id

Note: If multiple closed fragments exist for a table performance may be affected. Reorganize de Full-Text catalog.
 

In Depth
http://arcanecode.com/2008/06/03/advanced-queries-for-using-sql-server-2008-full-text-search-stopwords-stoplists/
https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
http://hspinfo.wordpress.com/2011/07/27/microsoft-sql-server-2008-fulltext-and-stoplist-association-manage-multiple-stoplists-fulltext-index/

Tested With
SQL Server 2008 R2
SQL Server 2012

Categories: SQL Full-Text | Leave a comment

Powershell – Regular Expressions for Parse Match or Replace


Regular expressions

List
Regular expression list:

https://positivedb.wordpress.com/wp-admin/post-new.php#edit_timestamp

https://positivedb.wordpress.com/wp-admin/post-new.php#edit_timestamp

RegEx Description
. matches any character except newline
\ escape character
\w word character [a-zA-Z_0-9]
\W non-word character [^a-zA-Z_0-9]
\d Digit [0-9]
\D non-digit [^0-9]
\n new line
\r carriage return
\t tabulation
\s white space
\S non-white space
^ beginning of a line
$ end of a line
\A beginning of the string (multi-line match)
\Z end of the string (multi-line match)
\b word boundary, boundary between \w and \W
\B not a word boundary
\< beginning of a word
\> end of a word
{n} matches exaclty n times
{n,} matches a minimum of n times
{x,y} matches a min of x and max of y
(a|b) ‘a’ or ‘b’
* matches 0 or more times
+ matches 1 or more times
? matches 1 or 0 times
*? matches 0 or more times, but as few as possible
+? matches 1 or more times, but as few as possible
?? matches 0 or 1 time
[abc] a single character of: a, b, or c
[^abc] any single character except: a, b, or c
[a-z] any single character in the range a-z
[a-zA-Z] any single character in the range a-z or A-Z
(…) capture everything enclosed
a? zero or one of a
a* zero or more of a
a+ one or more of a
a{3} exactly 3 of a
a{3,} 3 or more of a
a{3,6} between 3 and 6 of a

Match a single character (returns a True):

‘positivedb’ –match ‘posit.vedb’

Match at least one of a range of characters (returns a True):

‘positivedb and powershell’ –match ‘positiv[a-g] and powershell’

Doesn’t match at least one of a range of characters (returns a True):

‘positivedb and powershell’ –match ‘positiv[^h-z] and powershell’

Match any but these characters, can be specified in one or more contiguous ranges (returns False):

‘positivedb and powershell’ –match ‘positiv[^abc-dfg-hij] and powershell’

The some as above but returning True:

‘positivedb and powershell’ –match ‘positiv[^abc-dfg] and powershell’

Match only if at the beginning of the line (returns ‘ivedb and powershell’):

‘positivedb and powershell’ –replace ‘^posit’,’’

Match only if at the end of the line (returns ‘positivedb and power’):

‘positivedb and powershell’ –replace ‘shell$’,’’

Match zero or more of * preceding character (returns True):

‘positivedb and powershell’ –match ‘z*’
‘positivedb and powershell’ –match ‘z*z*’

The some as above but returns False:

‘positivedb and powershell’ –match ‘^z*$’

Match one or more of preceding and succeeding + (returns True):

‘positivedb and powershell’ –match ‘posi+’

Match a group of characters replaced by . (returns True):

‘positivedb and powershell’ –match ‘posi.’
‘positivedb and powershell’ –match ‘posi.shell’
‘positivedb and powershell’ –match ‘posi.shell$’

 

In Depth
http://www.zerrouki.com/powershell-cheatsheet-regular-expressions/
http://ss64.com/ps/syntax-regex.html

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Concatenate Strings and Variables


Concatenate

To concatenate strings and variables use +

$strvariable = ‘Test’
Write-Host $ strvariable + ‘ and keep ‘ + $ strvariable + ‘ing’
$ strvariable = ‘Test’
Write-Host [string]1000 + ‘ ‘ + $strvariable

 

In Depth
http://social.technet.microsoft.com/Forums/windowsserver/en-US/4e370143-eb34-4acc-96a2-301b102e69ec/concatenate-strings-and-variables?forum=winserverpowershell

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Reading Text File


Reading text file

Get-Content
To quickly read and display a text file content, type:

Get-Content c:\scripts\test.txt

To obtain some stats of file content, type:

Get-Content c:\scripts\test.txt | Measure-Object

To retrieve the last 5 lines, type:

Get-Content c:\scripts\test.txt | Select-Object –last 5

To retrieve first five lines but using a Get-Content property, type:

Get-Content c:\scripts\test.txt –totalcount 5

To tail the end of a file in real time, type:

Get-Content c:\scripts\test.txt –Wait

To filter a text file and see only lines that match a string, type:

Get-Content c:\scripts\test.txt –Wait | Where-Object {$_ -match “ERROR”}

Using the text file content to use it with another cmdlet, like:

Get-Content c:\scripts\test.txt | Foreach-Object {Get-Wmiobject -computername $_ win32_bios}

Note: Get-Content alias is gc, type or cat
Set-Content
To copy first 50 lines to a new text file, type:

Get-Content c:\scripts\test.txt –totalcount 50 | Set-Content c:\scripts\test_copy.txt

 

In Depth
http://technet.microsoft.com/en-us/library/ee176843.aspx

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – OS Recovery configuration


OS Recovery

Get-Member
To list all properties from OS recovery object, type:

Get-WmiObject Win32_OSRecoveryConfiguration | Select-Object *

To change same of the properties, type:

$osrecoveryconf = Get-WmiObject Win32_OSRecoveryConfiguration
$osrecoveryconf.AutoReboot = [bool]1
$osrecoveryconf.DebugInfoType = [uint32]3
$osrecoveryconf.Put()

Note: Debug information type values are:

Value Description
0 None
1 Complete memory dump
2 Kernel memory dump
3 Small memory dump

 

In Depth
http://blogs.technet.com/b/heyscriptingguy/archive/2006/06/02/how-can-i-configure-the-system-failure-options-on-a-windows-server-2003-computer.aspx

Tested With
PowerShell 4.0

Categories: Powershell, WMI | Leave a comment

Powershell – Properties and Methods of a cmdlet


Enumerate properties and methods

Get-Member
To list all methods and properties of an object, type:

Get-EventLog –List | Get-Member

To list only properties, type:

Get-EventLog –List | Get-Member –MemberType Properties

 
Select-Object
To select a specific property or method, type:

Get-Process | Select-Object name,id | Format-List

To select all object except the defined below, type:

Get-Process | Select-Object * -exclude site,container

In the next example we list the last 3 smallest files:

Get-ChildItem c:\windows\*.* | Sort-Object length -descending | Select-Object -last 3

 

In Depth
http://technet.microsoft.com/en-us/library/ee176854.aspx
http://technet.microsoft.com/en-us/library/ee176955.aspx

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Pagefile configuration


Automatic Pagefile management

Disable AutomaticManagedPagefile
To disable automatic Pagefile managemet, type:

$computersys = Get-WmiObject Win32_ComputerSystem -EnableAllPrivileges
$computersys.AutomaticManagedPagefile = $False
$computersys.Put()

To enable, type::

$computersys = Get-WmiObject Win32_ComputerSystem -EnableAllPrivileges
$computersys.AutomaticManagedPagefile = $True
$computersys.Put()

 
Change existing Pagefile
To change existing Pagefile configuration, such as initial and maximum, type:

$physicalmem = Get-WmiObject Win32_PhysicalMemory
$pagefile = Get-WmiObject -Query "Select * From Win32_PageFileSetting Where Name='c:\\pagefile.sys'"
$pagefile.InitialSize = [int]($physicalmem.capacity*1.5/1024/1024)
$pagefile.MaximumSize = [int]($physicalmem.capacity*1.5/1024/1024)
$pagefile.Put()

 
Delete and create Pagefile
To delete an existing Pagefile, type:

$pagefile = Get_WmiObject -Query "Select * From Win32_PageFileSetting Where Name='c:\\pagefile.sys'"
$pagefile.Delete()

To create a new Pagefile, type

Set-WMIInstance -class Win32_PageFileSetting -Arguments @{name="d:\pagefile.sys";InitialSize = 4096;MaximumSize =4096}

 

In Depth
http://myitforum.com/myitforumwp/2013/06/12/pagefile-manual-or-windows-managed/
http://blogs.metcorpconsulting.com/tech/?p=188
http://mitchyb.blogspot.com.es/2013/11/page-file-manipulation-using-powershell.html

Tested With
PowerShell 4.0

Categories: Powershell, WMI | Leave a comment

Powershell – Filtering


Filtering returned data

Where-Object
This cmdlet lets you filter data returned by other cmdlets.
To filter all commands that contain in the name the string export, type:

Get-Command | Where-Object {$_.name –like “*export*”}

Where-Object operators are:

Operator Description
-lt Less than
-le Less than or equal to
-gt Greater than
-ge Greater than or equal to
-eq Equal to
-ne Not equal to
-like Like; uses wildcards for pattern matching
-notlike Not like; uses wildcards for pattern matching
-contains Contains the specified value
-notcontains Doesn’t contain the specified value

Other parameters that can be used:

Parameter Description
-and And
-or Or

To get all svchost processes with handles bigger than 300, type

Get-Process | Where-Object {$_.hangles –gt 300 –or $_.name –eq “svchost”}

To get all files of folder scripts that contain in the name the string test and are bigger than 100Kb, type

Get-ChildItem c:\scripts | Where-Object {$_.name –like “*test*” –and $_.length –gt 100000}

Other option:

Get-ChildItem c:\scripts | Where-Object {$_.name.contains(“test”) –and $_.length –gt 100000}

Note: Where-Object alias is Where
 

In Depth
http://technet.microsoft.com/en-us/library/ee177028.aspx
http://www.howtogeek.com/138324/geek-school-learning-formatting-filtering-and-comparing-in-powershell/

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Configure MSDTC


MSDTC cmdlets resume

The main cmdlets to work with Microsoft Distributed Transaction Coordinator (MSDTC):

Cmdlets Description
Get-Dtc retrieves the list of DTC instances on the machine and the complete list of standard settings (log, network, and trace) for a given DTC instance
Get-DtcLog
Set-DtcLog
retrieve all of the DTC log settings for a given DTC instance on the machine (Get-DtcLog) and write the DTC log settings to the new server (Set-DtcLog)
Get-DtcNetworkSetting
Set-DtcNetworkSetting
retrieve all of the DTC network settings for a given DTC instance on the machine (Get-DtcNetworkSetting) and write the DTC network settings to the new server (Set-DtcNetworkSetting)
Get-DtcTransactionsTraceSetting
Set-DtcTransactionsTraceSetting
retrieve all of the DTC transaction trace settings on the machine (Get-DtcTransactionsTraceSetting) and write the DTC transaction trace settings to the new server (Set-DtcTransactionsTraceSetting)
Get-DtcAdvancedSetting
Set-DtcAdvancedSetting
retrieve (Get-DtcAdvancedSetting) and write (Set-DtcAdvancedSetting) advanced settings that are used by a particular DTC instance on the machine
Get-DtcAdvancedHostSetting
Set-DtcAdvancedHostSetting
retrieve (Get-DtcAdvancedHostSetting) and write (Set-DtcAdvancedHostSetting) advanced host settings that DTC uses on the machine
Start-Dtc starts a particular DTC instance on the new server once it has been configured
Install-Dtc configures the MSDTC NT service for a different startup or log path on the new server

 
MSDTC info
Get-Dtc
To list all server defined MSDTC:

Get-Dtc

 
MSDTC Network Configuration
Get-DtcNetworkSetting
To see MSDTC network configuration to server local MSDTC, type:

Get-DtcNetworkConfiguration DtcName Local

 
Set-DtcNetworkSetting
To configure local MSDTC network configuration, type:

Set-DtcNetworkConfiguration –DtcName Local -RemoteClientAccessEnabled 0 -RemoteAdministrationAccessEnabled 0 -InboundTransactionsEnabled 1 -OutboundTransactionsEnabled 1 -AuthenticationLevel NoAuth -XATransactionsEnabled 0 -LUTransactionsEnabled 0

 

In Depth
http://msdn.microsoft.com/en-us/library/windows/desktop/hh829474%28v=vs.85%29.aspx

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Powershell – Working with Registry


Listing Registry Keys

Get-ChildItem
Registry keys and property can be displayed using Get-ChildItem. To see also hidden and system items, type:

Get-ChildItem HKCU:\ -Force

To query all underlined registry keys and search for keys with less than one subkey with ten values, type:

Get-ChildItem HKCU:\ -Force -Recurse | Where-Object -FilterScript {($_.SubKeyCount -le 1) -and ($_.ValueCount -eq 10)}

To List all Key properties, type:

Get-ChildItem HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion | Select-Object -ExpandProperty Property

The same in a more readable form:

Get-ItemProperty HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion | Select-Object -ExpandProperty Property

 
Copy Registry Key
Copy-Item
To coy a key in another location, type:

Copy-Item -Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion' -Destination HKCU:

To copy the key and all is content, type:

Copy-Item -Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion' -Destination HKCU: -Recurse

 
Create Registry Key
New-Item
To create a new Key, type:

New-Item -Path HKLM:\Software\Microsoft\Rpc\Internet

To create a new key property, type

New-ItemProperty -Path HKLM:\Software\Microsoft\Rpc\Internet -Name Ports -PropertyType MultiString -Value 5984-5994

Note: For key properties be aware:
REG_SZ = String
REG_DWORD = DWord
REG_QWORD = QWord
REG_MULTI_SZ = MultiString
REG_BINARY = Binary
 
Delete Registry Key
Remove-Item
To delete a key, type:

New-Item -Path HKLM:\Software\Microsoft\Rpc\Internet

If subKety exist and the –Recurse parameter is not passed you will be prompted to confirm subKeys deletion.
 

In Depth
http://technet.microsoft.com/en-us/library/dd315270.aspx
http://technet.microsoft.com/en-us/library/dd315394.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2012/05/07/use-the-powershell-registry-provider-to-simplify-registry-access.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2012/03/18/use-powershell-to-find-and-remove-remote-registry-entries.aspx

Tested With
PowerShell 4.0

Categories: Powershell | Leave a comment

Blog at WordPress.com.