65 lines
2.2 KiB
Python
65 lines
2.2 KiB
Python
from azure.iot.hub import IoTHubRegistryManager
|
|
from azure.iot.hub.protocol.models import QuerySpecification, Module
|
|
from azure.iot.hub.models import CloudToDeviceMethod, CloudToDeviceMethodResult
|
|
from dotenv import load_dotenv
|
|
from isight_device import iSightDevice
|
|
import pandas as pd
|
|
from datetime import datetime, timezone
|
|
|
|
import json
|
|
import os
|
|
|
|
load_dotenv()
|
|
|
|
now = datetime.now(timezone.utc)
|
|
|
|
CONNECTION_STRING = str(os.getenv("CONNECTION_STRING_INOX_PROD"))
|
|
if CONNECTION_STRING == "":
|
|
print("Provide a connection string for the Iot Hub before running the script!")
|
|
exit(13)
|
|
|
|
|
|
registry_manager = IoTHubRegistryManager.from_connection_string(CONNECTION_STRING)
|
|
query_spec = QuerySpecification(query="SELECT * FROM devices")
|
|
|
|
query_result = registry_manager.query_iot_hub(query_spec)
|
|
|
|
rows = []
|
|
for twin in query_result.items:
|
|
rows.append({
|
|
"device_id": twin.device_id,
|
|
"number": twin.tags.get("number") if twin.tags else None,
|
|
"site": twin.tags.get("site") if twin.tags else None,
|
|
"subsite": twin.tags.get("subsite") if twin.tags else None,
|
|
"connection_state": twin.connection_state,
|
|
"last_activity_time": twin.last_activity_time
|
|
})
|
|
|
|
df = pd.DataFrame(rows)
|
|
df['number'] = pd.to_numeric(df['number'], errors='coerce')
|
|
df['number'] = df['number'].astype('Int64')
|
|
df_sorted = df.sort_values(by=["site", "number"]).reset_index(drop=True)
|
|
|
|
for row in df_sorted.itertuples():
|
|
if "cube" not in row.device_id:
|
|
print(f"\"{row.device_id}\", \"{row.site}\", \"{row.number}\",")
|
|
|
|
|
|
# Compute difference in hours (float)
|
|
df_sorted["time_since_last_activity_hours"] = df_sorted["last_activity_time"].apply(
|
|
lambda x: (now - x).total_seconds() / 3600 if pd.notnull(x) else None
|
|
)
|
|
|
|
# Also add a readable string
|
|
df_sorted["time_since_last_activity_str"] = df_sorted["last_activity_time"].apply(
|
|
lambda x: str(now - x).split(".")[0] if pd.notnull(x) else None
|
|
)
|
|
|
|
if "last_activity_time" in df_sorted.columns:
|
|
df_sorted["last_activity_time"] = df_sorted["last_activity_time"].apply(
|
|
lambda x: x.replace(tzinfo=None) if pd.notnull(x) else x
|
|
)
|
|
|
|
|
|
df_sorted.to_excel("iot_devices_2.xlsx", index=False)
|