Splunk Search

Help getting search that includes LOOKUP that are merged into one row for each result

CSULeigh
Explorer

I am trying to get the following results for date, email and answer with the other data into separate rows:

Results I am getting:

Screen Shot 2020-08-19 at 7.00.13 PM.png

Results I need to see:

Screen Shot 2020-08-19 at 7.00.31 PM.png

Search Query:

index=someindex 
| eval status=case(like(_raw, "%NO%"), "NO", like(_raw, "%YES%"), "YES")
| lookup fall2020OnCampusStudents email OUTPUT class, name, ID, className, classNumber, college
| search class!=""
| table Date, name, email, ID, status, class, className, classNumber, college
| sort college, email, class
| rename email AS "Email", status AS "Answer", class AS "Classes", className as "Class Name", classNumber as "Class Number", college as "College"

I have tried using mvexpand, but it will only take the first line of each field. I am still trying to understand other techniques, but still learning. 

 

 

Labels (3)
0 Karma
1 Solution

CSULeigh
Explorer

Solved the issue with Splunks Help:

| inputlookup fall2020OnCampusStudents.csv
| join email
    [search index=someindex | eval status=case(like(_raw, "%NO%"), "NO", like(_raw, "%YES%"), "YES")
    | table Date, status, email]
| table Date, name, email, ID, status, class, className, classNumber, college
| sort college, email
 
I know the join is expensive, but this will run once a day.

 

View solution in original post

CSULeigh
Explorer

Solved the issue with Splunks Help:

| inputlookup fall2020OnCampusStudents.csv
| join email
    [search index=someindex | eval status=case(like(_raw, "%NO%"), "NO", like(_raw, "%YES%"), "YES")
    | table Date, status, email]
| table Date, name, email, ID, status, class, className, classNumber, college
| sort college, email
 
I know the join is expensive, but this will run once a day.

 

Get Updates on the Splunk Community!

OpenTelemetry for Legacy Apps? Yes, You Can!

This article is a follow-up to my previous article posted on the OpenTelemetry Blog, "Your Critical Legacy App ...

UCC Framework: Discover Developer Toolkit for Building Technology Add-ons

The Next-Gen Toolkit for Splunk Technology Add-on Development The Universal Configuration Console (UCC) ...

.conf25 Community Recap

Hello Splunkers, And just like that, .conf25 is in the books! What an incredible few days — full of learning, ...