Steaming Tech

Friday, 30 April 2010

Windows : Trace recent rdp sessions

Install log parser

Create a bat file like (change server accordingly):
echo off
cls
c:
cd "c:\Program Files\Log Parser 2.2\"
logparser.exe file:TSLoginsDetails_More.sql?source=\\server\Security -o:DATAGRID

Create a sql file like :

SELECT
      timegenerated,
      EXTRACT_TOKEN(Strings,0,'|') AS User,
      EXTRACT_TOKEN(Strings,4,'|') AS ClientName,
      EXTRACT_TOKEN(Strings,5,'|') AS ClientAddress
FROM %Source%
WHERE EventID=682
ORDER BY timegenerated desc

Run the bat file as admin

Windows : Uptime

net stats srv

Thursday, 29 April 2010

MS Sql : Ctas = SELECT INTO

In Oracle

create table T1 as select * from T1

The equivalent of that in Sql Server is SELECT INTO

select * into T2 from T1

Wednesday, 28 April 2010

Windows : Audit recent RDP logins

If the login/logoff is being audited in group policy (default?)

Event log id 682 in the security log shows all log on events including
client name and ip
Event log id 683 in the security log shows all log off events including client name and ip

Monday, 26 April 2010

Excel: Worksheet splitter vb

Sub SalesmanToSheet()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("A2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("A" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
            With ws.Rows(1)
                .HorizontalAlignment = xlCenter
                With .Font
                    .ColorIndex = 5
                    .Bold = True
                End With
            End With
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thursday, 22 April 2010

WIndows : Stop Robocopy trying forever

robocopy srcDir destDir /R:2 /W:10 /MIR'
retry and wait between attempts - default is 1million and 30 seconds

SQL Server: Listing sessions in 2000

SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

SELECT DB_NAME(dbid) as 'Database Name', a.*
FROM master.dbo.sysprocesses a WITH (nolock)
WHERE dbid > 0