CODEWITHSUNDEEP
javasplunksplunk-querysplunk-dashboard
Sumeet Kumar Yadav
Sumeet Kumar Yadav

Reputation: 12995

Splunk group by stats with where condition

I have a below event listed in Splunk. It logs the distinct API call made to services. Like in below example my-bag , my-basket , my-cart as distinct services and URL pattern of call is always /api/{service-name}/{v1 or v2 }/{ method name}? token = {dynamic token}.

 original search
| rex "GET /api/(?<service>[\w\-]+)/"
| stats count by service status

Output

service   status count
------------------------
my-bag    200    2
my-bag    429    1
my-bag    400    1
my-basket 200    2
my-basket 429    1
my-cart   200    1

The above query fetches services count group by status . How to further transform into group service status of 429 and not 429 . Like below .

service   count_of_429  count_of_not_429
----------------------------------------
my-bag       1                 3
my-basket    1                 2
my-cart      1                 1

I tried multireport but it did not work

    original search
    | rex "GET /api/(?<service>[\w\-]+)/"
    | stats count by service status| multireport [ stats sum(count) as count_of_429 by status | where status =429]
[ stats sum(count) as count_of_not_429 by status | where status !=429]

Logs

[host=abc.com request="GET /api/my-bag/v1/add?token=8989khk768yb887" status="200" ]
[host=abc.com request="GET /api/my-basket/v1/max?token=798797hjkhjkjgh8" status="200" ]
[host=abc.com request="GET /api/my-cart/v1/add?token=78765hghjgjh" status="200" ]
[host=abc.com request="GET /api/my-bag/v1/add?token=799865mnbjhgj6" status="429" ]
[host=abc.com request="GET /api/my-basket/v1/count?token=787jkhkjhk" status="200" ]
[host=abc.com request="GET /api/my-bag/v1/add?token=799865mnbjhgj6" status="200" ]
[host=abc.com request="GET /api/my-bag/v1/add?token=799865mnbjhgj6" status="400" ]
[host=abc.com request="GET /api/my-basket/v1/count?token=787jkhkjhk" status="429" ]

Upvotes: 1

Views: 3018

Answers (1)

PM 77-1
PM 77-1

Reputation: 13344

If I understood your task correctly, you need something like below. It asumes that the status field is already defined

   original search
    | rex "(GET|POST|DELETE|PATCH) /api/(?<service>[\w\-]+)/"
    | stats count(eval(status=429)) as count_of_429 
            count(eval(status!=429)) as count_of_not_429 by service

Upvotes: 4

Related Questions