CODEWITHSUNDEEP
powershellsplitimport-csv
DavDav
DavDav

Reputation: 127

Powershell: Import-CSV , delimiter and split combined - how?

Objective: Traverse a directory containing sub-dirs with log files, using Powershell to list hostnames in a column in the logfiles in Syslog RFC 5424 IETF formatting. Output: List of hosts, their IP and timestamps of log lines

Format in the logfiles is: col1 TAB col2 TAB col3 TAB col4 TAB col5 TAB col6

col6 contains various items, separated by a single SPACE and I'm after the hostname (in the example: MyHost01).

So far below example gives me a lot of lines like this:

2020-12-14 16:16:13 User.Notice  10.100.210.60 1 2020-12-14T16:17:44.755522+00:00 MyHost01 - - - [NXLOG@14506 EventReceivedTime="2020-12-14 16:17:43" SourceModuleName="auditlog" SourceModule... 

# Example - yields timestamp, info, IP address and raw message
$filelist = Get-ChildItem -Recurse -Path D:\Logs -Include *.txt
foreach ($textfile in $filelist) {
    $filepath = $textfile.fullname
    Import-Csv $filepath -Delimiter "`t" -Header col1,col3,col4,col6 |  Format-Table col1,col3,col4,col6
}

To achieve my objective, I need to split col6 by SPACE. How can this be done within my script?

Upvotes: 0

Views: 1185

Answers (1)

zett42
zett42

Reputation: 27756

I cannot see the TABs in your sample data. Assuming the host name is the first sub string of column 6:

Import-Csv $filepath -Delimiter "`t" -Header col1,col3,col4,col6 | 
    Select-Object col1, col3, col4, @{ n = 'col6'; e = { ( $_.col6 -split ' ' )[0] } } |
    Format-Table

Select-Object is used to to pass through columns 1, 3 and 4. Column 6 is defined as a calculated property by using a hashtable, which is short form of:

@{ 
    name = 'col6'                                # Name of the output column 
    expression = { ( $_.col6 -split ' ' )[0] }   # Calculated value
}

In the expression the -split operator is used to split the original value of column 6 by space, which returns an array of sub strings. The first element of this array will be the value to use for column 6. Change the index [0] to something else if you need to extract another sub string.

Upvotes: 1

Related Questions