Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculating time difference between event rows based on filters

Hi,   I have the following data structure. (Monitoring Icinga2) Object ID represents some IT Object, a Server for example State_Time is the time where an event was recognized Current_State is th...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please try this:

    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] )
    )
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Please try this:

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] )
    )
)

 

Best regards,

Yuliana Gu

Lah123's avatar
Lah123
Frequent Visitor
8 years ago

If I try this, my memory fills up pretty quick and Power BI terminates the query. I have 1M+ rows and 16gb of ram. Is there another way to do this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I dont know if this helps you, but I did it with another workaround:

     

    Every night a PowerShell script on the PowerBI gateway server is calculating the needed data.

     

    This script uses the MySql connector and I use the notes field for the customer name, that's why I write it out.

     

    You need to change the Settings for the MySql connecntion and the $Dir variable.

     

    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:\PowerBI\Sources\Icinga\"
$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.