I am using LibreCalc (on an airgapped system, so limited on software access) to perform some analysis of events extracted from a log file.
I wanted to automate analysis of the time elapsed between the Connect and Disconnect events of a devices connection and output both the time elapsed during the event and a total for the device that month. Because the events are logged chronologically however, sometimes another device will connect prior to another device disconnects therefore the sequential analysis methods I'm familiar are not viable and I'm baffled as to how to attempt this within LibreCalc.
However if there is a solution that could work within bash I'm happy to hear it. I wish to use the results for charts hence the use of LibreCalc.
The data looks like this
| Device | Event Type | DateTime |
|---|---|---|
| Device1 | Connect | 2022-07-01T00:07:02Z |
| Device1 | Disconnect | 2022-07-01T00:07:15Z |
| Device3 | Connect | 2022-07-01T00:07:26Z |
| Device4 | Connect | 2022-07-01T00:08:12Z |
| Device4 | Disconnect | 2022-07-01T00:08:19Z |
| Device3 | Disconnect | 2022-07-01T00:08:56Z |
Many thanks.
答案1
It will be easier to look not for the next match, but for the previous one: find for each Disconnect the corresponding Connect (of course, if it is present in the log).
A formula like
=IF(B3="Disconnect";MAXIFS($C$2:C2;$A$2:A2;A3;$B$2:B2;"Connect");0)+IF(CURRENT()=0;C3;0)
will do the job. Now count the duration of each row and use the filter to hide rows with zero (and empty) duration.
To calculate the total values for a period (for a month, a week, a decade, a year), apply the Pivot Table to the resulting table
The MAXIFS() function used in the formula expects the first parameter to be a range of numeric values. Therefore, you should first make sure that the dates in the DateTime column are really dates, and not their textual representation. Look at this column. By default numbers are aligned to the right cell borders whereas text is aligned to the left border
A more reliable way to determine the contents of each cell is to use Value Highlighting (CTRL+F8)
If your dates are presented as text, you will not be able to perform calculations with them and you will not be able to plot a graph based on them either.
Converting text representations of dates to numbers is not difficult, there are many tricks that will accomplish this trick. For example, enter a formula like
=VALUE(REGEX(C2;"[^(\d:\-)]";" ";"g"))
in an auxiliary column and extend it to the end of the table. Yes, the result of the calculation will look strange, it will be something like 44743.0048842593 - this is how Calc stores and processes dates and times.
Just choose Format - Cells or press Ctrl+1 and select the appropriate format to display (or enter your own)
Now cut out this auxiliary column and use Paste Special - Values&Formats to place it in place of the original text values
If you don't like using formulas, auxiliary columns, copying and pasting, you can use the simple method of converting data "in place" - Text to Columns.
Select the date column, choose Data - Text to Columns, choose Separated By - Other - Z and click OK
Your dates are now real dates and formulas that use them should work correctly.