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.
