Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to calculate downtime and uptime from a system log that i am pulling from SQL Server.
The information is like below. you can see how the device offline or online is in the description. Below that I have shown how the calculations should be.
A | B | C | D | E | |
1 | Timestamp | Priority | Source | Target | Description |
2 | 9/22/2020 11:57 | ERROR | Group1 | device1 | Device offline |
3 | 9/22/2020 12:07 | INFORMATION | Group1 | device1 | Device online |
4 | 9/26/2020 18:12 | ERROR | Group1 | device1 | Device offline |
5 | 9/26/2020 18:22 | INFORMATION | Group1 | device1 | Device online |
6 | 11/28/2020 21:31 | ERROR | Group1 | device1 | Device offline |
7 | 11/28/2020 21:41 | INFORMATION | Group1 | device1 | Device online |
8 | 12/2/2020 4:02 | ERROR | Group1 | device2 | Device offline |
9 | 12/2/2020 4:12 | INFORMATION | Group1 | device2 | Device online |
10 | |||||
11 | A3-A2= | 0:10:10 | (hh:mm:ss) | Downtime | |
12 | A4-43= | 102:04:49 | (hh:mm:ss) | Uptime | |
13 | A5-A4= | 0:10:15 | (hh:mm:ss) | Downtime | |
14 | A6-A5= | 1515:08:45 | (hh:mm:ss) | Uptime | |
15 | A7-A6= | 0:10:18 | (hh:mm:ss) | Downtime | |
16 | A8-A7= | 78:20:42 | (hh:mm:ss) | Uptime | |
17 | A9-A8= | 0:10:03 | (hh:mm:ss) | Downtime |
Any help or guidance is greatly appreciated.
Solved! Go to Solution.
Hi @moltra please try the following in Power Query to see if this is what you are trying to achieve:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtI3MjAyUDA0tDI1V9JRcg0K8g8C0u5F+aUFhkBGSmpZZnIqiOUCZinkp6XlZOalKsXqoOg3sjIA6ff0c/MP8nUM8fT3w29KHrIhZlBDLKwMjchxBEK/kRF5jjA01DeygJhiZGhlbEiyK1ANMDEk0xnA4IQYYmJlgCcojHA4Akm7IcGQMMJ0QiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Priority = _t, Source = _t, Target = _t, Description = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type text}, {"Priority", type text}, {"Source", type text}, {"Target", type text}, {"Description", type text}}),
#"Parsed Date Time" = Table.TransformColumns(#"Changed Type",{{"Timestamp", each DateTime.From(_, "en-Us"), type datetime}}),
#"Added Index" = Table.AddIndexColumn(#"Parsed Date Time", "Table Row Index", 0, 1, Int64.Type),
#"Added Index Offset" = Table.AddIndexColumn(#"Added Index", "Offset Row Index", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index Offset", {"Offset Row Index"}, #"Added Index Offset", {"Table Row Index"}, "Added Index1", JoinKind.Inner),
#"Expand Columns" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Timestamp"}, {"End Timestamp"}),
#"Sorted Rows" = Table.Sort(#"Expand Columns",{{"Table Row Index", Order.Ascending}}),
#"Add Type" = Table.AddColumn(#"Sorted Rows", "Type", each if [Priority] = "ERROR" then "Downtime" else "Uptime", type text),
#"Add Duration" = Table.AddColumn(#"Add Type", "Duration Day:Hours:Minutes:Seconds", each [End Timestamp] - [Timestamp] , type time)
in
#"Add Duration"
Hi @moltra ,
Please try:
Duration =
var _next= CALCULATE(MIN('Table'[Timestamp]),FILTER('Table',[Timestamp]>EARLIER('Table'[Timestamp])))
var _seconds= DATEDIFF([Timestamp],_next,SECOND)
var _hour=INT(_seconds/3600)
var _minutes= TRUNC((_seconds-_hour*3600)/ 60)
var _sec=MOD(_seconds,60)
return IF(_seconds<>BLANK(), _hour&":"&IF(_minutes<10,"0"&_minutes,_minutes) &":"&IF(_sec<10,"0"&_sec,_sec))
Type = SWITCH([Priority],"ERROR","Downtime","INFORMATION","Uptime")
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @moltra please try the following in Power Query to see if this is what you are trying to achieve:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtI3MjAyUDA0tDI1V9JRcg0K8g8C0u5F+aUFhkBGSmpZZnIqiOUCZinkp6XlZOalKsXqoOg3sjIA6ff0c/MP8nUM8fT3w29KHrIhZlBDLKwMjchxBEK/kRF5jjA01DeygJhiZGhlbEiyK1ANMDEk0xnA4IQYYmJlgCcojHA4Akm7IcGQMMJ0QiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Priority = _t, Source = _t, Target = _t, Description = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type text}, {"Priority", type text}, {"Source", type text}, {"Target", type text}, {"Description", type text}}),
#"Parsed Date Time" = Table.TransformColumns(#"Changed Type",{{"Timestamp", each DateTime.From(_, "en-Us"), type datetime}}),
#"Added Index" = Table.AddIndexColumn(#"Parsed Date Time", "Table Row Index", 0, 1, Int64.Type),
#"Added Index Offset" = Table.AddIndexColumn(#"Added Index", "Offset Row Index", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index Offset", {"Offset Row Index"}, #"Added Index Offset", {"Table Row Index"}, "Added Index1", JoinKind.Inner),
#"Expand Columns" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Timestamp"}, {"End Timestamp"}),
#"Sorted Rows" = Table.Sort(#"Expand Columns",{{"Table Row Index", Order.Ascending}}),
#"Add Type" = Table.AddColumn(#"Sorted Rows", "Type", each if [Priority] = "ERROR" then "Downtime" else "Uptime", type text),
#"Add Duration" = Table.AddColumn(#"Add Type", "Duration Day:Hours:Minutes:Seconds", each [End Timestamp] - [Timestamp] , type time)
in
#"Add Duration"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |