New Relic logging % of response codes from the message

I have an infra agent installed and logging enabled and I would like to run some NRQL to get the ‘% of response codes’ from the message. Is this possible?

The format of the messages is as follows:-
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken

I want to report on sc-status so ‘% of 200 response’ etc and create a dashboard.

I have tried ‘SELECT count(*) FROM Log where application = ‘----’ FACET status’ but status is not recognised.

Thanks

Hi, @bgreenwood: It would be a lot easier if you configure a parsing rule for your logs to split the fields into individual attributes. But you may be able to use RLIKE to extract the element using a regular expression:

SELECT percentage(count(*), WHERE message RLIKE r'(?:\S+){11}(200)') FROM Log
2 Likes

Thanks Phil

I figured that using the parsing rule would be more suitable . As I am looking to parse iis logs the GROK parsing logic I used is as follows:

%{TIMESTAMP_ISO8601:log_timestamp} %{IPORHOST:S-IP} %{WORD:CS-Method} %{URIPATH:CS-URI-Stem} (?:-|"%{URIPATH:CS-URI-Query}") %{NUMBER:S-Port} %{NOTSPACE:CS-Username} %{IPORHOST:C-IP} %{NOTSPACE:CS-UserAgent} %{NOTSPACE:CS-Referer} %{NUMBER:SC-Status} %{NUMBER:SC-SubStatus} %{NUMBER:SC-Win32-Status} %{NUMBER:Time-Taken}

Hopefully this will help someone else in a similar situation.

1 Like

Hi, @bgreenwood: FYI (and for anyone finding this topic later), there is a built-in parsing rule for IIS logs; you should be able to specify iis_w3c in the logtype setting of your log forwarding configuration file. No need to create a custom parsing rule.

1 Like