Question:

What is the actual connection state of each machine/Luna?

Solution:

To answer this question with an analytics query a first/last aggregation is required which is actually not available on the Senseforce plattform. 

Therefore we will solve this with a short R-script. In this script we will filter for the last state message per machine to know if it is actually connected or disconnected.

The results then will be sorted by state and date of recieving the last state update. 

Additionally we will check if all machines which ever sent messages to the cloud appear in the given data set or not. If some of them did not update their state within the filtered time period (if you added an timestamp filter) we do not know the actual state. If this is the case the displayed state of this thing will be set to "unknown" and the timestamp information set to "last update out of filtered period".

Final Output in Table Widget:

    The final output of our solution is displayed in a table widget and looks like the one below

Required Analytic Queries: 

All distinct machines	Machine connection status

Script:

# Loading libraries necessary for the applied functions and method within this script
library(dplyr)
library(plyr)


# Prepare data frame
df <- Machineconnectionstatus %>% # Take the input query
            group_by(Thing) %>% # logically group by the machine ID
            filter(Timestamp == max(Timestamp)) %>% # filter for the last occured connection status update 
            arrange(State,Timestamp) # and sort by connection status and occurance

# Convert the UNIX timestamp in an easy to read format
df$Timestamp <- as.character(as.Date(as.POSIXct(df$Timestamp/1000, origin = "1970-01-01"), tz = "UTC")) 


# Define a filter function (only %in% is available out of the box, but not %!in%)
'%!in%' <- function(x,table) match(x,table, nomatch = 0) == 0 
# Search for machines which did not have any connection status update within the filtered time period of the query
machines_not_found <- Alldistinctmachines %>%
                        filter(Thing %!in% df$Thing) 

if( nrow(machines_not_found) > 0)
{
    machines_not_found <- machines_not_found %>% mutate(State = "unknown", Timestamp = "last update out of filtered period")
}

# Concatenate machines_not_found at the bottom of df
df <- rbind.fill(df,machines_not_found) 

# Assign data to output variables
machine_ID = as.character(df$Thing)
status = as.character(df$State)
timestamp = df$Timestamp