Splunk Search

How to create a table with field value as column header, and another field value as the column value?

qxintuit
Engager

I have data like

1. {
    studentId: 1111
    subject: math
    grade: A
} 

2. {
    studentId: 1111
    subject: history
    grade: A
} 

3. {
    studentId: 2222
    subject: math
    grade: A
} 

4. {
    studentId: 2222
    subject: history
    grade: B
} 

and want to have output as

studentId            math .       history
1111                   A              A
2222                   A              B

I used something like

stats list(subject), list(grade) by studentId

But the result is not what I wanted... Can anyone please help me with an example or something? Thanks a lot!

Tags (2)
0 Karma
1 Solution

niketn
Legend

@qxintuit, try the following run anywhere search with sample data similar to your question:

| makeresults
| eval _raw="{\"students\":[ 
                {
                    \"studentId\": 1111,
                    \"subject\": \"math\",
                    \"grade\": \"A\"
                },
                {  
                    \"studentId\": 1111,
                    \"subject\": \"history\",
                    \"grade\": \"A\"
                },
                {
                    \"studentId\": 2222,
                    \"subject\": \"math\",
                    \"grade\": \"A\"
                },
                {
                    \"studentId\": 2222,
                    \"subject\": \"history\",
                    \"grade\": \"B\"
                } 
            ]
}"
| spath
| rename students{}.grade as grade
| rename students{}.studentId as studentId
| rename students{}.subject as subject
| eval students=mvzip(studentId,mvzip(subject,grade))
| fields students
| mvexpand students
| eval students=split(students,",")
| eval studentId=mvindex(students,0),subject =mvindex(students,1),grade =mvindex(students,2)
| fields - students _*
| chart last(grade) as grade over studentId by subject
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@qxintuit, try the following run anywhere search with sample data similar to your question:

| makeresults
| eval _raw="{\"students\":[ 
                {
                    \"studentId\": 1111,
                    \"subject\": \"math\",
                    \"grade\": \"A\"
                },
                {  
                    \"studentId\": 1111,
                    \"subject\": \"history\",
                    \"grade\": \"A\"
                },
                {
                    \"studentId\": 2222,
                    \"subject\": \"math\",
                    \"grade\": \"A\"
                },
                {
                    \"studentId\": 2222,
                    \"subject\": \"history\",
                    \"grade\": \"B\"
                } 
            ]
}"
| spath
| rename students{}.grade as grade
| rename students{}.studentId as studentId
| rename students{}.subject as subject
| eval students=mvzip(studentId,mvzip(subject,grade))
| fields students
| mvexpand students
| eval students=split(students,",")
| eval studentId=mvindex(students,0),subject =mvindex(students,1),grade =mvindex(students,2)
| fields - students _*
| chart last(grade) as grade over studentId by subject
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

qxintuit
Engager

@niketnilay, thank you so much!

0 Karma
Get Updates on the Splunk Community!

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...

What's New in Splunk Observability - July 2025

What’s New?  We are excited to announce the latest enhancements to Splunk Observability Cloud as well as what ...