Statistics based on regex and quotes

[jaango123]

Hi,

I am new to Splunk and I managed to construct the below query to generate statistics(getting count of customers grouped by REQ). However I wanted to add four more columns with count values.
One for Success, one for failure, one for type of request(GET/POST etc), one for language

Success count should be counted based on HTTPRES="200 OK".
For failure count the above will anything other than 200
Request should be whether it is GET/POST etc. Obtained from Rest="GET h t t p ://.........". The characters after Rest="
Langage is the trickiest part. We need to extract 'gr/gr' from this url url starting with http/somealphabets/alphabets/gr/gr/....continues.

sample log, the url link starts with http, as I cant post any links directly now.

Aug 03 07:53:34 servername_APP_LOG [IN_PROD][12345678][APP_LOG][note] abc(NewService): Id(125678)[RESP][1.2.3.4] Globid(45678912): REQ=ABC.ElectronicsService,Customer=JIKL,NUM=34872,HTTPRES="200 OK",Fromcache=true,Result="",Op_name=ABCElectronicsService.getallpages.v1.0,Receive=Accepted,Policy=onepermin,Value=345,time=1,spent=2,Size=2,RspSize=123,Format=json,Actual=,remaining=2.3.4.5,Rest="GET url starting with http/salo/vbghj/gr/gr/val/prot/34567",Rwe="",Notice="",GH="version 1.1"

My cuurent query(query is fine)

"[APP_LOG]" "[IN_PROD]"
 | stats count as RequestCount count(Customer=*) by Customer, REQ

  | table Customer, REQ, RequestCount

yields

  Customer          REQ                                  RequestCount
  JIKL              ABC.ElectronicsService               5

Wanted like below table. Sorry for bad formatting

  Customer  REQ                 RequestCount       SuccessCount   Failure  Request          Language
  JIKL            ABC.ElectronicsService               5                    3           2         GET                gr/gr

[richgalloway · Accepted solution]

Getting the success count can be done using eval within stats. ... | stats count(eval(HTTPRES="200 OK")) as SuccessCount. Get the failure count with a similar command. ... | stats count(eval(HTTPRES="200 OK")) as FailureCount.
Pulling language out of the URL is not so bad, assuming the URL format is consistent with your example. rex handles that. ... rex "https?:\/\/.*?\/.*?\/(?<language>\w\w\/\w\w)\/". The same can be said for Request.

Putting it all together looks like this:

"[APP_LOG]" "[IN_PROD]"
| rex "https?:\/\/.*?\/.*?\/(?<language>\w\w\/\w\w)\/"
| rex "rest=\"(?<Request>\w+)"
| stats count as RequestCount count(Customer=*) count(eval(HTTPRES="200 OK")) as SuccessCount count(eval(HTTPRES!="200 OK")) as FailureCount values(language) as Language values(Request) as Request by Customer, REQ
| table Customer, REQ, RequestCount, SuccessCount, FailureCount, Request, Language

[jaango123]

Thanks.. I will try this. However the FailureCount is same as Successcount?

[richgalloway]

FailureCount is different. I've updated my answer.

[jaango123]

Thanks.. It shows how to use Regex and to group fields. Can you please let me know how to modify this so that i can group by Language as well. I get an error "The output field cannot have the same name Language as the group by field"