How to get changed kpi/service status? (ITSI)

[sboogaar]

To see which alerts are new Im trying to generate the following table:

---

KPI | Service | alert_level | alert_level_previous | entity_title

---

Where alert_level_previous is the last alert_level for each (KPI, service, entity_title) combination
So if in the past a KPI was normal and now it is critical the alert_level should be critical and the alert_level_previous should be normal
I noticed there are duplicate entries (ALL fields are the same even the timestamp) in itsi_summary
My current non working query:

index=itsi_summary 
| search alert_level >= 4 AND NOT is_entity_in_maintenance=1 AND NOT is_service_in_maintenance=1 
| streamstats window=2 latest(alert_level) as alert_level_previous by kpi, host, itsi_service_id, entity_title 
| streamstats window=2 latest(alert_value) as alert_value_previous by kpi, host, itsi_service_id, entity_title 
| join type=inner itsi_service_id 
    [| search (index=itsi_summary source=service_mapping earliest=-2h latest=now) 
    | dedup itsi_service_id 
    | table itsi_service_id itsi_service ] 
| eval alert_new = if(alert_level != alert_level_previous, "yes", "no") 
| table kpi, itsi_service, alert_level, alert_level_previous, host,alert_new, alert_value, alert_value_previous _time, entity_title

If anyone can help me in the right direction it would be very nice

[sboogaar]

I ended up with the following query:

index=itsi_summary 
| fields kpi, alert_level, alert_value, entity_title, _time, host, message, alert_severity, itsi_service_id, itsi_kpi_id 
| dedup _time, itsi_service_id, itsi_kpi_id, entity_title 
| reverse 
| streamstats window=1 current=false global=false 
    latest(alert_level) as alert_level_previous, 
    latest(alert_value) as alert_value_previous, 
    latest(_time) as called_last_time 
    latest(alert_severity) as alert_severity_previous 
    by kpi, itsi_service_id, entity_title 
| reverse 
| join type=inner itsi_service_id 
    [| search (index=itsi_summary source=service_mapping ) 
    | fields itsi_service_id, itsi_service 
    | dedup itsi_service_id 
    | table itsi_service_id itsi_service ] 
| where _time > time() -120 AND alert_level > 4 
| eval calculated_last_time = strftime(called_last_time, "%d %H:%M:%S") 
| eval alert_new = if(alert_level!=alert_level_previous AND alert_level > 4, "1", "0") 
| eval up = alert_level - alert_level_previous 
| stats count by alert_severity, alert_severity_previous, itsi_service, kpi, entity_title, alert_new, alert_value, alert_value_previous, _time,calculated_last_time, host, alert_level, up 
| dedup kpi, itsi_service, entity_title 
| table alert_severity, alert_severity_previous, itsi_service, kpi, entity_title, alert_new, alert_value, alert_value_previous, _time,calculated_last_time, host, alert_level, up 
| sort -alert_new, - alert_level 
| eval alert_email = replace(alert_email, ",", ", ")