topic Re: Statistical Count from a lookup Table in Monitoring Splunk

Statistical Count from a lookup Table

zacksoft — Mon, 20 May 2019 13:19:47 GMT

I have a look up csv file added, which looks like this,
The header contains subject names and student name, and then subsequent rows contain performances for each pupil

MATH ENGLISH NAME SCIENCE
good bad Timmy best
good good John better
better bad Alek good

good bad Priya good
beter best Arun best

The above table means Timmy is 'good' at MATH, 'bad' at ENGLISH and 'best' at SCIENCE.
SImilarly John is 'good' at MATH and ENGLISH and 'bad' at SCIENCE.
etc...

I want to know how many kids are good, bad and best at each subject.
in stats table and if possible in a visualization.

e.g. 3 kids are good at MATH(Timmy, JOHN , Priya)
2 kids are best at SCIENCE (Timmy, Arun)

My query starts like,
| inputlookup marks.csv
| stats ........

Re: Statistical Count from a lookup Table

koshyk — Mon, 20 May 2019 13:48:35 GMT

Is it something you looking for?

|inputlookup marks.csv
| stats values(NAME) as students, count by ENGLISH

if yes, you can expand to other subjects

Re: Statistical Count from a lookup Table

zacksoft — Mon, 20 May 2019 14:19:38 GMT

It is counting the header name too . How do I exclude that?
Also, I have a lot of subjects, more than 50, how can I see them all in one query ? Is it possible..

Re: Statistical Count from a lookup Table

zacksoft — Mon, 20 May 2019 14:35:38 GMT

What are the subjects where the BEST count is less than 5 ?
i.e. What are those subjects' names where only 5 or less students perform as BEST.

Re: Statistical Count from a lookup Table

somesoni2 — Mon, 20 May 2019 16:06:07 GMT

Here you go

| inputlookup marks.csv
| table NAME *
| untable NAME subject grade
| chart count over subject by grade