topic Re: multiple counts in one table in Splunk Search

multiple counts in one table

sati80 — Mon, 24 Jun 2013 23:53:58 GMT

I have a table called READER_NAME
this table has all info of reader

I want to query with location(PHX,SFO,SLC,THF.TKO) and get result like this

PHX:20
SFO:10
SLC:20
THF:100
TKO:10

if I do this query

index="access" SFO earliest=-20m latest=now | stats count(READER_NAME)

I only get result on SFO(SFO:10)

Any solution that I can get multiple counts with one query

Thank you in advance

Re: multiple counts in one table

adrianathome — Tue, 25 Jun 2013 00:12:10 GMT

Try this:
index="access" SFO earliest=-20m latest=now | stats count by READER_NAME

Keep in mind that by having SFO in your search you might only get events that contain SFO.

Re: multiple counts in one table

sati80 — Tue, 25 Jun 2013 00:16:34 GMT

if I query
index="access" earliest=-20m latest=now | table READER_NAME

It returns

PHX DC  STRWL
SEOL VENDOR OFF 1
KUL.26 OFFICE NTH
SEOL.37 S ELEV LOBBY ENT
TPZ DC 1.1 LOBBY MANTRAP EXIT 1
PHX DC 2.2 S OFFICE ENT
TPZ DC 1.1 LOBBY MANTRAP EXIT 1
SEOL.19 NORTH LOBBY ENT
SEOL.36 N ELEV LOBBY ENT

and I want to get count on each location

if I try this query

index="access" earliest=-20m latest=now | stats count(READER_NAME)

it only returns count number

Thank you,

Re: multiple counts in one table

adrianathome — Tue, 25 Jun 2013 00:24:06 GMT

I updated my answer. Check below.

Re: multiple counts in one table

sati80 — Tue, 25 Jun 2013 00:33:47 GMT

I want to get all locations with one query
the result should be like this
PHX:20
SFO:10
SLC:20
THF:100
TKO:10

Re: multiple counts in one table

adrianathome — Tue, 25 Jun 2013 01:00:09 GMT

Try it without the SFO in your search. Then write some regex to extract the Codes (SFO, PHX, SLC). Without knowing your data, see below.

index="access" earliest=-20m latest=now | rex field=READER_NAME (?^\w*) | stats count by location