powershell for log analysis and data crunching

Powershell

for Log Analysis & Data CrunchingBY MICHELLE D'ISRAELI

@MDISRAELI

#BSIDESLDN2015

Why Powershell?SIEMs are sadly not universal yet Limited environments You might not need Cygwin any more Quick and dirty analysis & tricks Red teams – process data remotely!

#BSIDESLDN2015

About me Babcock MSS

◦ Threat intelligence feeds Serco Security Operations

◦ Web activity investigations◦ Bulk email analysis

Serco Major Incident Manager◦ Incident response◦ Whole host of things

#BSIDESLDN2015

Assumptions BASH / other command lines Regular expressions Object orientated programming

#BSIDESLDN2015

Objectives Where to learn more How powershell is different Really cool tricks

#BSIDESLDN2015

Get-CommandPS D:\> Get-Command

CommandType Name Definition----------- ---- ----------Alias % ForEach-ObjectAlias ? Where-ObjectFunction A: Set-Location A:Alias ac Add-ContentCmdlet Add-Computer Add-Computer [-DomainName]Cmdlet Add-Content Add-Content [-Path] <StrinCmdlet Add-History Add-History [[-InputObjectCmdlet Add-Member Add-Member [-MemberType] <Cmdlet Add-PSSnapin Add-PSSnapin [-Name] <StriCmdlet Add-Type Add-Type [-TypeDefinition]Alias asnp Add-PSSnapInFunction B: Set-Location B:Function C: Set-Location C:Alias cat Get-ContentAlias cd Set-Location

Verbose CMD

◦ Ancient BASH / GNU Posix

◦ Efficent◦ Ancient too

Powershell◦ Explicit◦ Tab completion

#BSIDESLDN2015

man -> Get-Help Where to learn more

How powershell is different

Really cool tricks

PS D:\> Get-HelpTOPIC Get-Help

SHORT DESCRIPTION Displays help about Windows PowerShell cmdlets andconcepts.

LONG DESCRIPTION

SYNTAX get-help {<CmdletName> | <TopicName>} help {<CmdletName> | <TopicName>} <CmdletName> -?

"Get-help" and "-?" display help on one page. "Help" displays help on multiple pages.

Examples:

cat -> Get-Content Where to learn more


Really cool tricks

PS D:\> Get-Content songs.txtArtist - SongMindInABox - SynchronizeTaylor Swift : StyleThe Prodigy -- voodoo peopleOrbital - halcyon & on & onVieon - StarfighterMind.In.A.Bax – AmnesiaTaylor Swift – Blank Space

In BASH / CMD land…. Pipelines = text Cat examplefile

[ AAA BBB CCC DDD … ]

=> One Long Stream of Text

In Powershell land…. Pipelines = objects Get-Content examplefile

[ "AAA" ] =>[ "BBB" ] =>[ "CCC" ] =>[ "DDD" ] =>[ … ] =>

=> Stream of objects

grep -> Where-Object Where to learn more


Really cool tricks

> Get-Content songs.txt | Where-Object { $_ -match "Swift"}Taylor Swift : StyleTaylor Swift – "Blank Space"

> Get-Content songs.txt

| Where-Object { $_ -match "^Mind\.?In\.?A\.?B[ao]x"}MindInABox - SynchronizeMind.In.A.Bax – Amnesia

# …and fixed the file

Where-Object { some-function }

Much more than grep Takes a function

◦ Get-Random True,False◦ $_ = current object◦ $_ -notmatch "regular-expression"

#BSIDESLDN2015

Regular Expressions in Powershell

Regular Expressions on Windows out of the box! Escaping nightmare

◦ Backslash within the regex as normal◦ Backtick ` for escaping Powershell◦ This will catch you out!

$1 etc for group references… BUT…◦ Magic regex references NOT environment variables◦ Use single quotes OR escape the $

sed/awk -> ForEach-Object Where to learn more


Really cool tricks

>> | ForEach-Object { $_ -replace "\s+[:-]+\s+",","}Artist,SongMind.In.A.Box,SynchronizeTaylor Swift,StyleThe Prodigy,voodoo peopleOrbital,halcyon & on & onVieon,StarfighterMind.In.A.Box,AmnesiaTaylor Swift,Blank Space

>| ForEach-Object { $_ -replace "\s+[:-]+\s+",","} >songs.csv

ForEach-Object { some-function }

Takes a function◦ $_ = current object◦ $_ -replace "regular-expression","replacement-text"

#BSIDESLDN2015

~The Powershell Zone~

#BSIDESLDN2015

Import-Csv Where to learn more


Really cool tricks

PS D:\> Import-Csv .\songs.csv

Artist Song

------ ----

Mind.In.A.Box Synchronize

Taylor Swift Style

The Prodigy voodoo people

Orbital halcyon & on & on

Vieon Starfighter

Mind.In.A.Box Amnesia

Taylor Swift Blank Space

Import-Csv $_.column

PS D:\> Import-Csv .\songs.csv | Where-Object {$_.Song -

match "Style"}

Artist Song

------ ----

Taylor Swift Style

Select-Object Where to learn more


Really cool tricks

PS D:\> Import-Csv .\songs.csv | Select-Object Artist

Artist

------

Mind.In.A.Box

Taylor Swift

The Prodigy

Orbital

Vieon

Mind.In.A.Box

Taylor Swift

Group-Object Where to learn more


Really cool tricks

PS D:\> Import-Csv .\songs.csv | Group-Object Artist

Count Name Group

----- ---- -----

2 Mind.In.A.Box {@{Artist=Mind.In.A....

2 Taylor Swift {@{Artist=Taylor Swi...

1 The Prodigy {@{Artist=The Prodig...

1 Orbital {@{Artist=Orbital; S...

1 Vieon {@{Artist=Vieon; Son...

Get-EventLog Where to learn more


Really cool tricks

PS D:\> Get-EventLog Application -Newest 100

Index Time EntryType Source InstanceID Message

----- ---- --------- ------ ---------- -------

59825 May 27 20:54 Warning Microsoft-Windows... 11 The des...

59824 May 27 20:47 Information gupdate 0 The des...

59823 May 27 19:33 0 Office Software P... 1073742727 The Sof...

59822 May 27 19:28 Information Microsoft-Windows... 1000 Perform...


59820 May 27 19:28 Information Office Software P... 1073742827 The Sof...



59817 May 27 19:27 Information Office Software P... 1073742890 Initial...






59811 May 27 12:32 Information Office Software P... 1073742890 Initial...


59809 May 27 10:49 0 Software Protecti... 1073742727 The Sof...




Get-EventLog Does what it says on the tin Requires which event log you want

◦ Eg, Application◦ NB: Security event log requires admin privileges

-newest 100 Get-Help Get-EventLog

$logs = Get-EventLog Application -Newest 100

Group-Object Where to learn more


Really cool tricks

$logs | Group-Object EntryType,Source | Sort-Object Count -descending

Count Name Group

----- ---- -----

10 Information, Software ... {System.Diagnostics.EventLogEntry,...

10 Information, Office So... {System.Diagnostics.EventLogEntry,...

8 Information, gupdate {System.Diagnostics.EventLogEntry,...

7 Information, NvStreamSvc {System.Diagnostics.EventLogEntry,...

6 Information, Microsoft... {System.Diagnostics.EventLogEntry,...

6 0, Software Protection... {System.Diagnostics.EventLogEntry,...

6 0, Office Software Pro... {System.Diagnostics.EventLogEntry,...

5 Information, SkypeUpdate {System.Diagnostics.EventLogEntry,...

4 Information, IAStorDat... {System.Diagnostics.EventLogEntry,...

4 0, WinMgmt {System.Diagnostics.EventLogEntry,...

4 Information, Bonjour S... {System.Diagnostics.EventLogEntry,...

2 Information, AvastVBoxSvc {System.Diagnostics.EventLogEntry,...

2 Error, WinMgmt {System.Diagnostics.EventLogEntry,...

2 Information, Microsoft... {System.Diagnostics.EventLogEntry,...

2 Information, Wlclntfy {System.Diagnostics.EventLogEntry,...

2 Warning, Microsoft-Win... {System.Diagnostics.EventLogEntry,...

2 Information, UNS {System.Diagnostics.EventLogEntry,...

2 Information, VSS {System.Diagnostics.EventLogEntry,...

2 Information, LMS {System.Diagnostics.EventLogEntry,...

2 Information, iPod Service {System.Diagnostics.EventLogEntry,...

2 Information, SecurityC... {System.Diagnostics.EventLogEntry,...

Group and Sort Pivot tables on the command line! Can group by multiple columns Output is a massive hash table

◦ New top-level 'columns' – Count, Name, Values Sort-Object count –descending

$pt = $logs | Group-Object EntryType,Source | Sort-Object Count -descending

Get-Member Where to learn more


Really cool tricks

PS D:\> $pt | Get-Member

TypeName: Microsoft.PowerShell.Commands.GroupInfo

Name MemberType Definition

---- ---------- ----------

Equals Method bool Equals(System.Object obj)

GetHashCode Method int GetHashCode()

GetType Method type GetType()

ToString Method string ToString()

Count Property System.Int32 Count {get;}

Group Property System.Collections.ObjectMode...

Name Property System.String Name {get;}

Values Property System.Collections.ArrayList ...

Fun with objects Where to learn more


Really cool tricks

PS D:\> $pt[7].Group[2]

Index Time EntryType Source

----- ---- --------- ------

59784 May 27 10:41 Information SkypeUpdate

PS D:\> $pt[7].Group[2].Source.ToUpper()

SKYPEUPDATE

Extra stuff Adding new members to objects is a pain but possible Hashtables, everywhere!

◦ @{'foo' = $_.Name; 'widget' = $_.Type} Literally all of .NET available to you

◦ $ie = New-Object -ComObject InternetExplorer.Application

$tempfilename = [System.IO.Path]::GetTempFileName()

[io.file]::WriteAllBytes($tempfilename,(Invoke-WebRequest -URI "http://fqdn/rs_server.dll").content)

12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00Jane 23rdJane 24thJane 25thAlex 23rdAlex 24thAlex 25thClive 23rdClive 24thClive 25th

Result?

Resources & Any Questions?Technet Hey Scripting Guy blog*

ss64.com

Powershell.com

StackOverflow etc

* http://blogs.technet.com/b/heyscriptingguy/

Michelle D’Israeli

@mdisraeli

powershell for log analysis and data crunching

Technology