src/ValidateFix-AzLogAnalyticsTableSchemaColumnNames.ps1


Function ValidateFix-AzLogAnalyticsTableSchemaColumnNames
{
 <#
    .SYNOPSIS
    Validates the column names in the schema are valid according the requirement for LogAnalytics tables
    Fixes any issues by rebuild the source object
 
    .DESCRIPTION
    Checks for prohibited column names - and adds new column with <name>_ - and removes prohibited column name
    Checks for column name length is under 45 characters
    Checks for column names must not start with _ (underscore) - or contain " " (space) or . (period)
    In case of issues, an new source object is build
 
    .VERSION
    1.0
 
    .AUTHOR
    Morten Knudsen, Microsoft MVP - https://mortenknudsen.net
 
    .LINK
    https://github.com/KnudsenMorten/AzLogDcrIngestPS
 
    .PARAMETER Data
    This is the data array
 
    .INPUTS
    None. You cannot pipe objects
 
    .OUTPUTS
    Updated $DataVariable with valid column names
 
    .EXAMPLE
    #-------------------------------------------------------------------------------------------
    # Variables
    #-------------------------------------------------------------------------------------------
    $Verbose = $true # $true or $false
 
    #-------------------------------------------------------------------------------------------
    # Collecting data (in)
    #-------------------------------------------------------------------------------------------
    $DNSName = (Get-CimInstance win32_computersystem).DNSHostName +"." + (Get-CimInstance win32_computersystem).Domain
    $ComputerName = (Get-CimInstance win32_computersystem).DNSHostName
    [datetime]$CollectionTime = ( Get-date ([datetime]::Now.ToUniversalTime()) -format "yyyy-MM-ddTHH:mm:ssK" )
 
    $UserLoggedOnRaw = Get-Process -IncludeUserName -Name explorer | Select-Object UserName -Unique
    $UserLoggedOn = $UserLoggedOnRaw.UserName
 
    Write-Output "Get-Process is pretty slow .... take a cup coffee :-)"
    $DataVariable = Get-Process
 
    #-------------------------------------------------------------------------------------------
    # Preparing data structure
    #-------------------------------------------------------------------------------------------
    # convert CIM array to PSCustomObject and remove CIM class information
    $DataVariable = Convert-CimArrayToObjectFixStructure -data $DataVariable -Verbose:$Verbose
     
    # add CollectionTime to existing array
    $DataVariable = Add-CollectionTimeToAllEntriesInArray -Data $DataVariable -Verbose:$Verbose
 
    # add Computer & UserLoggedOn info to existing array
    $DataVariable = Add-ColumnDataToAllEntriesInArray -Data $DataVariable -Column1Name Computer -Column1Data $Env:ComputerName -Column2Name UserLoggedOn -Column2Data $UserLoggedOn -Verbose:$Verbose
 
    # adding prohibted columns to data - to demonstrate how it works
    $DataVariable = Add-ColumnDataToAllEntriesInArray -Data $DataVariable -Column1Name "Type" -Column1Data "MyDataType" -Verbose:$Verbose
    $DataVariable = Add-ColumnDataToAllEntriesInArray -Data $DataVariable -Column1Name "Id" -Column1Data "MyId" -Verbose:$Verbose
 
    # schema - before changes - we see columns named Type and Id (prohibited)
    Get-ObjectSchemaAsArray -Data $DataVariable -Verbose:$Verbose
 
    # Data before changes - we see columns named Type and Id (prohibited)
    $DataVariable[0]
 
    # Validating/fixing schema data structure of source data
    $DataVariable = ValidateFix-AzLogAnalyticsTableSchemaColumnNames -Data $DataVariable -Verbose:$verbose
 
    # schema - after changes - we see data was transferred to new columns (type_ and id_ - and the wrong columns (type, id) were removed
    Get-ObjectSchemaAsArray -Data $DataVariable -Verbose:$Verbose
 
    # Data after changes - we see data was transferred to new columns (type_ and id_ - and the wrong columns (type, id) were removed
    $DataVariable[0]
 
    #-------------------------------------------------------------------------------------------
    # Output
    #-------------------------------------------------------------------------------------------
    VERBOSE: Converting CIM array to Object & removing CIM class data in array .... please wait !
    VERBOSE: Adding CollectionTime to all entries in array .... please wait !
    VERBOSE: Adding columns to all entries in array .... please wait !
    VERBOSE: Adding columns to all entries in array .... please wait !
    VERBOSE: Adding columns to all entries in array .... please wait !
 
    VERBOSE: Validating schema structure of source data ... Please Wait !
    VERBOSE: ISSUE - Column name is prohibited [ Id ]
    VERBOSE: ISSUE - Column name is prohibited [ Type ]
    VERBOSE: ISSUE - Column name must start with character [ __NounName ]
    VERBOSE: ISSUE - Column name is prohibited [ Id ]
    VERBOSE: ISSUE - Column name is prohibited [ Type ]
    VERBOSE: ISSUE - Column name must start with character [ __NounName ]
    VERBOSE: Issues found .... fixing schema structure of source data ... Please Wait !
 
    name type
    ---- ----
    BasePriority int
    CollectionTime datetime
    Company dynamic
    Computer string
    Container dynamic
    CPU dynamic
    Description dynamic
    EnableRaisingEvents boolean
    ExitCode dynamic
    ExitTime dynamic
    FileVersion dynamic
    Handle int
    HandleCount int
    Handles int
    HasExited boolean
    Id string
    MachineName string
    MainModule dynamic
    MainWindowHandle int
    MainWindowTitle string
    MaxWorkingSet int
    MinWorkingSet int
    Modules dynamic
    Name string
    NonpagedSystemMemorySize int
    NonpagedSystemMemorySize64 int
    NPM int
    PagedMemorySize int
    PagedMemorySize64 int
    PagedSystemMemorySize int
    PagedSystemMemorySize64 int
    Path string
    PeakPagedMemorySize int
    PeakPagedMemorySize64 int
    PeakVirtualMemorySize int
    PeakVirtualMemorySize64 int
    PeakWorkingSet int
    PeakWorkingSet64 int
    PM int
    PriorityBoostEnabled boolean
    PriorityClass int
    PrivateMemorySize int
    PrivateMemorySize64 int
    PrivilegedProcessorTime dynamic
    ProcessName string
    ProcessorAffinity int
    Product dynamic
    ProductVersion dynamic
    Responding boolean
    SafeHandle dynamic
    SessionId int
    SI int
    Site dynamic
    StandardError dynamic
    StandardInput dynamic
    StandardOutput dynamic
    StartInfo dynamic
    StartTime datetime
    SynchronizingObject dynamic
    Threads dynamic
    TotalProcessorTime dynamic
    Type string
    UserLoggedOn string
    UserProcessorTime dynamic
    VirtualMemorySize int
    VirtualMemorySize64 int
    VM int
    WorkingSet int
    WorkingSet64 int
    WS int
    __NounName string
    AcrobatNotificationClient MyDataType
 
 
    BasePriority : 8
    CollectionTime : 12-03-2023 17:10:15
    Company :
    Computer : STRV-MOK-DT-02
    Container :
    CPU : 0,015625
    Description :
    EnableRaisingEvents : False
    ExitCode :
    ExitTime :
    FileVersion :
    Handle : 10044
    HandleCount : 377
    Handles : 377
    HasExited : False
    Id_ : MyId
    MachineName : .
    MainModule : @{ModuleName=AcrobatNotificationClient.exe; FileName=C:\Program Files\WindowsApps\AcrobatNotificationClient_
                                 1.0.4.0_x86__e1rzdqpraam7r\AcrobatNotificationClient.exe; BaseAddress=6225920; ModuleMemorySize=438272; Entr
                                 yPointAddress=6460140; FileVersionInfo=; Site=; Container=}
    MainWindowHandle : 0
    MainWindowTitle :
    MaxWorkingSet : 1413120
    MinWorkingSet : 204800
    Modules : {@{ModuleName=AcrobatNotificationClient.exe; FileName=C:\Program Files\WindowsApps\AcrobatNotificationClient
                                 _1.0.4.0_x86__e1rzdqpraam7r\AcrobatNotificationClient.exe; BaseAddress=6225920; ModuleMemorySize=438272; Ent
                                 ryPointAddress=6460140; FileVersionInfo=; Site=; Container=}, @{ModuleName=ntdll.dll; FileName=C:\WINDOWS\SY
                                 STEM32\ntdll.dll; BaseAddress=140715251924992; ModuleMemorySize=2179072; EntryPointAddress=0; FileVersionInf
                                 o=; Site=; Container=}, @{ModuleName=wow64.dll; FileName=C:\WINDOWS\System32\wow64.dll; BaseAddress=14071524
                                 5764608; ModuleMemorySize=356352; EntryPointAddress=140715245870880; FileVersionInfo=; Site=; Container=}, @
                                 {ModuleName=wow64base.dll; FileName=C:\WINDOWS\System32\wow64base.dll; BaseAddress=140715221450752; ModuleMe
                                 morySize=36864; EntryPointAddress=140715221454864; FileVersionInfo=; Site=; Container=}...}
    Name : AcrobatNotificationClient
    NonpagedSystemMemorySize : 23424
    NonpagedSystemMemorySize64 : 23424
    NPM : 23424
    PagedMemorySize : 10592256
    PagedMemorySize64 : 10592256
    PagedSystemMemorySize : 466384
    PagedSystemMemorySize64 : 466384
    Path : C:\Program Files\WindowsApps\AcrobatNotificationClient_1.0.4.0_x86__e1rzdqpraam7r\AcrobatNotificationClient.
                                 exe
    PeakPagedMemorySize : 11440128
    PeakPagedMemorySize64 : 11440128
    PeakVirtualMemorySize : 318820352
    PeakVirtualMemorySize64 : 318820352
    PeakWorkingSet : 39202816
    PeakWorkingSet64 : 39202816
    PM : 10592256
    PriorityBoostEnabled : True
    PriorityClass : 32
    PrivateMemorySize : 10592256
    PrivateMemorySize64 : 10592256
    PrivilegedProcessorTime : @{Ticks=156250; Days=0; Hours=0; Milliseconds=15; Minutes=0; Seconds=0; TotalDays=1,80844907407407E-07; Tota
                                 lHours=4,34027777777778E-06; TotalMilliseconds=15,625; TotalMinutes=0,00026041666666666666; TotalSeconds=0,0
                                 15625}
    ProcessName : AcrobatNotificationClient
    ProcessorAffinity : 65535
    Product :
    ProductVersion :
    Responding : True
    SafeHandle : @{IsInvalid=False; IsClosed=False}
    SessionId : 1
    SI : 1
    Site :
    StandardError :
    StandardInput :
    StandardOutput :
    StartInfo : @{Verb=; Arguments=; CreateNoWindow=False; EnvironmentVariables=System.Object[]; Environment=System.Object[]
                                 ; RedirectStandardInput=False; RedirectStandardOutput=False; RedirectStandardError=False; StandardErrorEncod
                                 ing=; StandardOutputEncoding=; UseShellExecute=True; Verbs=System.Object[]; UserName=; Password=; PasswordIn
                                 ClearText=; Domain=; LoadUserProfile=False; FileName=; WorkingDirectory=; ErrorDialog=False; ErrorDialogPare
                                 ntHandle=0; WindowStyle=0}
    StartTime : 08-03-2023 22:22:46
    SynchronizingObject :
    Threads : {@{BasePriority=8; CurrentPriority=8; Id=24524; PriorityBoostEnabled=True; PriorityLevel=0; PrivilegedProces
                                 sorTime=; StartAddress=140715252309904; StartTime=08-03-2023 22:22:46; ThreadState=5; TotalProcessorTime=; U
                                 serProcessorTime=; WaitReason=5; Site=; Container=}, @{BasePriority=8; CurrentPriority=9; Id=18836; Priority
                                 BoostEnabled=True; PriorityLevel=0; PrivilegedProcessorTime=; StartAddress=140715252309904; StartTime=08-03-
                                 2023 22:22:46; ThreadState=5; TotalProcessorTime=; UserProcessorTime=; WaitReason=5; Site=; Container=}, @{B
                                 asePriority=8; CurrentPriority=8; Id=18608; PriorityBoostEnabled=True; PriorityLevel=0; PrivilegedProcessorT
                                 ime=; StartAddress=140715252309904; StartTime=08-03-2023 22:22:46; ThreadState=5; TotalProcessorTime=; UserP
                                 rocessorTime=; WaitReason=5; Site=; Container=}, @{BasePriority=8; CurrentPriority=9; Id=18832; PriorityBoos
                                 tEnabled=True; PriorityLevel=0; PrivilegedProcessorTime=; StartAddress=140715252309904; StartTime=08-03-2023
                                  22:22:46; ThreadState=5; TotalProcessorTime=; UserProcessorTime=; WaitReason=5; Site=; Container=}...}
    TotalProcessorTime : @{Ticks=156250; Days=0; Hours=0; Milliseconds=15; Minutes=0; Seconds=0; TotalDays=1,80844907407407E-07; Tota
                                 lHours=4,34027777777778E-06; TotalMilliseconds=15,625; TotalMinutes=0,00026041666666666666; TotalSeconds=0,0
                                 15625}
    Type_ : MyDataType
    UserLoggedOn : 2LINKIT\mok
    UserProcessorTime : @{Ticks=0; Days=0; Hours=0; Milliseconds=0; Minutes=0; Seconds=0; TotalDays=0; TotalHours=0; TotalMillisecon
                                 ds=0; TotalMinutes=0; TotalSeconds=0}
    VirtualMemorySize : 289554432
    VirtualMemorySize64 : 289554432
    VM : 289554432
    WorkingSet : 6762496
    WorkingSet64 : 6762496
    WS : 6762496
    NounName :
 #>


    [CmdletBinding()]
    param(
            [Parameter(mandatory)]
                [Array]$Data
         )

    $ProhibitedColumnNames = @("_ResourceId","id","_ResourceId","_SubscriptionId","TenantId","Type","UniqueId","Title","Date")

    Write-Verbose " Validating schema structure of source data ... Please Wait !"

    #-----------------------------------------------------------------------
    # Initial check
    $IssuesFound = $false

        # loop through data
        ForEach ($Entry in $Data)
            {
                $ObjColumns = $Entry | Get-Member -MemberType NoteProperty

                ForEach ($Column in $ObjColumns)
                    {
                        # get column name
                        $ColumnName = $Column.Name

                        If ($ColumnName -in $ProhibitedColumnNames)   # prohibited column names
                            {
                                $IssuesFound = $true
                                Write-Verbose " ISSUE - Column name is prohibited [ $($ColumnName) ]"
                            }

                        ElseIf ($ColumnName -like "_*")   # remove any leading underscores - column in DCR/LA must start with a character
                            {
                                $IssuesFound = $true
                                Write-Verbose " ISSUE - Column name must start with character [ $($ColumnName) ]"
                            }
                        ElseIf ($ColumnName -like "*.*")   # includes . (period)
                            {
                                $IssuesFound = $true
                                Write-Verbose " ISSUE - Column name include . (period) - must be removed [ $($ColumnName) ]"
                            }
                        ElseIf ($ColumnName -like "* *")   # includes whitespace " "
                            {
                                $IssuesFound = $true
                                Write-Verbose " ISSUE - Column name include whitespace - must be removed [ $($ColumnName) ]"
                            }
                        ElseIf ($ColumnName.Length -gt 45)   # trim the length to maximum 45 characters
                            {
                                $IssuesFound = $true
                                Write-Verbose " ISSUE - Column length is greater than 45 characters (trimming column name is neccessary) [ $($ColumnName) ]"
                            }
                    }
            }

    If ($IssuesFound)
        {
            Write-Verbose " Issues found .... fixing schema structure of source data ... Please Wait !"

            $DataCount  = ($Data | Measure-Object).Count

            $DataVariableQA = @()

            $Data | ForEach-Object -Begin  {
                    $i = 0
            } -Process {

                    # get column names
                    $ObjColumns = $_ | Get-Member -MemberType NoteProperty

                    ForEach ($Column in $ObjColumns)
                        {
                            # get column name
                            $ColumnName = $Column.Name

                            If ($ColumnName -in $ProhibitedColumnNames)   # phohibited column names
                                {
                                    $UpdColumn  = $ColumnName + "_"
                                    $ColumnData = $_.$ColumnName
                                    $_ | Add-Member -MemberType NoteProperty -Name $UpdColumn -Value $ColumnData -Force
                                    $_.PSObject.Properties.Remove($ColumnName)
                                }
                            ElseIf ($ColumnName -like "*.*")   # remove any . (period)
                                {
                                    $UpdColumn = $ColumnName.Replace(".","")
                                    $ColumnData = $Entry.$Column
                                    $_ | Add-Member -MemberType NoteProperty -Name $UpdColumn -Value $ColumnData -Force
                                    $_.PSObject.Properties.Remove($ColumnName)
                                }
                            ElseIf ($ColumnName -like "_*")   # remove any leading underscores - column in DCR/LA must start with a character
                                {
                                    $UpdColumn = $ColumnName.TrimStart("_")
                                    $ColumnData = $Entry.$Column
                                    $_ | Add-Member -MemberType NoteProperty -Name $UpdColumn -Value $ColumnData -Force
                                    $_.PSObject.Properties.Remove($ColumnName)
                                }
                            ElseIf ($ColumnName -like "* *")   # remove any whitespaces
                                {
                                    $UpdColumn = $ColumnName.TrimStart()
                                    $ColumnData = $Entry.$Column
                                    $_ | Add-Member -MemberType NoteProperty -Name $UpdColumn -Value $ColumnData -Force
                                    $_.PSObject.Properties.Remove($ColumnName)
                                }
                            ElseIf ($ColumnName.Length -gt 45)   # trim the length to maximum 45 characters
                                {
                                    $UpdColumn = $ColumnName.Substring(0,45)
                                    $ColumnData = $_.$Column
                                    $_ | Add-Member -MemberType NoteProperty -Name $UpdColumn -Value $ColumnData -Force
                                    $_.PSObject.Properties.Remove($ColumnName)
                                }
                            Else    # write column name and data (OK)
                                {
                                    $ColumnData = $_.$ColumnName
                                    $_ | Add-Member -MemberType NoteProperty -Name $ColumnName -Value $ColumnData -Force
                                }
                        }
                    $DataVariableQA += $_

                    # Increment the $i counter variable which is used to create the progress bar.
                    $i = $i+1

                    # Determine the completion percentage
                    $Completed = ($i/$DataCount) * 100
                    Write-Progress -Activity "Validating/fixing schema structure of source object" -Status "Progress:" -PercentComplete $Completed
            } -End {
                $Data = $DataVariableQA
                Write-Progress -Activity "Validating/fixing schema structure of source object" -Status "Ready" -Completed
            }
        }
    Else
        {
            Write-Verbose " SUCCESS - No issues found in schema structure"
        }
    Return [array]$Data
}