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.
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.
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
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.
Hi yuanliu,
So great !!! How kind you are to help me. Thank you very much.
--DungNQ--