How to edit my search to get a count and time chart of unique status codes by URL?

[smutherbavaro]

Hi all -- I'm having some trouble wrapping my mind around a problem I'd like to measure.

I would like to perform a count -- and eventually a timechart -- of the number of status code responses by URL endpoint returning the status code. Currently, I have this:

index=XYZ environment=Production clientName="XYZClient" (statusCode!=200 AND statusCode!=-1) earliest=-24h | timechart count by statusCode

Which spits out a nice timechart of the count of each bad status code response we're seeing throughout the day. BUT I would like to further refine that result to see the count of each status code response by url. That is to say:

For url 1:
X 403 responses
X 500 responses

For url 2:
Y 401 responses
Y 500 responses

Having trouble performing this sort of stats/timechart function against another stats/timechart function and can't think of any other clever ways to accomplish this.

Any thoughts would be hugely appreciated!

[gokadroid]

You shall ask these two questions:

1. Do I want span between two data points of a timechart to adjust automatically?
2. Am I OK with concatenating the status code and URL as a single field?

If the answer to above two questions is yes then you shall use time chart as follows, where answer to second question is more important as timechart takes snigle by clause whereas span can always be controlled by using span= option within timechart command :

index=XYZ environment=Production clientName="XYZClient" (statusCode!=200 AND statusCode!=-1) earliest=-24h 
| eval myfield= url."#".statusCode
| timechart count by myfield

Else you shall use chart command with bucketing _time first and then charting count over _time by url,statusCode , something like below to see if it works out to give the visualization you expect:

index=XYZ environment=Production clientName="XYZClient" (statusCode!=200 AND statusCode!=-1) earliest=-24h
| bucket _time span=1h
| chart count over _time by url,statusCode

[DalJeanis]

Try this

 index=XYZ environment=Production clientName="XYZClient" (statusCode!=200 AND statusCode!=-1) earliest=-24h 
| bin _time span=1h
| stats count as eventcount by _time URL statusCode
| chart sum(eventcount) over URL by statusCode

See revised version below, with test input generator code.

[DalJeanis]

I used this to generate some test data

| gentimes start="1/21/2017:02:00:01" end="1/21/2017:17:22:00" increment=23m  
| eval URL=mvappend("MyURL1","MyURL2") | eval statusCode=mvappend("123","124","137")
| append [
| gentimes start="1/21/2017:08:15:02" end="1/21/2017:12:18:00" increment=41m  
| eval URL=mvappend("MyURL1","MyURL3") | eval statusCode=mvappend("121","124","137","215")
]
| append [
| gentimes start="1/21/2017:10:01:03" end="1/21/2017:12:18:00" increment=11m  
| eval URL=mvappend("MyURL1","MyURL2") | eval statusCode=mvappend("121","137","215")
]
| append [
| gentimes start="1/21/2017:09:11:04" end="1/21/2017:12:18:00" increment=14m  
| eval URL=mvappend("MyURL4","MyURL2") | eval statusCode=mvappend("122","137","123")
]
| mvexpand URL
| mvexpand statusCode
| eval _time = starttime

Then tested this

| bin _time span=1h
| stats count as eventcount by _time URL statusCode
| eval URLStatus = URL." - ".statusCode
| chart sum(eventcount) as eventcount over _time by URLStatus

and this

| bin _time span=1h
| stats count as eventcount by _time URL statusCode
| eval URLStatus = URL." - ".statusCode
| timechart sum(count) by URLStatus

and both give useful-looking results. (limited to ten total combinations of URL and status)