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 packages necessary for the applied functions and method within this script
import pandas as pd
import datetime as dtime
from dateutil import tz

# Prepare data frame
df = Machineconnectionstatus.loc[Machineconnectionstatus['Timestamp'] == Machineconnectionstatus.groupby(by='Thing')['Timestamp'].transform(lambda x: [x.max()]*len(x))]

# Convert the UNIX timestamp in an easy to read format
df['Timestamp'] = [dtime.datetime.fromtimestamp(dt / 1000 ).astimezone(tz.gettz('Europe/Berlin')).strftime('%Y-%m-%d %H:%M:%S') for dt in df['Timestamp']]

# Search for machines which did not have any connection status update within the filtered time period of the query
machines_not_found = Alldistinctmachines[ [ m not in set(Machineconnectionstatus['Thing']) for m in Alldistinctmachines['Thing']]]

if(len(machines_not_found) > 0):
    machines_not_found = machines_not_found.assign(State = 'unknown', Timestamp = 'last update out of filtered period' )
    
# Concatenate machines_not_found at the bottom of df
df = df.append(machines_not_found, sort=False)


machine_ID = [str(t) for t in df['Thing']]
status = [str(st) for st in df['State']]
timestamp = [str(st) for st in df['Timestamp']]