Automate log file using powershell

Question

Hi,

I do have a log file that has some content and insert the the data into SQL DB
All I need is to read the log file and fetch these details.

1. Date and Time : Need date and time as different columns
2. Message Type : INFO or ERROR what ever it is
3. Message : File Moved
4. SFN : Filename.txt
5. SAT : \abc\a\b\c\Filename.txt
6. FSize : 99223

2022-02-08 14:06:15 , Process1, INFO , File Moved , SFN - Filename.txt , Source - \abc\a\b\c\Filename.txt , SAT - 2022/02/08 08:12:06 , Dest - C:\Users\a\File.txt , FSize - 99223 B ,
2022-02-08 14:06:15 , Process1, INFO , File Moved , SFN - Filename1.txt , Source - \abc\a\b\c\Filename1.txt , SAT - 2022/02/08 08:15:06 , Dest - C:\Users\a\File1.txt , FSize - 99023 B ,
2022-02-08 14:06:15 , Process2, INFO , file does not exist: \abc\a\b\file.csv,
2022-02-08 14:06:15 , Process1, ERROR , File Moved , SFN - Filename2.txt , Source - \abc\a\b\c\Filename2.txt , SAT - 2022/02/08 08:12:06 , Dest - C:\Users\a\File2.txt , FSize - 99123 B ,

Script that I have created to read and fetch data that have conditions where "Messagetype = INFO"

$header = "Date", "MessageType","Message","SFN","SAT","FSize"
$log = Import-csv -Delimiter ',' -Header $header -path C:\Users\abc\File.log
$log | Where-Object {$_.MessageType = 'INFO'}
$log

So please help me in getting the data with Date and time as different columns, MessageType, SFN (not empty), SAT (not empty), FSize

Answer 1

Hi, I'm a little confused on your heading mapping as in your list (numbered 1-6) which is what I'm presuming your desired outcome is, SFN looks to be what the log file refers to as 'source'. The code here provides the same sort of output as you've listed (1-6) but I don't know what SAT stands for so I've mapped SAT to be the Source from the logging. If this isn't correct you might need to adjust accordingly.

$header = "Date", "Process", "MessageType", "Message", "SFN", "Source", "SAT","Dest","FSize"
$log = Import-csv -Delimiter ',' -Header $header -path C:\Users\abc\File.log
$log = $log | Select @{Name = 'Date';  Expression = {$_.Date.Split("")[0]}}, @{Name = 'Time';  Expression = {$_.Date.Split("")[1]}}, @{Name = 'MessageType';  Expression = {$_.MessageType.Trim()}}, @{Name = 'Message';  Expression = {$_.Message}},@{Name = 'SFN';  Expression = {$_.SFN.Replace("SFN - ","")}},@{Name = 'SAT';  Expression = {$_.Source.Replace("Source - ","")}},@{Name = 'FSize';  Expression = {$_.FSize.Replace("FSize - ","").Replace("B","")}}
$log | Where-Object {$_.MessageType -eq 'INFO'}

I've used calculated expressions to split the date/time into two columns and to tidy up some of the text such as on 'MessageType' the data in message actually has leading/trailing spaces so the Where-Object wouldn't work. Also not sure if the logging is right as it seem the 3rd entry is showing as a type 'INFO' tho its an error message and the 4th is showing a type 'ERROR' tho it looks the same as the 1st and 2nd lines.

Answer 2

You were missing some headers and in Powershell the equal sign is for assignment. For comparison you have to use switches like -eq'.

This should get you going.

cls  
$header = "Date", "Process", "MessageType","Message","SFN","Source", "SAT","Dest","FSize"  
$log = Import-csv -Delimiter ',' -Header $header -path C:\temp\log.txt  
""  
"All entries"  
$log | Format-Table -Property *  
  
""  
"Info records"  
$Info = $log | Where-Object  {$_.MessageType.trim() -eq 'INFO'}  
$info | Format-Table -Property Date, MessageType, Message, SFN  
  
""  
"INFO entries where SFN contains data."  
  
$Info2 = $info | Where-Object  {$_.SFN -ne ''}  
$info2 | Format-Table -Property Date, MessageType, Message, SFN  
  
""  
"Remove labels from data."  
$clean = $info2 | foreach {  
       [PSCustomObject]@{  
                 Date        = $_.Date  
                 MessageType = $_.MessageType  
                 Message     = $_.Message   
                 SFN         = $_.SFN.replace("SFN - ","")  
                 FSize       = $_.FSize.replace("FSize - ","").replace("B","").trim()  
            }  
}   
$clean | Format-Table -Property *  

Here is the output that it produced for me.

All entries  
  
Date                 Process  MessageType Message                                 SFN                  Source                              SAT     
----                 -------  ----------- -------                                 ---                  ------                              ---     
2022-02-08 14:06:15  Process1 INFO        File Moved                              SFN - Filename.txt   Source - \\abc\a\b\c\Filename.txt   SAT...  
2022-02-08 14:06:15  Process1 INFO        File Moved                              SFN - Filename1.txt  Source - \\abc\a\b\c\Filename1.txt  SAT...  
2022-02-08 14:06:15  Process2 INFO        file does not exist: \\abc\a\b\file.csv                                                                  
2022-02-08 14:06:15  Process1 ERROR       File Moved                              SFN - Filename2.txt  Source - \\abc\a\b\c\Filename2.txt  SAT...  
  
  
  
Info records  
  
Date                 MessageType Message                                 SFN                   
----                 ----------- -------                                 ---                   
2022-02-08 14:06:15  INFO        File Moved                              SFN - Filename.txt    
2022-02-08 14:06:15  INFO        File Moved                              SFN - Filename1.txt   
2022-02-08 14:06:15  INFO        file does not exist: \\abc\a\b\file.csv                       
  
  
  
INFO entries where SFN contains data.  
  
Date                 MessageType Message     SFN                   
----                 ----------- -------     ---                   
2022-02-08 14:06:15  INFO        File Moved  SFN - Filename.txt    
2022-02-08 14:06:15  INFO        File Moved  SFN - Filename1.txt   
  
  
  
Remove labels from data.  
  
Date                 MessageType Message     SFN            FSize  
----                 ----------- -------     ---            -----  
2022-02-08 14:06:15  INFO        File Moved  Filename.txt   99223  
2022-02-08 14:06:15  INFO        File Moved  Filename1.txt  99023