How to calculate duration by error code?

[dungnq]

Hi team,

I have raw data with status: 200, 404, 503.

183080267.ap-southeast-1.elb.amazonaws.com | app | 200
183080267.ap-southeast-1.elb.amazonaws.com | app | 200
183080267.ap-southeast-1.elb.amazonaws.com | app | 200
183080267.ap-southeast-1.elb.amazonaws.com | app | 404
183080267.ap-southeast-1.elb.amazonaws.com | app | 200
183080267.ap-southeast-1.elb.amazonaws.com | app | 200
183080267.ap-southeast-1.elb.amazonaws.com | app | 200
183080267.ap-southeast-1.elb.amazonaws.com | app | 503

I want to calculate total time with error request (status!=200) by dns. Please help me!!! Thanks.

[yuanliu]

How do you define duration from the data you illustrated?  In other words, without Splunk, how do you calculate duration?  When you ask a question about data processing, first define the problem in terms of data.

[dungnq]

I'm so sorry for this confusion. I send back more detailed information as follows:

time dns | service | status 
--------------------------------------------------------------------------
2023-18-07 12:53:53 183080267.ap-southeast-1.elb.amazonaws.com | app | 200
2023-18-07 12:53:52 183080267.ap-southeast-1.elb.amazonaws.com | app | 200
2023-18-07 12:53:51 183080267.ap-southeast-1.elb.amazonaws.com | app | 200
2023-18-07 12:53:49 183080267.ap-southeast-1.elb.amazonaws.com | app | 404
2023-18-07 12:53:40 183080267.ap-southeast-1.elb.amazonaws.com | app | 404
2023-18-07 12:53:30 183080267.ap-southeast-1.elb.amazonaws.com | app | 200
2023-18-07 12:53:29 183080267.ap-southeast-1.elb.amazonaws.com | app | 200
2023-18-07 12:53:23 183080267.ap-southeast-1.elb.amazonaws.com | app | 503
2023-18-07 12:53:20 183080267.ap-southeast-1.elb.amazonaws.com | app | 503
2023-18-07 12:53:10 183080267.ap-southeast-1.elb.amazonaws.com | app | 503

Purpose I want to calculate total service downtime with status code !=200

Example: at 2023-18-07 12:53:40 services downtime(status code: 404) and then at 2023-18-07 12:53:51 services uptime (status code: 200). So Total downtime: 11s

I used the "transaction" function but it's not correct.Because duration is calculated twice (first from event 404: 2023-18-07 12:53:40 to 1st event 200: 2023-18-07 12:53:51 and second
from event 404: 2023-18-07 12:53:49 to 1st event 200: 2023-18-07 12:53:51)

transaction dns service startswith=(status!=200) endswith=(status=200) | rename duration AS Downtime

[yuanliu]

So, you will first need to determine the start event of "down" stream using streamstats.  But in order to do this by DNS and service, you need to sort twice.  This can be expensive if number of events are large.

If you need to retain all events, you can do

| eval updown = if(status != 200, "down", "up")
| sort DNS service _time
| streamstats min(_time) as down by DNS service updown reset_on_change=true
| eval updown = if(updown == "up" OR _time == down, updown, null())
| sort - DNS service _time
| transaction DNS service startswith=updown=down endswith=updown=up keepevicted=true

Your sample events will show something like

_raw	_time	closed_txn	duration	eventcount	field_match_sum	linecount
2023-18-07T12:53:53,183080267.ap-southeast-1.elb.amazonaws.com,app,200	2023-07-18 12:53:53	0	0	1	2	1
2023-18-07T12:53:52,183080267.ap-southeast-1.elb.amazonaws.com,app,200	2023-07-18 12:53:52	0	0	1	2	1
2023-18-07T12:53:40,183080267.ap-southeast-1.elb.amazonaws.com,app,404 2023-18-07T12:53:49,183080267.ap-southeast-1.elb.amazonaws.com,app,404 2023-18-07T12:53:51,183080267.ap-southeast-1.elb.amazonaws.com,app,200	2023-07-18 12:53:40	1	11	3	6	3
2023-18-07T12:53:30,183080267.ap-southeast-1.elb.amazonaws.com,app,200	2023-07-18 12:53:30	0	0	1	2	1
2023-18-07T12:53:10,183080267.ap-southeast-1.elb.amazonaws.com,app,503 2023-18-07T12:53:20,183080267.ap-southeast-1.elb.amazonaws.com,app,503 2023-18-07T12:53:23,183080267.ap-southeast-1.elb.amazonaws.com,app,503 2023-18-07T12:53:29,183080267.ap-southeast-1.elb.amazonaws.com,app,200	2023-07-18 12:53:10	1	19	4	8	4

If, on the other hand, you don't care about events not used in this calculation, you can improve efficiency by dropping them early:

| eval updown = if(status != 200, "down", "up")
| sort DNS service _time
| streamstats min(_time) as down by DNS service updown reset_on_change=true
| where updown == "up" OR _time == down
| sort - DNS service _time
| transaction DNS service startswith=updown=down endswith=updown=up

This will give you

_raw	_time	closed_txt	duration	eventcount	field_match_sum	linecount
2023-18-07T12:53:40,183080267.ap-southeast-1.elb.amazonaws.com,app,404 2023-18-07T12:53:51,183080267.ap-southeast-1.elb.amazonaws.com,app,200	2023-07-18 12:53:40	1	11	2	4	2
2023-18-07T12:53:10,183080267.ap-southeast-1.elb.amazonaws.com,app,503 2023-18-07T12:53:29,183080267.ap-southeast-1.elb.amazonaws.com,app,200	2023-07-18 12:53:10	1	19	2	4	2

The following is an emulation of your sample data that you can play with and compare with real data:

| makeresults
| eval _raw = "time,DNS,service,status
2023-18-07T12:53:53,183080267.ap-southeast-1.elb.amazonaws.com,app,200
2023-18-07T12:53:52,183080267.ap-southeast-1.elb.amazonaws.com,app,200
2023-18-07T12:53:51,183080267.ap-southeast-1.elb.amazonaws.com,app,200
2023-18-07T12:53:49,183080267.ap-southeast-1.elb.amazonaws.com,app,404
2023-18-07T12:53:40,183080267.ap-southeast-1.elb.amazonaws.com,app,404
2023-18-07T12:53:30,183080267.ap-southeast-1.elb.amazonaws.com,app,200
2023-18-07T12:53:29,183080267.ap-southeast-1.elb.amazonaws.com,app,200
2023-18-07T12:53:23,183080267.ap-southeast-1.elb.amazonaws.com,app,503
2023-18-07T12:53:20,183080267.ap-southeast-1.elb.amazonaws.com,app,503
2023-18-07T12:53:10,183080267.ap-southeast-1.elb.amazonaws.com,app,503"
| multikv forceheader=1
| eval _time = strptime(time, "%Y-%d-%mT%H:%M:%S")
| fields - linecount time
``` the above emulates sample events ```

Hope this helps.

[dungnq]

Hi yuanliu,

So great !!! How kind you are to help me. Thank you very much.

--DungNQ--