Reading and exporting lines to a headered CSV depending on a line preceeding it

Hey guys, back again with a brain buster for me.For those of you who assisted on my other project i very much appreciate it. i have another quandary for you guys

$reg1file = "C:\install\script\cashdrawerlogexport\REG1_oposcashdrawer.log"
$oposlogfile = "C:\Install\Script\CashDrawerLogExport\"+$StoreNo+"_"+$date+"_LineDetail_oposlog.csv"
Add-Content -Path $oposlogfile -Value '"Store","REG","Keyed_Event_Details"'
if (Test-Path $reg1file) {
write-host "##########################################################################"
write-host "Count Pattern for Total Drawer pops on REG1 and return corresponding lines"
write-host "##########################################################################"
$reg1_totalcount = (get-content $reg1file | Select-String -pattern 'Cash Drawer 1 Opened. evt','Rx pop drawer 1 from POS').Line
}

How can i take these lines and output only the lines containing ‘Cash Drawer 1 Opened. evt’ that dont have the ‘Rx pop drawer 1 from POS’ within the last 4 preceeding lines.

i.e.

,‘Rx pop drawer 1 from POS’

line 2

line 3

‘Cash Drawer 1 Opened. evt’(write this line to a csv with headers)

 

Will you provide sample data for REG1_oposcashdrawer.log. Replace any personal data within the sample.

Well if your log file actually looks that way, meaning all those extra spaces, then they have to be checked as well.
So, extending you sample log a bit. You could do this via RegEx and the -Context switch of Get-Content.

Get-Content -Path 'E:\temp\REG1_oposcashdrawer.log'

# Results

i.e.

,'Rx pop drawer 1 from POS'

line 2

line 3

'Cash Drawer 1 Opened. evt'(write this line to a csv with headers)

line 4

line 5

line 6

line 7

'Cash Drawer 1 Opened. evt'(write this line to a csv with headers)

line 8

line 9

'Cash Drawer 1 Opened. evt'(write this line to a csv with headers)


# Read the log, Reversse check line for matching string and exclude a string
[RegEx]::Matches(((Get-Content -Path 'E:\temp\REG1_oposcashdrawer.log'  | 
Select-String -pattern 'Cash Drawer 1 Opened. evt' -Context 6,1) -notmatch 
'Rx pop drawer 1 from POS'),'Cash Drawer 1 Opened. evt').Value

#Resutls
Cash Drawer 1 Opened. evt
Cash Drawer 1 Opened. evt

Also, you never really need Write-Host to write to the screen, well, except for when you are using foreground / background colors.
Write-Output is the default and you don’t have to write that either, unless it’s your style. The below will do the same thing.

# Set a line separator / banner line
$BannerLine = ('#')*75

# Use the banner line
$BannerLine 
'Count Pattern for Total Drawer pops on REG1 and return corresponding lines'
$BannerLine 

# Results
###########################################################################
Count Pattern for Total Drawer pops on REG1 and return corresponding lines
###########################################################################

You could also do this, to make your string concat a bit less quote’y and those extra ‘+’. ;-}

$StoreNo = '24'
$date = (Get-Date).ToString('mmddyyyy')

$oposlogfile = "C:\Install\Script\CashDrawerLogExport\$($StoreNo)_$($date)_LineDetail_oposlog.csv"

# Results

$oposlogfile
C:\Install\Script\CashDrawerLogExport\24_05302019_LineDetail_oposlog.csv

Yet, it’s really a personal style this as well, or standards you may need to adhere to in your org.

Here is a snippet of the log file i am trying to parse.

as mentioned if the ‘Cash Drawer 1 Opened’ line occurs but the ‘Rx pop drawer’ line does not precede within 3-4 lines then i want to output that ‘Cash Drawer…’ Line.

 

The syntax of the line will always follow

[pre]

line = Rx pop drawer

line2 = device report current state

line3 = open drawer return

line4 = cash drawer 1 opened

line5 = status sent to client

line6 = cash drawer closed

line7 = status sent to client

[/pre]

The Rx force_getstate line is an error due to a hardware issue and will not always be there

[pre]

03/29/2019 01:59:54.801 [t2520] OposCashDrawer: <100004:Trace> cServerCtl::OnAcceptConnection() Server Socket Count = 3
03/29/2019 04:17:31.584 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 04:17:31.594 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 04:17:36.351 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 04:17:36.361 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 04:22:21.230 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 04:22:21.240 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 04:23:17.591 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 04:23:17.601 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:02:50.153 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 05:02:50.163 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:05:00.911 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 05:05:00.921 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:05:45.044 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 05:05:45.054 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:06:00.737 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 05:06:00.747 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:00:08.908 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:00:08.918 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:25.417 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 06:04:25.437 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:40.638 [t2520] OposCashDrawer: <100004:Trace> Rx pop drawer 1 from POS
03/29/2019 06:04:40.638 [t2520] OposCashDrawer: <100004:Trace> Device reported current state [Closed]
03/29/2019 06:04:40.658 [t2520] OposCashDrawer: <100004:Trace> OpenDrawer return = 0
03/29/2019 06:04:40.899 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:04:40.899 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:42.371 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 06:04:42.381 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:42.641 [t2520] OposCashDrawer: <100004:Trace> Rx ‘FORCE_GETSTATE’ from POS
03/29/2019 06:04:42.641 [t2520] OposCashDrawer: <100004:Trace> ForceGetState: Status sent to 1 clients
03/29/2019 06:08:10.620 [t2520] OposCashDrawer: <100004:Trace> Rx ‘FORCE_GETSTATE’ from POS
03/29/2019 06:08:10.620 [t2520] OposCashDrawer: <100004:Trace> ForceGetState: Status sent to 1 clients
03/29/2019 06:08:50.237 [t2520] OposCashDrawer: <100004:Trace> Rx pop drawer 1 from POS
03/29/2019 06:08:50.237 [t2520] OposCashDrawer: <100004:Trace> Device reported current state [Closed]
03/29/2019 06:08:50.237 [t2520] OposCashDrawer: <100004:Trace> OpenDrawer return = 0
03/29/2019 06:08:50.748 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:08:50.748 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:09:00.232 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 06:09:00.242 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:11:06.944 [t2520] OposCashDrawer: <100004:Trace> Rx ‘FORCE_GETSTATE’ from POS
03/29/2019 06:11:06.944 [t2520] OposCashDrawer: <100004:Trace> ForceGetState: Status sent to 1 clients
03/29/2019 06:12:29.192 [t2520] OposCashDrawer: <100004:Trace> Rx pop drawer 1 from POS
03/29/2019 06:12:29.192 [t2520] OposCashDrawer: <100004:Trace> Device reported current state [Closed]
03/29/2019 06:12:29.192 [t2520] OposCashDrawer: <100004:Trace> OpenDrawer return = 0
03/29/2019 06:12:29.553 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:12:29.553 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients

[/pre]

Please go back and edit your post again to fix the formatting of the code. Use the code tag button (pre) for it. Thanks

[quote quote=147953]Please go back and edit your post again to fix the formatting of the code. Use the code tag button (pre) for it. Thanks

[/quote]
Thanks, i did not even notice since

I’m pretty unsure if I got you right … try if this logic applies to your requirement:

Select-String -Path E:\temp\REG1_oposcashdrawer.log -Pattern  ‘Cash Drawer 1 Opened’ -Context 3 |
Where-Object {$_.Context.PreContext[0] -notmatch ‘Rx pop drawer’ } |
Select-Object -Property LineNumber,Line

BigelowCC35, thx for the update, but my response still applies. Just change the -context numbers to whatever you need. In your case 3,1.
If you want more of the line, just change that match string, or Or Olaf’s answer. They both do the same thing, capturing the string you are after based on your criteria. How much of that line you’d like is up to you.

@postanote

Can you give me a little detail on how context works in this aspect. I looked at the microdocs for it but was having a hard time understanding.

[quote quote=147960]I’m pretty unsure if I got you right … try if this logic applies to your requirement:

PowerShell
3 lines
<textarea class="ace_text-input" style="opacity: 0; height: 18px; width: 7px; left: 44px; top: 0px;" spellcheck="false" wrap="off"></textarea>
1
2
3
Select-String -Path E:\temp\REG1_oposcashdrawer.log -Pattern 'Cash Drawer 1 Opened' -Context 3 |
Where-Object {$_.Context.PreContext[0] -notmatch 'Rx pop drawer' } |
Select-Object -Property LineNumber,Line
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
[/quote]

This works perfectly however now i need to be able to append each line to a csv file under a header specified like

[pre]

“Store”,“REG”,“Keyed_Event_Details”

“9999”,“REG1”,"\line returned from SLS"

“9999”,“REG1”,"\line returned from SLS"

[/pre]

i am able to place the data alone in the file without effort but i seem to be doing something wrong when running it under a specific header

[pre]

$reg1file = “C:\install\script\cashdrawerlogexport\REG1_oposcashdrawer.log”
$oposlogfile = “C:\Install\Script\CashDrawerLogExport”+$StoreNo+"_"+$date+"LineDetail_oposlog.csv"
Add-Content -Path $oposlogfile -Value ‘“Store”,“REG”,“Keyed_Event_Details”’
if (Test-Path $reg1file) {
write-host “##########################################################################”
write-host “Count Pattern for Total Drawer pops on REG1 and return corresponding lines”
write-host “##########################################################################”
$reg1_content = select-string -Path $reg1file -pattern ‘Cash Drawer 1 Opened’ -Context 3,1 | Where-Object {$
.Context.PreContext[0] -notmatch ‘Rx pop drawer 1’ } | Select-Object -Property LineNumber,Line
}
#Export-Csv -Path $oposlogfile -InputObject $reg1_content -property Store,REG,Keyed_Event_details -Append
foreach ($line in $reg1_content){
$oposlog = @(
[pscustomobject]@{
Store = $StoreNo
REG = ‘REG1’
Keyed_Event_Details = $line.Line
}
)
}

$oposlog | export-csv -path $oposlogfile -append -NoTypeInformation[/pre]

This is what i have so far but this only prints the last line found to the csv file not each line found

Olaf, Thanks for the info it really helped me work through this. i have figured out after ripping my hair out that i simply had to put the export cmd into the foreach statement instead of outside. i.e.

[pre]

$reg1_content = select-string -Path $reg1file -pattern ‘Cash Drawer 1 Opened’ -Context 3,1 | Where-Object {$_.Context.PreContext[0] -notmatch ‘Rx pop drawer 1’ } | Select-Object -Property Line
}
foreach ($line in $reg1_content){
$oposlog = @(
[pscustomobject]@{
Store = $StoreNo
REG = ‘REG1’
Keyed_Event_Details = $line.line
}
$oposlog | export-csv -path $oposlogfile -append
)
}

[/pre]

those looking over this after the fact the $line.line is there because if i dont declare the .line it will return not only the data but the line number as well and that was just extraneous data i did not need.

This works perfectly now. I appreciate all the help you guys have given in the project of mine. It will certainly save us a big headache =)