Building objects from Text log

This topic contains 13 replies, has 4 voices, and was last updated by Profile photo of Wei-Yen Tan Wei-Yen Tan 4 months ago.

Viewing 14 posts - 1 through 14 (of 14 total)
  • Author
    Posts
  • #39083
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    I got asked to convert text files with certain values in them and turn them into a table

    The following is an output of a SQL Log for fragmentation.

    I see that the first 'paragraph' is information based on the server the second is the database and the third is based on the tables and details of the fragmentation.

    There will be other table/fragmentation information.

    What I would like is to capture the SQL server/version the database that it is under and the table fragmention (details like [GEN_ACCOUNT] (INDEX), [CTSO_Landing] [GEN_ACCOUNT] (database),

    There will be a number of database and tables listed in the log.

    By the multidimensional type of the data I think XML will be the best way to do it. That way I can create a report out of it.

    I don't expect anyone to write the script I have planned (it is fun writing it). Curtis kindly helped me in a previous post on how to use regular expression on single text and extract that...although I know that regex matches line by line but I think I need to get Powershell to process the text as a paragraph? Is there a way to get it to process the paragraph as a group one by one and search for regex that way?

    For example the first paragraph is information about the server. My plan is to use regex to determine if the paragraph is server information and if it is then it will extract the values for the server as objects down the line and then would be added to a collection.

    I would use a foreach loop to process each information.

    The guidance that I need help with is how to get powershell to analyse only that block of text

    Below is an excerpt of the text log. (Names have been changed).

    Date and time: 2016-05-07 04:30:00
    Server: CTSO_SVR\DATAHUB
    Version: 12.0.4213.0
    Edition: Enterprise Edition: Core-based
    Procedure: [dbadb].[dbo].[IndexOptimize]
    Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
    Source: http://ola.hallengren.com
    
    Date and time: 2016-05-07 04:30:00
    Database: [CTSO_Landing]
    Status: ONLINE
    Standby: No
    Updateability: READ_WRITE
    User access: MULTI_USER
    Is accessible: Yes
    Recovery model: FULL
    Availability group:DATAHUB-AG
    Availability group role: PRIMARY
    
    Date and time: 2016-05-07 04:30:01
    Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
    Outcome: Succeeded
    Duration: 00:00:08
    Date and time: 2016-05-07 04:30:09
    
    Date and time: 2016-05-07 04:30:10
    Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
    Outcome: Succeeded
    Duration: 00:00:04
    Date and time: 2016-05-07 04:30:14
    
    

    I am aware that it might be possible to get into SQL Server and get the objects that way but this may prove to be a good exercise. Thank so much for the guidance.

    #39092
    Profile photo of AK
    AK
    Participant

    Ola has addressed this with a switch:

    @logtotable='Y'

    It would write directly to a table, so you don't have to parse anything.

    #39094
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    Hi, Ak thanks for the reply I am not quite sure what you mean. Could you please elaborate ? Thanks 🙂

    EDIT: thanks for that. I did a bit of digging and see what you meant. I will let the other team know about this but I still think it would be a good idea to know how to parse text to objects for other log files. So I still pose the question out there about the powershell handling paragraph at a time for regex? Thanks. 🙂

    #39095
    Profile photo of AK
    AK
    Participant

    When I added a URL to his documentation page my reply disappeared without an error. I'm glad you found it. I'll let someone else help you with a question about paragraphs.

    #39096
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    @wei-yen-tan
    If the records we all the same type, then you could parse the log very quickly using the ConvertFrom-String cmdlet. I was unsuccessful using this cmdlet with this dataset since there are multiple record types. Maybe someone more advanced with ConvertFrom-String could offer some input.

    With that said, below is an example of parsing the data line by line. It is a quick example, so there is some more handling an decisions that need to be made, like what you want to do with the multiple date time stamps. Right now It just keeps the last one instead of all of them.

    Basically each line is prefixed with a field designation, so we use that designation with Switch to determine what to do when that field is found. We've designated "Server" at the Start of record, so when Server is found, the existing record is output and a new record is started. This, of course, assumes that the logs are sequential and no simultaneous processes are run.

    cls
    $data = @'
    Date and time: 2016-05-07 04:30:00
    Server: CTSO_SVR\DATAHUB
    Version: 12.0.4213.0
    Edition: Enterprise Edition: Core-based
    Procedure: [dbadb].[dbo].[IndexOptimize]
    Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
    Source: http://ola.hallengren.com
    
    Date and time: 2016-05-07 04:30:00
    Database: [CTSO_Landing]
    Status: ONLINE
    Standby: No
    Updateability: READ_WRITE
    User access: MULTI_USER
    Is accessible: Yes
    Recovery model: FULL
    Availability group:DATAHUB-AG
    Availability group role: PRIMARY
    
    Date and time: 2016-05-07 04:30:01
    Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
    Outcome: Succeeded
    Duration: 00:00:08
    Date and time: 2016-05-07 04:30:09
    
    Date and time: 2016-05-07 04:30:10
    Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
    Outcome: Succeeded
    Duration: 00:00:04
    Date and time: 2016-05-07 04:30:14
    
    Date and time: 2016-05-07 04:30:00
    Server: CTSO_SVR\DATAHUB
    Version: 12.0.4213.0
    Edition: Enterprise Edition: Core-based
    Procedure: [dbadb].[dbo].[IndexOptimize]
    Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
    Source: http://ola.hallengren.com
    
    Date and time: 2016-05-07 04:30:00
    Database: [CTSO_Landing]
    Status: ONLINE
    Standby: No
    Updateability: READ_WRITE
    User access: MULTI_USER
    Is accessible: Yes
    Recovery model: FULL
    Availability group:DATAHUB-AG
    Availability group role: PRIMARY
    
    Date and time: 2016-05-07 04:30:01
    Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
    Outcome: Succeeded
    Duration: 00:00:08
    Date and time: 2016-05-07 04:30:09
    
    Date and time: 2016-05-07 04:30:10
    Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
    Outcome: Succeeded
    Duration: 00:00:04
    Date and time: 2016-05-07 04:30:14
    '@ -split "`r`n"
    
    $data |
    ForEach-Object {
        If ($_) {
            Switch ($_.substring(0,$_.indexof(":"))) {
                'Server' {
                    If ($psrecord) {
                        $psrecord
                    }
                    $psrecord = [pscustomobject]@{
                        'Server' = "$input".substring("$input".indexof(":") + 2)
                        'Version' = ""
                        'Date and time' = ""
                        'Database' = ""
                        'Command' = ""
                        'Tables' = ""
                    }
                }
                'Version' {$psrecord.Version = "$input".substring("$input".indexof(":") + 2)}
                'Date and time' {
                    If ($psrecord) {
                        $psrecord.'Date and time' = "$input".substring("$input".indexof(":") + 2)
                    }
                }
                'Database' {$psrecord.Database = "$input".substring("$input".indexof(":") + 2)}
                'Command' {
                    $psrecord.Command = "$input".substring("$input".indexof(":") + 2)
                    $psrecord.Tables = ([regex]::Matches("$input","\[([^dbo].*?)]") | ForEach-Object {$_.Groups[1].Value}) -join ","
                }
            }
        }
    }
    $psrecord
    Remove-Variable psrecord
    

    Results:

    Server        : CTSO_SVR\DATAHUB
    Version       : 12.0.4213.0
    Date and time : 2016-05-07 04:30:00
    Database      : [CTSO_Landing]
    Command       : ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Tables        : accout_idx1,CTSO_Landing,GEN_ACCOUNT
    
    Server        : CTSO_SVR\DATAHUB
    Version       : 12.0.4213.0
    Date and time : 2016-05-07 04:30:14
    Database      : [CTSO_Landing]
    Command       : ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Tables        : accout_idx1,CTSO_Landing,GEN_ACCOUNT
    
    #39099
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    Hiya Curtis. Thanks for the tip on this. I have decided to rework the structure a little bit.

    I saw that you were matching the value server and then getting the value after the semi colon.

    What I would like is to look for the word index and get the first enclosed bracket , in the above example it would get [account_idx]. I have used select-string to find the word command and then used the -context parameter to select the first 2 lines preceding it and the 3 lines after it.

    This has given me the paragraphs setting I want and then I can use Foreach to iterate through them I think.

    Thank you Curtis for giving me the idea. I now know I must make an effort to study regex too.

    #39100
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Hey @wei-yen-tan,
    For fun I wrote the following based on the sample dataset to parse each record type and convert it into a PowerShell Custom Object. You can then store and/or manipulate the objects as you would any other PowerShell object. Hope this helps, I sure enjoyed writing it!

    cls
    $data = @'
    Date and time: 2016-05-07 04:30:00
    Server: CTSO_SVR\DATAHUB
    Version: 12.0.4213.0
    Edition: Enterprise Edition: Core-based
    Procedure: [dbadb].[dbo].[IndexOptimize]
    Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
    Source: http://ola.hallengren.com
    
    Date and time: 2016-05-07 04:30:00
    Database: [CTSO_Landing]
    Status: ONLINE
    Standby: No
    Updateability: READ_WRITE
    User access: MULTI_USER
    Is accessible: Yes
    Recovery model: FULL
    Availability group:DATAHUB-AG
    Availability group role: PRIMARY
    
    Date and time: 2016-05-07 04:30:01
    Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
    Outcome: Succeeded
    Duration: 00:00:08
    Date and time: 2016-05-07 04:30:09
    
    Date and time: 2016-05-07 04:30:10
    Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
    Outcome: Succeeded
    Duration: 00:00:04
    Date and time: 2016-05-07 04:30:14
    '@ -split "`n"
    
    Function ParseRecord {
        Param($data, $type, $currentline)
        Switch ($type) {
            Server {
                    $RecordObject = [pscustomobject]@{
                        'Date and time' = ""
                        'Server' = ""
                        'Version' = ""
                        'Edition' = ""
                        'Procedure' = ""
                        'Parameters' = ""
                        'Source' = ""
                    } #[pscustomobject}
            } #Server
            Database {
                    $RecordObject = [pscustomobject]@{
                        'Date and time' = ""
                        'Database' = ""
                        'Status' = ""
                        'Standby' = ""
                        'Updateability' = ""
                        'User access' = ""
                        'Is accessible' = ""
                        'Recovery model' = ""
                        'Availability group' = ""
                        'Availability group role' = ""
                    } #[pscustomobject}
            } #Database
            Command {
                    $RecordObject = [pscustomobject]@{
                        'Start Date and time' = ""
                        'Command' = ""
                        'Comment' = ""
                        'Outcome' = ""
                        'Duration' = ""
                        'Stop Date and time' = ""
                    } #[pscustomobject}
            } #Command
        } #Switch
        While ($data[$currentline]) {
            $field = $data[$currentline].substring(0,$data[$currentline].indexof(":"))
            If ($field -eq 'Date and time' -AND $type -eq 'Command') {
                If ($RecordObject.'Start Date and time') {
                    $RecordObject.'Stop Date and time' = $data[$currentline].substring($data[$currentline].indexof(":") + 2)
                } Else {
                    $RecordObject.'Start Date and time' = $data[$currentline].substring($data[$currentline].indexof(":") + 2)
                } # If Else
            } Else {
                $RecordObject.$field = $data[$currentline].substring($data[$currentline].indexof(":") + 2)
            } #If Else
            $currentline++
        } #While
        Remove-Variable 'field'
        return @{Object = $RecordObject; lastline = $currentline}
    } #Function
    
    $lines = $data.Count - 1
    $currentline = 0
    While ($currentline -lt $lines) {
        $recordtype = $data[$currentline +1].substring(0,$data[$currentline +1].indexof(":"))
        $record = ParseRecord $data $recordtype $currentline
        $currentline = $record.lastline
    #    $record.Object
        $currentline++
    
        Switch ($recordtype) {
            Server {
                "Server: $($record.Object.Server)"
                "Version: $($record.Object.Version)"
            } #Server
            Database {
                "Database: $($record.Object.Database)"
            } #Database
            Command {
                "Extractions: $(([regex]::Matches($record.Object.Command,"\[([^dbo].*?)]") | ForEach-Object {$_.Groups[1].Value}) -join ",")"
            } #Command
        } #Switch
    } #While
    

    Results:

    Server: CTSO_SVR\DATAHUB
    Version: 12.0.4213.0
    Database: [CTSO_Landing]
    Extractions: GEN_ACCOUNT,CTSO_Landing,GEN_ACCOUNT
    Extractions: accout_idx1,CTSO_Landing,GEN_ACCOUNT
    
    #39101
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    Thanks Curtis! In the extractions can I refer to the accout_idx ,CTSO_Landing and gen_account individually?

    For example:

    accout_idx1,CTSO_Landing,GEN_ACCOUNT

    I would extract accout_idx out on its own as its an index.

    CTSO_Landing as a database and GEN_Account as a table.

    Thank you so much for your guidance with this. I can then start playing with it and build something referring to your examples... 🙂

    EDIT: Found out that I can do split on the extraction field, and then reference it that way.
    Thank you very much Curtis, this is going to be fun. 🙂

    #39102
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    Well, the extractions is just a join of multiple objects, so you can change that to not join them and just return the first instance instead like this:

    "Extraction: $(([regex]::Matches($record.Object.Command,"\[([^dbo].*?)]") | ForEach-Object {$_.Groups[1].Value})[0])"

    #39170
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    Hi Curtis,

    In your first example you have "$input" listed in your object properties but is not defined.

    Is $input the current line?

    I suppose if it is I could do something like this:

    $b =$input  | select-string -allmatches "\[([^dbo].*?)]" 
    		$psrecord = [pscustomobject]@{
    					'Index' ="$b.Matches.Captures.captures.groups[1].value"
    					'Date' = ""
    					'Database' = ""
    					'Table' = ""
    					'Page Count' = ""
    					'Fragmentation' = ""
    					'Outcome' =""
    				}
    
    
    #39217
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    $input is an automatic variable from the pipleline input. I am using this because I need to calculate based on the original data from the pipeline input, not the current $_

    For Example:

    $data = "Hi my name is: Steve"
    $data |
    ForEach-Object {
        Switch ($data.Substring($data.IndexOf(':')+2)) {
            Steve {"`$_ = $_"
                   "`$input = $input"
            }
        }
    }
    

    Results

    $_ = Steve
    $input = Hi my name is: Steve
    
    #39219
    Profile photo of Anthony Stringer
    Anthony Stringer
    Participant

    glad to see you already have your answer. in the interest of knowledge sharing, here is my attempt

    # input trimmed to save space
    $data = @'
    Date and time: 2016-05-07 04:30:00
    Database: [CTSO_Landing]
    Status: ONLINE
    Standby: No
    Updateability: READ_WRITE
    User access: MULTI_USER
    Is accessible: Yes
    Recovery model: FULL
    Availability group:DATAHUB-AG
    Availability group role: PRIMARY
    
    Date and time: 2016-05-07 04:30:01
    Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
    Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
    Outcome: Succeeded
    Duration: 00:00:08
    Date and time: 2016-05-07 04:30:09
    '@.Split("`n")
    
    $data = $data | % {if ($_ -match '^(?:\s+)?$') {'&'} else {$_}}
    
    $sections = ($data | Out-String).Split('&')
    
    $results = $(foreach ($item in $sections) {
        $item = $item.Split("`n") | ? {$_ -notmatch '^(?:\s+)?$'}
        $hash = @{}
        for ($i = 0; $i -lt $item.Count; $i++) {
            $name = $item[$i].substring(0, $item[$i].indexof(':')).trim()
            $value = $item[$i].substring($item[$i].indexof(':') + 1).trim()
            $count = 0
            $newname = $name
            while ($newname -in $hash.keys) {
                $count++
                $newname = "$name$count"
            }
            $hash.Add($newname, $value)
        }
        [pscustomobject]$hash
    })
    
    $results
    

    *edit: side-note – curtis, you may be unintentionally excluding some captures in your regex. if you only want to exclude [dbo] this may be an option (i think... I'm not great with lookahead / lookbehind)

    $test = '[btest][accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)'
    
    "Extraction: $(([regex]::Matches($test, "\[((?!dbo).*?)]") | ForEach-Object {$_.Groups[1].Value})[0])"
    
    #39222
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    Thank you very much Curtis,

    I have reworked it a bit and this is my final result:

    $data = get-content "E:\test\db.txt" 
    $data2 = $data -split "`r`n"
    
    [CmdletBinding()]
    $data2 | ForEach-Object {if ($_){
                    Switch ($_.substring(0,$_.indexof(":"))) {
                        'Command'{ 
                        if ($psrecord)
                        {
                            $psrecord
                        }
                            $b = $input | select-string -allmatches "\[([^dbo].*?)]"
                             
                	        $psrecord = [pscustomobject]@{
    					        'Index' = ($b.Matches[0].Groups[1].Value)
    					        'Date' = ""
    					        'Database' = ($b.Matches[1].Groups[1].Value)
    					        'Table' = ($b.Matches.Captures[2].Groups.value[1])
    					        'PageCount' = ""
    					        'Fragmentation' = ""
                                'Operation' = ""
    					        'Outcome' =""
    				        }
                        
                            $Rebuild = $input | select-string -allmatch "Rebuild"
                            if ($Rebuild -ne $null){
                                $psrecord.Operation = "Rebuild"
                            }
    
                           
                    }
                        'Comment'{
                                $b = $input -split ","
                                $psrecord.PageCount = [int]($b[7]).Substring(12) 
                                $psrecord.Fragmentation = [int]($test[8]).Substring(16)
    
                        }
                        'outcome'{
    
                                $psrecord.Outcome = "$input".substring("$input".indexof(":") + 2)
                        }
    
                        
                        'Date and Time' {
                              If ($psrecord) {
                        $psrecord.Date = ([datetime]"$input".substring("$input".indexof(":") + 2))
                                }
                        }
    
       
                }
    
            }
    }
    
    

    What I found is at times it creates errors at times like this:

    
    Cannot index into a null array.
    At E:\Test\test2.ps1:48 char:22
    +                         $psrecord = [pscustomobject]@{
    +                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : NullArray
     
    Exception calling "Substring" with "2" argument(s): "Length cannot be less than zero.
    Parameter name: length"
    At E:\Test\test2.ps1:39 char:30
    + $data3 | ForEach-Object {if ($_){
    +                              ~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : ArgumentOutOfRangeException
    

    On investigation I did some debugging and I have some new lines which I thought was removed the split "`n`r"?

    It also has some lines that have

    Msg 50000, xxxx
    Msg 9002,xxxx
    

    (x marking the other lines)

    #39708
    Profile photo of Wei-Yen Tan
    Wei-Yen Tan
    Participant

    Thank you very much Curtis for giving me the guidelines. I owe you a beer!

    I've managed to work around the problems and it is now working.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.