Calcular la diferencia de tiempo entre las filas de eventos según los filtros

Un usuario Pregunto ✅

Duffkess

Hola,

Tengo la siguiente estructura de datos. (Monitoreo de Icinga2)

El ID de objeto representa algún objeto de TI, por ejemplo, un servidor

State_Time es el momento en el que se reconoció un evento

Current_State es el estado basado en este evento, por ejemplo, host activo, host inactivo.

Object_ID State_Time Estado
1 2018-05-02 09:00 0
1 2018-05-02 10:00 1
1 2018-05-02 11:00 0
2 2018-05-02 07:00 2
2 2018-05-02 10:30 3
3 2018-05-02 05:00 1
3 2018-05-02 06:00 0
3 2018-05-02 08:00 2

Quiero tener una nueva columna donde para cada evento se muestre la hora del evento anterior para este objeto.

Necesito esta columna porque quiero calcular cuánto tiempo estuvo vigente cada estado y luego calcular algunas «estadísticas de disponibilidad».

Ya probé con este ejemplo:

last_state_time = CALCULATE(MAX('icinga icinga_statehistory'[state_time]),filter(all('icinga icinga_statehistory'),  'icinga icinga_statehistory'[object_id] = earlier('icinga icinga_statehistory'[object_id]) && 'icinga icinga_statehistory'[state_time] > EARLIER('icinga icinga_statehistory'[state_time] ) ))

Pero siempre me da solo la última fila, no la última basada en la fila actual.

Y tampoco estoy seguro del rendimiento, creo que el conjunto de datos será de alrededor de 2,5 millones de filas, ¿crees que esto debería estar bien?

¡Gracias de antemano!

Duffkess

Hola @Duffkess,

Por favor intente esto:

last_state_time =
CALCULATE (
    MAX ( 'icinga icinga_statehistory'[state_time] ),
    FILTER (
        ALLEXCEPT (
            'icinga icinga_statehistory',
            'icinga icinga_statehistory'[Object_ID]
        ),
        'icinga icinga_statehistory'[state_time]
            < EARLIER ( 'icinga icinga_statehistory'[state_time] )
    )
)

1.PNG

Atentamente,

Yuliana Gu

Hola @Duffkess,

Por favor intente esto:

last_state_time =
CALCULATE (
    MAX ( 'icinga icinga_statehistory'[state_time] ),
    FILTER (
        ALLEXCEPT (
            'icinga icinga_statehistory',
            'icinga icinga_statehistory'[Object_ID]
        ),
        'icinga icinga_statehistory'[state_time]
            < EARLIER ( 'icinga icinga_statehistory'[state_time] )
    )
)

1.PNG

Atentamente,

Yuliana Gu

Lah123

En respuesta a v-yulgu-msft

Si intento esto, mi memoria se llena bastante rápido y Power BI finaliza la consulta. Tengo 1 M + filas y 16 GB de RAM. Hay otra manera de hacer esto?

Duffkess

En respuesta a Lah123

No sé si esto te ayuda, pero lo hice con otra solución:

Todas las noches, un script de PowerShell en el servidor de puerta de enlace de PowerBI calcula los datos necesarios.

Este script usa el conector MySql y yo uso el campo de notas para el nombre del cliente, por eso lo escribo.

Necesita cambiar la configuración para la conexión MySql y la variable $ Dir.

Function Query-MySQL ($query){

    $MySQLAdminUserName="user"
    $MySQLAdminPassword = 'passwd!'
    $MySQLDatabase="icinga"
    $MySQLHost="serverip"
    $ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase+";sslmode=none;Convert Zero Datetime=True"
    Try {
          [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
          $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
          $Connection.ConnectionString = $ConnectionString
          $Connection.Open()

          $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
          $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
          $DataSet = New-Object System.Data.DataSet
          $RecordCount = $dataAdapter.Fill($dataSet, "data")
          return $DataSet.Tables[0]
    }

    Catch {
        Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
    }

    Finally {
        $Connection.Close()
    }
}

$icinga_objects_query = "
    select object_id,name1,coalesce(name2,'Host') as name2,coalesce(icinga_hosts.notes, icinga_services.notes) as notes
    from icinga_objects
    left join icinga_hosts on icinga_hosts.host_object_id = icinga_objects.object_id
    left join icinga_services on icinga_services.service_object_id = icinga_objects.object_id
    where icinga_objects.is_active = 1
    and objecttype_id in (1,2)"

$icinga_objects = Query-MySQL $icinga_objects_query

$day = (get-date -Hour 0 -Minute 0 -Second 0 -Millisecond 0).AddDays(-1)
#$day = get-date -Hour 0 -Minute 0 -Second 0 -Millisecond 0 -Year 2018 -Month 9 -Day 16

$Dir = "D:PowerBISourcesIcinga"
$Path = $dir+$day.ToString("yyyy-MM-dd")+".csv"
$streamWriter = [System.IO.StreamWriter] $path

foreach($icinga_object in $icinga_objects){
    $events = @()
    <#  types: 0 = state history
               state: state of service or host
               1 = acknowledge
               state: sticky 1, no sticky 0
               2 = downtime start
               3 = downtime end
    #>
    
    $state_before_query = "
        select state_time,state,output from 
        icinga_statehistory 
        where object_id = " + $icinga_object.object_id + "
        and state_type = 1
        and state_time < '" + $day.ToString("yyyy-MM-dd") + "'
        order by statehistory_id desc 
        limit 1"

    $state_before = Query-MySQL $state_before_query

    $states_query = "select state_time,state,output from icinga_statehistory
        where object_id = "+$icinga_object.object_id+"
        and state_type = 1
        and state_time > '" + $day.ToString("yyyy-MM-dd") + "'
        and state_time < '" + $day.AddDays(1).ToString("yyyy-MM-dd") + "'
        order by statehistory_id asc"

    $states = Query-MySQL $states_query

    foreach($state in $states){
        $event = New-Object PSCustomObject
        Add-Member -InputObject $event -type NoteProperty -Name "TimeStamp" -Value $state.state_time
        Add-Member -InputObject $event -type NoteProperty -Name "Type" -Value 0
        Add-Member -InputObject $event -type NoteProperty -Name "State" -Value $state.state
        Add-Member -InputObject $event -type NoteProperty -Name "Info" -Value $state.output
        $events += $event
    }

    $ack_before_query = "select entry_time,is_sticky,comment_data from icinga_acknowledgements
        where object_id = " + $icinga_object.object_id + "
        and entry_time < '" + $day.ToString("yyyy-MM-dd") + "'
        order by acknowledgement_id desc
        limit 1"

    $ack_before = Query-MySQL $ack_before_query

    $acks_query = "select entry_time,is_sticky,comment_data from icinga_acknowledgements
        where object_id = " + $icinga_object.object_id + "
        and entry_time > '" + $day.ToString("yyyy-MM-dd") + "'
        and entry_time < '" + $day.AddDays(1).ToString("yyyy-MM-dd") + "'"

    $acks = Query-MySQL $acks_query

    foreach($ack in $acks){
        $event = New-Object PSCustomObject
        Add-Member -InputObject $event -type NoteProperty -Name "TimeStamp" -Value $ack.entry_time
        Add-Member -InputObject $event -type NoteProperty -Name "Type" -Value 1
        Add-Member -InputObject $event -type NoteProperty -Name "State" -Value $ack.is_sticky
        Add-Member -InputObject $event -type NoteProperty -Name "Info" -Value $ack.comment_data
        $events += $event
    }

    $downtimes_query = "select scheduled_start_time,scheduled_end_time,comment_data from icinga_downtimehistory
	where object_id = " + $icinga_object.object_id + "
    and icinga_downtimehistory.scheduled_start_time < '" + $day.AddDays(1).ToString("yyyy-MM-dd") + "'
    and icinga_downtimehistory.scheduled_end_time > '" + $day.ToString("yyyy-MM-dd") + "'"

    $downtimes = Query-MySQL $downtimes_query
    
    foreach($downtime in $downtimes){
        $event = New-Object PSCustomObject
        Add-Member -InputObject $event -type NoteProperty -Name "TimeStamp" -Value $downtime.scheduled_start_time
        Add-Member -InputObject $event -type NoteProperty -Name "Type" -Value 2
        Add-Member -InputObject $event -type NoteProperty -Name "State" -Value 1
        Add-Member -InputObject $event -type NoteProperty -Name "Info" -Value $downtime.comment_data
        $events += $event

        $event = New-Object PSCustomObject
        Add-Member -InputObject $event -type NoteProperty -Name "TimeStamp" -Value $downtime.scheduled_end_time
        Add-Member -InputObject $event -type NoteProperty -Name "Type" -Value 3
        Add-Member -InputObject $event -type NoteProperty -Name "State" -Value 0
        Add-Member -InputObject $event -type NoteProperty -Name "Info" -Value "downtime ended"
        $events += $event
    }

    if($state_before.state -eq 0){
        $current_ack = 0
    }else{
        if(@($ack_before).Count -gt 0){
            if($ack_before.is_sticky -eq 1){
        
                $states_after_before_ack_query = "select count(*) count from icinga_statehistory
                where object_id = "+$icinga_object.object_id+"
                and state_type = 1
                and state_time > '" + $ack_before.entry_time.ToString("yyyy-MM-dd") + "'
                and state_time < '" + $day.ToString("yyyy-MM-dd") + "'
                and state = 0
                order by statehistory_id asc"

                $states_after_before_ack = Query-MySQL $states_after_before_ack_query
                
                if($states_after_before_ack.count -gt 0){
                    $current_ack = 0
                }else{
                    $current_ack = 2
                }
            }else{
            
                $states_after_before_ack_query = "select count(*) as count from icinga_statehistory
                where object_id = "+$icinga_object.object_id+"
                and state_type = 1
                and state_time > '" + $ack_before.entry_time.ToString("yyyy-MM-dd HH:mm:ss") + "'
                and state_time < '" + $day.ToString("yyyy-MM-dd HH:mm:ss") + "'
                order by statehistory_id asc"

                $states_after_before_ack = Query-MySQL $states_after_before_ack_query
                
                if($states_after_before_ack.count -gt 0){
                    $current_ack = 0
                }else{
                    $current_ack = 1
                }
            }
        }else{
            $current_ack = 0
        }
    }

    $current_state = $state_before.state

    $current_downtime = ($downtimes | where-object {$_.scheduled_start_time -lt $day}).Count

    $events = $events | Sort-Object -Property TimeStamp | Where{ $_.TimeStamp -lt $day.AddDays(1) -and $_.TimeStamp -gt $day}
    if(@($events).Count -gt 0){
        $line = ($day.toString("yyyy-MM-dd HH:mm:ss"),$events[0].TimeStamp.toString("yyyy-MM-dd HH:mm:ss"), $icinga_object.notes, $icinga_object.name1, $icinga_object.name2, $current_state, $current_ack, $current_downtime) -join ";"
    
        $streamWriter.WriteLine($line)

        for($i = 0; $i -lt @($events).Count; $i++){
          
            switch($events[$i].Type){
                0{
                            
                    if($current_ack -eq 1 -and $current_state -ne $events[$i].State){
                        $current_ack = 0
                    }

                    if($current_ack -eq 2 -and $events[$i].State -eq 0){
                        $current_ack = 0
                    }

                    $current_state = $events[$i].State
                }
                1{
                    switch($events[$i].State){
                        0{$current_ack = 1}
                        1{$current_ack = 2}
                    }
                }
                2{
                    $current_downtime++
                }
                3{
                    $current_downtime--
                }
            }
            switch($i){
                (@($events).count-1){
                    $line = ($events[$i].TimeStamp.toString("yyyy-MM-dd HH:mm:ss"), $day.AddDays(1).toString("yyyy-MM-dd HH:mm:ss"), $icinga_object.notes,$icinga_object.name1, $icinga_object.name2, $current_state, $current_ack, $current_downtime) -join ";"
                }
                default {
                    $line = ($events[$i].TimeStamp.toString("yyyy-MM-dd HH:mm:ss"), $events[$i+1].TimeStamp.toString("yyyy-MM-dd HH:mm:ss"), $icinga_object.notes, $icinga_object.name1, $icinga_object.name2, $current_state, $current_ack, $current_downtime) -join ";"
                } 
            }
            $streamWriter.WriteLine($line)
        }
    
    }else{
        $line = ($day.toString("yyyy-MM-dd HH:mm:ss"),$day.AddDays(1).toString("yyyy-MM-dd HH:mm:ss"), $icinga_object.notes, $icinga_object.name1, $icinga_object.name2, $current_state, $current_ack, $current_downtime) -join ";"
        $streamWriter.WriteLine($line)
    }
}

$streamWriter.Close()

Give it a try, if you want.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *