Splunk Search

Help getting count by letter grade based on score range

Diana_a
Loves-to-Learn Lots

Hi,

I was hoping someone can help me, though I have seen some examples, but all of them involve case. 
I need to create a statistical table that would give me the letter grade, count, percentage and grand total as the last column (the last one is just for me to see the actual total)

 

Letter grade Count Perc % Grand Total
A 33 33.33 100
B 33 33.33 100
C... 33... 33.33... 100 ...
no score 1 1 100

 

I wrote in SQL where I join 2 tables the ones with the SCORES and one with the STUDENTS. If a student does not have a score or it's a zero, they should just go to the no score bucket.

SELECT t.SCORE as [grade],
count(*) [counter],
count(*)*100.00 / SUM(COUNT(*)) OVER() as [perc students %],
SUM(COUNT(*)) OVER() as [total students]
FROM
( select case
when SCORE = 0 then 'no score or zero'
when SCORE between 1 and 59 THEN 'F'
when SCORE between 60 AND 69 THEN 'D'
when SCORE between 70 AND 79 THEN 'C'
when SCORE between 80 AND 89 THEN 'B'
else 'A' end as SCORE
FROM
[main_db].[dbo].[scores]
RIGHT JOIN [main_db].[dbo].[students] ON [scores].STUDENTNUMBER = [students].STUDENTNUMBER
) t
Group by t.SCORE

My SPL I have so far is that I need join them but I am having a hard time:

This contains the list of active student numbers:

index=main host=main source=students  sourcetype=db_students STUDENTNUMBER

And this contains all (inactive and active) student numbers and their scores.

index=main host=main source=scores sourcetype=db_scores
| dedup STUDENTID
| eval scoreRange=case(SCORE >= 90, "A",
SCORE >= 80 AND CREDIT_SCORE <= 89, "B",
SCORE >= 70 AND CREDIT_SCORE <= 79, "C",
SCORE >= 60 AND CREDIT_SCORE <= 69, "D",
SCORE >= 0 AND CREDIT_SCORE <= 59, "F"
)
| stats count(STUDENTID) by scoreRange


Don't know how to join the above to get the counts from active students (STUDENT table) only and don't know how to tell that if there's no score in the SCORES table, to give me "No Score".


I'd really appreciate any hints you can give me, I believe it may need tweaks because it needs to be more efficient and perhaps I don't need to use CASE.

 

Much appreciated!

 

Diana

Labels (4)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Can you share some sample events from your student and scores sources?

Is SCORE and CREDIT_SCORE the same thing?

Which field tells you if the student is active?

Your calculations are different between the SQL and SPL for grade F and consequently "no score" - was this deliberate?

0 Karma

gcusello
Legend

Hi @Diana_a,

about the first question,  you should try to use the evenstats command before the stats, remembering to add the result of evenstats to the stats, something like this:

index=main host=main source=scores sourcetype=db_scores
| eval scoreRange=case(SCORE >= 90,"A", SCORE >= 80 AND CREDIT_SCORE <= 89,"B", SCORE >= 70 AND CREDIT_SCORE <= 79,"C", SCORE >= 60 AND CREDIT_SCORE <= 69,"D", SCORE >= 0 AND CREDIT_SCORE <= 59, "F")
| evenstats dc(STUDENT_ID) AS STUDENT_COUNT
| stats count(STUDENTID) AS students values(STUDENT_COUNT) AS STUDENT_COUNT BY scoreRange

Ciao.

Giuseppe

 

0 Karma

Diana_a
Loves-to-Learn Lots

I see, thank you for the tip! I am using it to check

0 Karma

gcusello
Legend

Hi @Diana_a,

good for you, see next time!

please accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors  😉

 

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...