How to group results and list common occurrences by time?

[guywood13]

I have the following data:

{
  "remote_addr": "1.2.3.4",
  "remote_user": "-",
  "time_local": "24/Nov/2022:09:55:46 +0000",
  "request": "POST /myService.svc HTTP/1.1",
  "status": "200",
  "request_length": "4581",
  "body_bytes_sent": "4891",
  "http_referer": "-",
  "http_user_agent": "-",
  "http_x_forward_for": "-",
  "request_time": "0.576"
}

These are nginx access logs.  I have a situation where certain requests are failing and then retrying every hour or so.  I want to identify these as best I can.  So...

• Return results where status!=200
• Group where:
  • remote_addr matches, and
  • request_length matches, and
  • status matches, and
  • body_bytes_sent matches
    (I'm making the presumption these would be our identical requests with same values for these)
• Create a table of these results showing the time_local for each occurence
  • Order time_local within each row (from earliest to latest)
• This would leave rows where the above matches aren't made and I'd just want these listing on individual rows

This is beyond my capabilities and I got this (not very) far:

index=index source="/var/log/nginx/access.log" | 
    where status!=200 |
    stats list(time_local) by request_length |
    sort - list(time_local)

This is sort of what I want but doesn't do any matching.  It does group the time_local against the request_length which is how I'd like the output (but including the other fields for visibility).  Also, the sort doesn't work as it seems to sort by the first record in each row and I want it to sort WITHIN the row itself.

This the output:

request_length	list(time_local)
26562	24/Nov/2022:16:19:20 +0000 24/Nov/2022:14:16:45 +0000 24/Nov/2022:12:15:04 +0000 24/Nov/2022:11:15:01 +0000 24/Nov/2022:15:18:02 +0000
41977	24/Nov/2022:16:19:20 +0000 24/Nov/2022:14:16:45 +0000 24/Nov/2022:12:15:04 +0000 24/Nov/2022:11:15:01 +0000 24/Nov/2022:15:18:02 +0000 24/Nov/2022:13:15:06 +0000

But I want it to look more like this...

request_length	status	body_bytes_sent	remote_addr	time_local
26562	500	4899	1.2.3.4	24/Nov/2022:11:15:01 +0000 24/Nov/2022:12:15:04 +0000 24/Nov/2022:14:16:45 +0000 24/Nov/2022:15:18:02 +0000 24/Nov/2022:16:19:20 +0000
41977	500	5061	6.7.8.9	24/Nov/2022:11:15:01 +0000 24/Nov/2022:12:15:04 +0000 24/Nov/2022:13:15:06 +0000 24/Nov/2022:14:16:45 +0000 24/Nov/2022:15:18:02 +0000 24/Nov/2022:16:19:20 +0000

[guywood13]

Oh, figured out the way to do the sort as I want:

index=index source="/var/log/nginx/access.log"
| where status!=200
| sort time_local
| stats list(time_local) by request_length status body_bytes_sent remote_addr

Thanks again @yuanliu 😃

[guywood13]

Oooohhhhh I didn't realise it was that simple!  Thank you.  To finish off, know how I can sort the timestamps within the grouped rows?  The existing sort does the whole list by the first entry it seems.

[yuanliu · Accepted solution]

Not sure if I understand the difficulty.  What you are asking seems to be just adding the extra fields in groupby, like

index=index source="/var/log/nginx/access.log"
| where status!=200
| stats list(time_local) by request_length status body_bytes_sent remote_addr
| sort - list(time_local)

Can you explain what is missing?