IT Related

Friday, November 16, 2018

capturing data using netsh

netsh trace start capture = yes ipv4.address = 10.81.4.196

Start your app

netsh trace stop

Friday, July 20, 2018

Remote patching

1. Who is logged on

qwinsta

2.Logoff sessions:

logoff 3

3. Check if you are admin

$currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
$currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)

4.Copy files to remote computer:

$TargetSession = New-PSSession -ComputerName HALOMEM03

Copy-Item -ToSession $TargetSession -Path "C:\tools\" -Destination "C:\tools\" -Recurse

5. Upgrade Powershell to 5.1:

Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=839516' -OutFile "c:\\temp\\Win8.1AndW2K12R2-KB3191564-x64.msu" -Proxy "http:\\10.81.18.66:3128"
winrs -r:localhost c:\windows\system32\wusa.exe c:\temp\Win8.1AndW2K12R2-KB3191564-x64.msu /extract:c:\temp
winrs -r:localhost dism /online /add-package /PackagePath:c:\temp\WindowsBlue-KB3191564-x64.cab /norestart
shutdown /r /t 10

6. Installing Sysinternal tools:

Invoke-Command -ComputerName jdecli003d -ScriptBlock {
    & { 
        $TARGETDIR = 'c:\tools'
        if(!(Test-Path -Path $TARGETDIR )){
           New-Item -ItemType directory -Path $TARGETDIR
        }
        Invoke-WebRequest -Uri 'https://download.sysinternals.com/files/SysinternalsSuite.zip' -OutFile "c:\\temp\\SysinternalsSuite.zip" -Proxy "http:\\10.81.18.66:3128"
        Expand-Archive C:\temp\SysinternalsSuite.zip -DestinationPath C:\tools

        #alternative expand:
        #Add-Type -A 'System.IO.Compression.FileSystem'
        #[IO.Compression.ZipFile]::ExtractToDirectory('c:\temp\SysinternalsSuite.zip', 'c:\tools')

    }
}

7. Getting list of users who were logged to machine in past 60 days:

Invoke-Command -ComputerName jdecli001d,jdecli002d,jdecli003d,jdecli004d,jdecli005d,jdecli006d,jdecli007d,jdecli008d,jdecli009d,jdecli010d,jdecli011d,jdecli012d,jdecli013d,jdecli014d,jdecli015d,jdecli016d,jdecli017d,jdecli018d,jdecli019d,jdecli020d -ScriptBlock {
    & { 
        Param (
        #[string]$Computer = (Read-Host Remote computer name),
        [string]$Computer = 'localhost',
        [int]$Days = 60
        )
        cls
        $Hostname = hostname
        $Result = @()
        Write-Host "Gathering Event Logs, this can take awhile..."
        $ELogs = Get-EventLog System -Source Microsoft-Windows-WinLogon -After (Get-Date).AddDays(-$Days) -ComputerName $Computer
        If ($ELogs)
        { Write-Host "Processing..."
        ForEach ($Log in $ELogs)
        { If ($Log.InstanceId -eq 7001)
          { $ET = "Logon"
          }
          ElseIf ($Log.InstanceId -eq 7002)
          { $ET = "Logoff"
          }
          Else
          { Continue
          }
          $Result += New-Object PSObject -Property @{
           Time = $Log.TimeWritten
           'Event Type' = $ET
           User = (New-Object System.Security.Principal.SecurityIdentifier $Log.ReplacementStrings[1]).Translate([System.Security.Principal.NTAccount])
           'Computer' = $Hostname
          }
        }
        $Result | Select Time,"Event Type",User,Computer | Sort Time -Descending | export-csv c:\temp\logged.csv -Force #| Out-GridView
        cat c:\temp\logged.csv
        Write-Host "Done."
        }
        Else
        { Write-Host "Problem with $Computer."
        Write-Host "If you see a 'Network Path not found' error, try starting the Remote Registry service on that computer."
        Write-Host "Or there are no logon/logoff events (XP requires auditing be turned on)"
        }
    }
}

8.Change Regional settings

Invoke-Command crkrd1azwe0p,crkrd1azwe1p,crkrd1azwe2p,crkrd1azwe3p,crkrd1azwe4p,crkrd1azwe5p,crkrd1azwe6p,crksqlazwe0p,Crksqlazwe1p,crkupdazwe0p,Crkupdazwe1p -ScriptBlock {
 
if (!(test-path c:\temp)) {New-Item c:\temp -ItemType Directory} 
[xml]$XmlDocument = invoke-webrequest -Uri https://raw.githubusercontent.com/poorleno1/systemlocale/master/USRegion.xml -UseBasicParsing | Select-Object -ExpandProperty content
$XmlDocument.Save("c:\temp\USRegion.xml")
# Set Locale, language etc.
& $env:SystemRoot\System32\control.exe "intl.cpl,,/f:`"c:\temp\USRegion.xml`""
  
# Set Timezone
& tzutil /s "Central European Standard Time"
   
# Set languages/culture
Set-Culture en-US
 
}

Monday, September 4, 2017

Different things

1. Enable Powershell ISE from Windows Server 2008 R2

 Import-Module ServerManager 
 Add-WindowsFeature PowerShell-ISE

2. Using nslookup from CMD:

Nslookup -querytype=all  google.com NameServer | findstr crk

3. Updating DNS records using Powershell:

Add-DnsServerResourceRecordA -Name "DNS-HOSTNAME" -ComputerName "NAMESERVER" -ZoneName "statoilfuelretail.com" -AllowUpdateAny -IPv4Address "1.1.1.1"

Remove-DnsServerResourceRecord -ZoneName "statoilfuelretail.com" -RRType "A" -Name "NAME" -ComputerName 1.1.1.1

Add-ADGroupMember groupname "username"

remove-ADGroupMember groupname "username"

Get-ADGroupMember groupname

New-ADGroup -Path "ou=Groups,ou=NAME,dc=DN_NAME,dc=com" -GroupScope 1 -server SERVERNAME -Name GROUPNAME



Create a structure of folder from another folder with NTFS security setting:




robocopy c:\temp c:\tmp\test1 /zb /e /xf /sec *

Monday, May 8, 2017

how to connect to internal company resource from virtual box on direct access

Issue:
no connection from Virtual box that was provisioned using vagrant to company internal resources while connected there using Direct access.

Solution:
1. Connect to company with DA.
2. Ssh to known linux box inside company with local port forward:

3. Connect to your vagrant box with remote port forward:

 
ssh ubuntu@127.0.0.1 -p 2222 -R 10000:localhost:125 -o LogLevel=ERROR -o IdentitiesOnly=yes -o StrictHostKeyChecking=no -i C:\Jarek\vagrant_hosts\ubuntu_postfix2\.vagrant\machines\default\virtualbox\private_key

Saturday, January 28, 2017

Enabling different features of PI MusicBox.

Enabling different features of PI MusicBox.

1. Fix missing playlists from Spotify:

Comment out string "return" in usr/local/lib/python2.7/dist-packages/mopidy_spotify/session_manager.py

def refresh_playlists(self):
"""Refresh the playlists in the backend with data from Spotify"""
if not self.initialdata_receive_completed:
logger.debug('Still getting data; skipped refresh of playlists')
return
playlists = []
folders = []

2. Enable sound from YouTube:

pip install --upgrade pafy

3. Enable instant play of audio from YouTube:

open the following file for editing in your favorite editor:

/usr/local/lib/python2.7/dist-packages/mopidy/audio/actor.py

remove the download flag from PLAYBIN_FLAGS so only the following options remain:

# Default flags to use for playbin: AUDIO, SOFT_VOLUME
PLAYBIN_FLAGS = (1 << 1) | (1 << 4)
PLAYBIN_VIS_FLAGS = PLAYBIN_FLAGS | (1 << 3)

Save the file and reboot your pi, please note that changing this results in to not being able to seek through the track

Managing Windows power schemes with powercfg

Managing Windows power schemes with powercfg

1. Displaying what is preventing from sleep:

C:\Windows\system32>powercfg -requests
DISPLAY:
[PROCESS] \Device\HarddiskVolume1\Program Files (x86)\Sony\PlayMemories Home\PMBBrowser.exe

SYSTEM:
[PROCESS] \Device\HarddiskVolume1\Program Files (x86)\Sony\PlayMemories Home\PMBBrowser.exe

AWAYMODE:
None.

2. Disabling process that prevents computer going to sleep:

C:\Windows\system32>powercfg /requestsoverride PROCESS PMBBrowser.exe DISPLAY SYSTEM AWAYMODE

Tuesday, August 2, 2016

Finding SQL server Performance bottlenecs

Primary source of information are Dynamic Views and Functions.

1. Connection and session data.

SELECT
    database_id,    -- SQL Server 2012 and after only
    session_id,
    status,
    login_time,
    cpu_time,
    memory_usage,
    reads,
    writes,
    logical_reads,
    host_name,
    program_name,
    host_process_id,
    client_interface_name,
    login_name as database_login_name,
    last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY cpu_time DESC;

cpu_time - miliseconds
memory_usage - 8kb blocks
reads,writes - 8kb blocks that read/written from/to physical disk
logical_reads - 8kb blocks that read/written from/to buffer cache

TIP: for "Program Name" column use a specifically crafted connection string to see
your program name there, example:

Data Source=myServer;
Initial Catalog=myDB;
User Id=myUsername;
Password=myPassword;

Application Name=myApp;

2. What is executed on SQL server

SELECT
        [DatabaseName] = db_name(rq.database_id),
        s.session_id, 
        rq.status,
        [SqlStatement] = SUBSTRING (qt.text,rq.statement_start_offset/2,
            (CASE WHEN rq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),
            qt.text)) * 2 ELSE rq.statement_end_offset END - rq.statement_start_offset)/2),        
        [ClientHost] = s.host_name,
        [ClientProgram] = s.program_name, 
        [ClientProcessId] = s.host_process_id, 
        [SqlLoginUser] = s.login_name,
        [DurationInSeconds] = datediff(s,rq.start_time,getdate()),
        rq.start_time,
        rq.cpu_time,
        rq.logical_reads,
        rq.writes,
        [ParentStatement] = qt.text,
        p.query_plan,
        rq.wait_type,
        [BlockingSessionId] = bs.session_id,
        [BlockingHostname] = bs.host_name,
        [BlockingProgram] = bs.program_name,
        [BlockingClientProcessId] = bs.host_process_id,
        [BlockingSql] = SUBSTRING (bt.text, brq.statement_start_offset/2,
            (CASE WHEN brq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),
            bt.text)) * 2 ELSE brq.statement_end_offset END - brq.statement_start_offset)/2)
    FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests rq
        ON s.session_id = rq.session_id
    CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as qt
    OUTER APPLY sys.dm_exec_query_plan(rq.plan_handle) p
    LEFT OUTER JOIN sys.dm_exec_sessions bs
        ON rq.blocking_session_id = bs.session_id
    LEFT OUTER JOIN sys.dm_exec_requests brq
        ON rq.blocking_session_id = brq.session_id
    OUTER APPLY sys.dm_exec_sql_text(brq.sql_handle) as bt
    WHERE s.is_user_process =1
        AND s.session_id <> @@spid
-- AND rq.database_id = DB_ID()  -- Comment out to look at all databases
    ORDER BY rq.start_time ASC;

cpu_time - miliseconds

ParentStatement - name of store procedure if SP is run, otherwise it is the same as SqlStatement

SqlStatement - current SQL query (can be a part of SP) 
rq.start_time - important to look at those, indicate a problem if values are high, same as in previous chapter.
rq.cpu_time - same as above
rq.logical_reads - same as above
rq.writes  - same as above
p.query_plan = use this to see what is query plan

3. Statement execution statistics.

Finding the worst performing statements:

This keep last sever hours of running statements in SQL memory.

SELECT TOP 20    
        DatabaseName = DB_NAME(CONVERT(int, epa.value)), 
        [Execution count] = qs.execution_count,
        [CpuPerExecution] = total_worker_time / qs.execution_count ,
        [TotalCPU] = total_worker_time,
        [IOPerExecution] = (total_logical_reads + total_logical_writes) / qs.execution_count ,
        [TotalIO] = (total_logical_reads + total_logical_writes) ,
        [AverageElapsedTime] = total_elapsed_time / qs.execution_count,
        [AverageTimeBlocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,
     [AverageRowsReturned] = total_rows / qs.execution_count,    
     [Query Text] = SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
            (CASE WHEN qs.statement_end_offset = -1 
                THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                ELSE qs.statement_end_offset end - qs.statement_start_offset)
            /2),
        [Parent Query] = qt.text,
        [Execution Plan] = p.query_plan,
     [Creation Time] = qs.creation_time,
     [Last Execution Time] = qs.last_execution_time   
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) p
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE epa.attribute = 'dbid'
        AND epa.value = db_id()
    ORDER BY [AverageElapsedTime] DESC; --Other column aliases can be used

4. Missing indexes

Link.

SELECT     
    TableName = d.statement,
    d.equality_columns, 
    d.inequality_columns,
    d.included_columns, 
    s.user_scans,
    s.user_seeks,
    s.avg_total_user_cost,
    s.avg_user_impact,
    AverageCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0), 3),
    TotalCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0) * (s.user_seeks + s.user_scans),3)
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
    ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
    ON d.index_handle = g.index_handle
WHERE d.database_id = db_id()
ORDER BY TableName, TotalCostSavings DESC;

TableName = d.statement - table name that SQL recommends to create an index
d.equality_columns - Comma-separated list of columns that contribute to equality predicates of the form: table.column = constant_value
d.inequality_columns - Comma-separated list of columns that contribute to  inequality predicates, for example, predicates of the form: table.column > constant_value Any comparison operator other than “=” expresses inequality.
d.included_columns - Comma-separated list of columns needed as covering columns for the query.
s.user_scans,
s.user_seeks,
s.avg_total_user_cost,
s.avg_user_impact,

5. Index usage.

SELECT
    [DatabaseName] = DB_Name(db_id()),
    [TableName] = OBJECT_NAME(i.object_id),
    [IndexName] = i.name, 
    [IndexType] = i.type_desc,
    [TotalUsage] = IsNull(user_seeks, 0) + IsNull(user_scans, 0) + IsNull(user_lookups, 0),
    [UserSeeks] = IsNull(user_seeks, 0),
    [UserScans] = IsNull(user_scans, 0), 
    [UserLookups] = IsNull(user_lookups, 0),
    [UserUpdates] = IsNull(user_updates, 0)
FROM sys.indexes i 
INNER JOIN sys.objects o
    ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON s.object_id = i.object_id
    AND s.index_id = i.index_id
WHERE 
    (OBJECTPROPERTY(i.object_id, 'IsMsShipped') = 0)
ORDER BY [TableName], [IndexName];

Look for:

a) lines with NONCLUSTERED type of index with high number of "UserUpdates" and low number of User actions.

b) high value for UserSeeks for NONCLUSTERED type of index, this means that index is properly build.


6. What is memory usage.

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value   FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'   AND counter_name = 'Total Pages';
;WITH src AS(   SELECT        database_id, db_buffer_pages = COUNT_BIG(*) 
FROM sys.dm_os_buffer_descriptors       --WHERE database_id BETWEEN 5 AND 32766       
GROUP BY database_id)SELECT   [db_name] = CASE [database_id] WHEN 32767        THEN 'Resource DB'        ELSE DB_NAME([database_id]) END,   db_buffer_pages,   db_buffer_MB = db_buffer_pages / 128,   db_buffer_percent = CONVERT(DECIMAL(6,3),        db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

--then drill down into memory used by objects in database of your choice

USE DB_name;

WITH src AS(   SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] = COALESCE(i.name, ''),       [Index_Type] = i.type_desc,       p.[object_id],       p.index_id,       au.allocation_unit_id   
FROM       sys.partitions AS p   INNER JOIN       sys.allocation_units AS au       ON p.hobt_id = au.container_id   INNER JOIN       sys.objects AS o       ON p.[object_id] = o.[object_id]   INNER JOIN       sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND p.index_id = i.index_id   WHERE       au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)
SELECT   src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   buffer_pages = COUNT_BIG(b.page_id),   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM   src
INNER JOIN   sys.dm_os_buffer_descriptors AS b  
 ON src.allocation_unit_id = b.allocation_unit_id
WHERE   b.database_id = DB_ID()
GROUP BY   src.[Object],   src.[Type],   src.[Index],   src.Index_Type
ORDER BY   buffer_pages DESC;



7. What is memory usage + index usage statistics.


WITH src AS(   SELECT        [Object] = o.name,       [Type] = o.type_desc,       [Index] = COALESCE(i.name, ''),       [Index_Type] = i.type_desc,       p.[object_id],       p.index_id,       au.allocation_unit_id   
FROM       sys.partitions AS p   INNER JOIN       sys.allocation_units AS au       ON p.hobt_id = au.container_id   INNER JOIN       sys.objects AS o       ON p.[object_id] = o.[object_id]   INNER JOIN       sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND p.index_id = i.index_id   WHERE       au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)


SELECT   src.[Object],   src.[Type],   src.[Index],   src.Index_Type, i.type_desc,  buffer_pages = COUNT_BIG(b.page_id),   buffer_mb = COUNT_BIG(b.page_id) / 128,
 src.object_id,
    [TotalUsage] = IsNull(user_seeks, 0) + IsNull(user_scans, 0) + IsNull(user_lookups, 0),
    [UserSeeks] = IsNull(user_seeks, 0),
    [UserScans] = IsNull(user_scans, 0), 
    [UserLookups] = IsNull(user_lookups, 0),
    [UserUpdates] = IsNull(user_updates, 0)

FROM   src
INNER JOIN   sys.dm_os_buffer_descriptors AS b  
 ON src.allocation_unit_id = b.allocation_unit_id
left outer join sys.indexes i 
on src.object_id=i.object_id
 and src.Index_Type=i.type_desc
 and src.[Index]= i.name

LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON s.object_id = i.object_id
    AND s.index_id = i.index_id
 
WHERE   b.database_id = DB_ID()
--and src.[Index]='icts_transaction_idx5'
GROUP BY   src.[Object],   src.[Type],   src.[Index],   src.Index_Type,
i.type_desc,
src.object_id,
    IsNull(user_seeks, 0) + IsNull(user_scans, 0) + IsNull(user_lookups, 0),
    IsNull(user_seeks, 0),
    IsNull(user_scans, 0), 
    IsNull(user_lookups, 0),
    IsNull(user_updates, 0)
ORDER BY   buffer_pages DESC;


8. Statistics usage,  this shows statistics higher than 5% calculated since last update.


set nocount on

declare @modPercentThreshold   decimal(8,4) = 5.0
  
SELECT 
    [rowmodcounter].[modPercent], 
    names.dbName + '.' + names.schemaName + '.' + names.tableName as tableName,
    names.statsName,
    [sp].[last_updated] as lastUpdated,
    [sp].[modification_counter] as numberOfModifications,
    [sp].[rows] as tableRowCount,
    [sp].[rows_sampled] as rowsSampled,
    [s].[auto_created] as isAnAutoCreatedStats, 
    sampleRate = (1.0 * sp.rows_sampled / sp.rows) * 100,
   'UPDATE STATISTICS ' + names.schemaName + '.' + names.tableName + '(' + names.statsName + ')' as sqlStmt
FROM [sys].[stats] s
              CROSS APPLY [sys].[dm_db_stats_properties]([s].[object_id],[s].[stats_id]) sp
              INNER JOIN [sys].[tables] t
                  ON [s].[object_id] = [t].[object_id] 
              CROSS APPLY (SELECT (1.0 * [sp].[modification_counter] / NULLIF([sp].[rows], 0)) * 100) AS rowmodcounter(modPercent)
              CROSS APPLY (SELECT DB_NAME() as dbName,
                                          SCHEMA_NAME(t.schema_id) as schemaName,
                                          t.[name] as tableName, 
                                          s.[name] as statsName) AS names
WHERE OBJECTPROPERTY(s.[object_id],'IsMSShipped')=0 AND 
                [rowmodcounter].[modPercent] > @modPercentThreshold 
ORDER BY [rowmodcounter].[modPercent] DESC;
Go