Calculate downtime and uptime from a system log.

[moltra]

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.

[v-eqin-msft]

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.

[Daryl-Lynch-Bzy · Accepted 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"