PowerShell

Powershell information and commands

Oracle – tkprof – Extract all waits from output file using Powershell

Posted by

0

$astr=@("Event", "\*\*\*\*", "--------")
#Gets them all
#set up the regexp
$regex = '(?ms) Event waited on(.+?)\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'
#get-content gets an array of strings, join them all into one string separated by newline
$string=(Get-Content .\CCI1_ora_15824_2.txt) -join "`n"
# search for the regexp in the string and output the matches
Select-String -input $string -Pattern $regex -AllMatches | `
% { $_.Matches } | `
% { 
    $arr = $_.Value -split  "`n"
    foreach ($line in $arr) {
     # output only lines that do not contain the strings in the array, trim it and replace "more than one space" with a semi-colon
     Select-String -input $line -pattern $astr -NotMatch | ForEach-Object {$_.Line.Trim() -replace '  +',";"}
    }
}

Powershell – Concatenate files

Posted by

Example: To concatenate all .txt files in directory C:\temp and store result in outfile.txt

$ToNatural = { [regex]::Replace($_, '\d+', { $args[0].Value.PadLeft(20) }) }

if (Test-Path "C:\temp\outfile.txt") {
    Remove-Item "C:\temp\outfile.txt"
}

Get-ChildItem "C:\temp" -Filter *.txt|sort-object $ToNatural | `
Foreach-Object{
  Write-Host $_
  #Get-Content $_ >> C:\temp\outfile.txt
}

Powershell – Get restarts and Uptime

Posted by 
gwmi win32_ntlogevent -filter "LogFile='System' and EventCode='1074' and Message like '%restart%'" |select User,@{n="Time";e={$_.
ConvertToDateTime($_.TimeGenerated)}}




</pre>
<pre class="toolbar-overlay:false lang:ps decode:true ">$BootTime = (systeminfo | find "System Boot Time:").Replace("System Boot Time:","").Trim()
$BootTime


$Uptime = (Get-Date) - [datetime]$BootTime
$Uptime

Display the SQL statement that a Linux Process is Executing

Posted by 
-- get the process id from Linux (use top or ps)
-- the process id is spid in v$process
select sql_id from v$session where paddr in (select addr from v$process where spid in (4567))

-- put the sql_id from v$session into v$sqlarea/v$sql_text/v$sql
select sql_text from v$sqlarea where sql_id = 'ap3qms77rt67k'
-- this will display the sql that the process is executing

-- In one query
select sql_text
from v$sqlarea
where sql_id = 
(select sql_id from 
 v$session where paddr in 
 (select addr from v$process where spid = 1820)
);

SQL Server – Display Running Tasks

Posted by

Use this query in conjunction with sp_who to display running tasks and determine whether there are sessions blocking other sessions, and who is executing the query (sp_who)

SELECT command,
            r.session_id, r.blocking_session_id,
            s.text,
            start_time,
            percent_complete, 
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
--WHERE r.command like ('%DBCC%')