CODEWITHSUNDEEP
powershellselect-string
GettingStarted
GettingStarted

Reputation: 7605

Is there a way to display the lines of text that meets a condition with PowerShell

$data = Select-String -Path $selectedDirectory\$sqlFile -Pattern "GRANT" -Context 5,5

I want to use PowerShell to read .SQL files and we want to make sure that a user isn't using GRANT or DROP or DELETE without a human reviewing the file to see if it's okay.

My 1 line only is looking at GRANT but I don't think it's working.

If the keywords are in the file, I want to display a portion of the text on the screen +/- 5 lines of where the offending text was found.

Is there a way to change the color of the text for the specific line that has the offending search criteria (all other lines will be shown as default)

Upvotes: 1

Views: 1144

Answers (4)

rokumaru
rokumaru

Reputation: 1244

Another way is to use the oss function (=Out-String -Stream).

Select-String "\b(GRANT|DROP|DELETE)\b" .\test.txt -Context 5 | oss | foreach { Write-Host $_ -ForegroundColor ("White","Cyan")[$_[0] -eq '>'] }

The following may make it a bit more readable.

Select-String "\b(GRANT|DROP|DELETE)\b" .\test.txt -Context 5 | Out-String -Stream | foreach {
    if(-not $_) { return }
    $fileName,$lineNumber,$line = $_.Split(":", 3)
    $color = if($_.StartsWith(">")) { "Cyan" } else { "White" }
    Write-Host $fileName $lineNumber.PadLeft(3, "0") $line -ForegroundColor $color -Separator "  "
}

Upvotes: 0

ncfx1099
ncfx1099

Reputation: 367

I'll give it a shot.

This function takes a file, searches for those keywords, and then prints +/- 5 lines. It's easy enough that I'm sure you know how it works and how to modify it. You can find the reference for the matchinfo class (returned by Select-String( here.

Function Get-SQLForbiddenWords ($sqlDataFile) {
    $data =  Select-String -Path $sqlDataFile -Pattern "GRANT|DROP|DELETE" 
    Foreach ( $line in $data) {
        $lineNumberS = $line.LineNumber - 5
        $lineNumberE = $line.LineNumber + 5
        echo ('Bad Command Detected: {0}' -f $line.line)
        (Get-Content $sqlDataFile)[$lineNumberS..$lineNumberE]
        echo "`n"
    }
}

It was pretty fun. Output: 

Bad Command Detected: DROP
this is the sixth line 
GRANT
this is the seventh line
this is the eighth line
DROP
this is the ninth line 
this is the tenth linet
this is the eleventh line 
this is the twelfbthfbth line

Upvotes: 1

AdminOfThings
AdminOfThings

Reputation: 25001

If you want colors displayed to the console, you will need to utilize Write-Host.

$data = Select-String -Path $selectedDirectory\$sqlFile -Pattern "GRANT|DROP|DELETE" -Context 5,5
$data | Foreach-Object {
    $_.Context.Precontext
    Write-Host $_.Line -ForeGroundColor Cyan
    $_.Context.Postcontext
}

Upvotes: 3

Sid
Sid

Reputation: 2676

For starters, "GRANT" should be in quotes to denote a string.

If you notice, $line = Select-String -Pattern "Grant" will return an object.

If you look at the properties of the object using Get-Member, one of them is LineNumber

If you have read the contents of your file using $data = Get-Content File.sql or any something similar, you will have your data as an array object. Now you can now use this line number to extract the +/- 5 lines as you wish like $data[50..60]. This will show output lines from 50th to 60th line. You can easily replace the 50 and 60 with your variables.

enter image description here

Upvotes: 0

Related Questions