splunk search

[satheesh121]

{

Exams : { “Message” : “Passed in Maths paper 1 exam” ,”Result”:”Passed”, ’Name’ : “s3”}

SubjecctName:Passed-Maths-SemiAnually

}

{

Exams : { “Message” : “Passed in Maths paper 2 exam” ,”Result”:”Passed”, ’Name’ : “s3”}

SubjecctName:Passed-Maths-SemiAnually

}

{

Exams : { “Message” : “Passed in Maths paper 1 exam” ,”Result”:”Passed”, ’Name’ : “s4”}

SubjecctName:Passed-Maths-SemiAnually

}

{

Exams : { “Message” : “Passed in Maths paper 2 exam” ,”Result”:”Passed”, ’Name’ : “s4”}

SubjecctName:Passed-Maths-SemiAnually

}

{

Exams : { “Message” : “Passed in Maths paper 1 exam” ,”Result”:”Passed”, ’Name’ : “s3”}

SubjecctName:Passed-Maths-Anually

}

{

Exams : { “Message” : “Passed in Maths paper 2 exam” ,”Result”:”Passed”, ’Name’ : “s3”}

SubjecctName:Passed-Maths-Anually

}

{

Exams : { “Message” : “Passed in Physics paper 1 exam” ,”Result”:”Passed”, ’Name’ : “s4”}

SubjecctName:Passed-Physics-Anually

}

{

Exams : { “Message” : “Failed in Physics paper 2 exam” ,”Result”:”Failed”, ’Name’ : “s4”}

SubjecctName:Passed-Physics-Anually

}

Statusreport of each student ( Count no of exams passed and failed by each student)

In the above example s4 passed in physics paper 1 but failed in paper 2 Annually then it must be considered as failed in that exam Annually

In the above example s4 passed in Maths paper 1, passed in paper 2 semi Annually then it must be considered as passed in that exam semi - Annually

In the above example s3 passed in Maths paper 1, passed in paper 2 semi Annually then it must be considered as passed in that exam semi - Annually

In the above example s3 passed in Maths paper 1, passed in paper 2 Annually then it must be considered as passed in that exam Annually

Final output should be

Student  failed passed

S4            1           1

S3            2           0

[ITWhisperer]

Assuming this is part of a JSON event, and that these are elements of an array,

• extract the array elements using spath;
• mvexpand the elements to separate rows;
• use spath to extract name, result and subject;
• evaluate a new field as 1 if result is failed;
• evaluate another field as 1 if result is passed;
• use stats to collect values of passed and failed by name, subject;
• evaluate exampassed as 1 if zero failed papers
• evaluate examfailed as 1 if any failed papers
• use stats to sum passed and failed exams by name

[xanthakita]

I would check out this page:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/JSONFunctions 

as these functions make it cleaner and easier to extract JSON fields (particularly when your events are as consistent as these) then follow the steps that @ITWhisperer suggested.

[satheesh121]

Thanks for your response. Can you please post some query I am new to splunk

[ITWhisperer]

Can you post some example anonymised raw events that you are working with?

[satheesh121]

It would be exactly like this format

Time                                 Event

4/1/21  2:10:9          {

                                                 Exams : { “Message” : “Passed in Maths paper 1 exam” ,”Result”:”Passed”, "Name" : “s3”}

                                                 SubjectName:Passed-Maths-SemiAnually

                                              }

                                             host=localhost:8089        sourcetype = app_log

4/1/21 2:11:19            {

                                                 Exams : { “Message” : “Passed in Maths paper 2 exam” ,”Result”:”Passed”, "Name" : “s3”}

                                                 SubjectName:Passed-Maths-SemiAnually

                                              }

                                             host=localhost:8089        sourcetype = app_log

[ITWhisperer]

| makeresults | eval _raw="{

Exams : { \"Message\" : \"Passed in Maths paper 1 exam\" ,\"Result\":\"Passed\", \"Name\" : \"s3\"}

SubjecctName:Passed-Maths-SemiAnually

}
|
{

Exams : { \"Message\" : \"Passed in Maths paper 2 exam\" ,\"Result\":\"Passed\", \"Name\" : \"s3\"}

SubjecctName:Passed-Maths-SemiAnually

}
|
{

Exams : { \"Message\" : \"Passed in Maths paper 1 exam\" ,\"Result\":\"Passed\", \"Name\" : \"s4\"}

SubjecctName:Passed-Maths-SemiAnually

}
|
{

Exams : { \"Message\" : \"Passed in Maths paper 2 exam\" ,\"Result\":\"Passed\", \"Name\" : \"s4\"}

SubjecctName:Passed-Maths-SemiAnually

}
|
{

Exams : { \"Message\" : \"Passed in Maths paper 1 exam\" ,\"Result\":\"Passed\", \"Name\" : \"s3\"}

SubjecctName:Passed-Maths-Anually

}
|
{

Exams : { \"Message\" : \"Passed in Maths paper 2 exam\" ,\"Result\":\"Passed\", \"Name\" : \"s3\"}

SubjecctName:Passed-Maths-Anually

}
|
{

Exams : { \"Message\" : \"Passed in Physics paper 1 exam\" ,\"Result\":\"Passed\", \"Name\" : \"s4\"}

SubjecctName:Passed-Physics-Anually

}
|
{

Exams : { \"Message\" : \"Failed in Physics paper 2 exam\" ,\"Result\":\"Failed\", \"Name\" : \"s4\"}

SubjecctName:Passed-Physics-Anually

}"
| eval events=split(_raw,"|")
| mvexpand events
| eval _raw=events
| fields - events _time


| rex "Exams\s\:\s(?<exams>\{[^\}]+\})"
| rex "SubjecctName:(?<subject>.+)"
| spath input=exams
| fields - _raw exams
| eval failed=if(Result="Failed",1,null)
| eval passed=if(Result="Passed",1,null)
| stats values(failed) as failed values(passed) as passed by Name subject
| eval exampassed=if(isnull(failed),1,null)
| eval examfailed=if(isnotnull(failed),1,null)
| stats sum(exampassed) as examspassed sum(examfailed) as examsfailed by Name
| fillnull value=0 examspassed examsfailed