Hello, I am creating some reports to measure the uptime of hardware we have deployed, and I need a way to filter out multiple date/time ranges the match up with maintenance windows. We are utilizin...
See more...
Hello, I am creating some reports to measure the uptime of hardware we have deployed, and I need a way to filter out multiple date/time ranges the match up with maintenance windows. We are utilizing a Data Model and tstats as the logs span a year or more.
The (truncated) data I have is formatted as so:
time range: Oct. 3rd - Oct 7th. |tstats summariesonly=true allow_old_summaries=true count(device.status) as count from datamodel=Devices.device where device.status!="" AND device.customer="*" AND device.device_id ="*" by device.customer, device.device_id, device.name, device.status _time
device.customer device.device_id device.name device.status _time count
ppt
webOS-205AZXCA8162
Sao Paulo Office
offline
2022-10-04
314
ppt
webOS-205AZXCA8162
Sao Paulo Office
offline
2022-10-05
782
ppt
webOS-205AZXCA8162
Sao Paulo Office
offline
2022-10-06
749
ppt
webOS-205AZXCA8162
Sao Paulo Office
offline
2022-10-07
1080
ppt
webOS-205AZXCA8162
Sao Paulo Office
online
2022-10-04
510
ppt
webOS-205AZXCA8162
Sao Paulo Office
online
2022-10-05
658
ppt
webOS-205AZXCA8162
Sao Paulo Office
online
2022-10-06
691
ppt
webOS-205AZXCA8162
Sao Paulo Office
online
2022-10-07
360
ppt
webOS-205AZXCA8162
Sao Paulo Office
warning
2022-10-04
1
ppt
webOS-205AZXCA8162
Sao Paulo Office
warning
2022-10-06
2
ppt
webOS-205AZXCA8162
Sao Paulo Office
warning
2022-10-07
1
As the reports will be run by other teams ad hoc, I was attempting to use a 'blacklist' lookup table to allow them to add the devices, time ranges, or device AND time range they wish to exclude from the results. That lookup table is formatted as such:
type
start
end
deviceID
note
time
2022-10-03T13:10:30.000-04:00
2022-10-04T14:10:30.000-04:00
test range 10-04-2022 1:30 through 2:10 in EST UTC-4
device
12345
timedevice
2022-10-04T13:10:30.000-04:00
2022-10-05T14:10:30.000-04:00
webOS-205AZXCA8162
time
2022-10-06T13:10:30.000-04:00
2022-10-06T14:10:30.000-04:00
test range 10-06-2022 1:30 through 2:10 in EST UTC-4
device
webOS-205AZXCA8122
In my head, this works as a report they run on the total timeframe they wish to analyze, and then the devices, timeframes, and timeframe/device events are removed as entered on the lookup table. My biggest hang up right now is finding a way to exclude the unknown quantity of time or timedevice blacklist entries from the total list of results.
Thank you for any help you can provide!