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.