https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512035#M143596 <P>Getting closer... but still no dice:</P><LI-CODE lang="markup">| makeresults | eval STUDENT="ALICE" |eval EOY_GRADE=96 |eval GENDER="FEMALE" |eval STUDENT_STATUS="ACTIVE" | append [ makeresults | eval STUDENT="BOB" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="ACTIVE"] | append [ makeresults | eval STUDENT="CANDICE" |eval EOY_GRADE=92 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="DEBBIE" |eval EOY_GRADE=94 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="EDDIE" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="FRANK" |eval EOY_GRADE=96 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] | stats list(STUDENT) AS STUDENTS,list(GENDER) AS GENDERS,list(eval(if(GENDER="MALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,""))) as MALE_GRADES, list(eval(if(GENDER="FEMALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,""))) as FEMALE_GRADES,list(eval(if(STUDENT_STATUS="FORMER",EOY_GRADE,""))) as PREVIOUS_GRADES,list(eval(if(STUDENT_STATUS="ACTIVE",EOY_GRADE,""))) as CURRENT_GRADES by STUDENT_STATUS</LI-CODE> Sun, 02 Aug 2020 03:26:54 GMT awmorris 2020-08-02T03:26:54Z https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512032#M143594 <P>Imagine the following data set:</P><TABLE width="471"><TBODY><TR><TD width="90"><STRONG>STUDENT</STRONG></TD><TD width="98"><STRONG>EOY_GRADE</STRONG></TD><TD width="132"><STRONG>GENDER</STRONG></TD><TD width="151"><STRONG>STUDENT_STATUS</STRONG></TD></TR><TR><TD>Alice</TD><TD>96</TD><TD>Female</TD><TD>ACTIVE</TD></TR><TR><TD>Bob</TD><TD>94</TD><TD>Male</TD><TD>ACTIVE</TD></TR><TR><TD>Candice</TD><TD>92</TD><TD>Female</TD><TD>FORMER</TD></TR><TR><TD>Debbie</TD><TD>94</TD><TD>Female</TD><TD>FORMER</TD></TR><TR><TD>Eddie</TD><TD>94</TD><TD>Male</TD><TD>FORMER</TD></TR><TR><TD>Frank</TD><TD>96</TD><TD>Male</TD><TD>FORMER</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>And I would like the produce the following output comparing current students to former:</P><TABLE width="650"><TBODY><TR><TD width="90"><STRONG>STUDENT</STRONG></TD><TD width="98"><STRONG>EOY_GRADE</STRONG></TD><TD width="132"><STRONG>PREV_GENDER_AVG</STRONG></TD><TD width="151"><STRONG>PREV_CLASS_AVG</STRONG></TD><TD width="179"><STRONG>CURRENT_CLASS_AVG</STRONG></TD></TR><TR><TD>Alice</TD><TD>96</TD><TD>93</TD><TD>94</TD><TD>95</TD></TR><TR><TD>Bob</TD><TD>94</TD><TD>95</TD><TD>94</TD><TD>95</TD></TR></TBODY></TABLE><P><BR />Thanks in advance for consideration and thoughts</P> Sun, 02 Aug 2020 01:11:08 GMT https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512032#M143594 awmorris 2020-08-02T01:11:08Z https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512033#M143595 <P>To help, here is an SPL query to preload the data:<BR /><BR /></P><LI-CODE lang="markup">| makeresults | eval STUDENT="ALICE" |eval EOY_GRADE=96 |eval GENDER="FEMALE" |eval STUDENT_STATUS="ACTIVE" | append [ makeresults | eval STUDENT="BOB" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="ACTIVE"] | append [ makeresults | eval STUDENT="CANDICE" |eval EOY_GRADE=92 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="DEBBIE" |eval EOY_GRADE=94 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="EDDIE" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="FRANK" |eval EOY_GRADE=96 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] |table STUDENT,EOY_GRADE,GENDER,STUDENT_STATUS</LI-CODE> Sun, 02 Aug 2020 01:35:52 GMT https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512033#M143595 awmorris 2020-08-02T01:35:52Z https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512035#M143596 <P>Getting closer... but still no dice:</P><LI-CODE lang="markup">| makeresults | eval STUDENT="ALICE" |eval EOY_GRADE=96 |eval GENDER="FEMALE" |eval STUDENT_STATUS="ACTIVE" | append [ makeresults | eval STUDENT="BOB" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="ACTIVE"] | append [ makeresults | eval STUDENT="CANDICE" |eval EOY_GRADE=92 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="DEBBIE" |eval EOY_GRADE=94 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="EDDIE" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="FRANK" |eval EOY_GRADE=96 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] | stats list(STUDENT) AS STUDENTS,list(GENDER) AS GENDERS,list(eval(if(GENDER="MALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,""))) as MALE_GRADES, list(eval(if(GENDER="FEMALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,""))) as FEMALE_GRADES,list(eval(if(STUDENT_STATUS="FORMER",EOY_GRADE,""))) as PREVIOUS_GRADES,list(eval(if(STUDENT_STATUS="ACTIVE",EOY_GRADE,""))) as CURRENT_GRADES by STUDENT_STATUS</LI-CODE> Sun, 02 Aug 2020 03:26:54 GMT https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512035#M143596 awmorris 2020-08-02T03:26:54Z https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512036#M143597 <P>HIT DANG!!!&nbsp; &nbsp;I FINALLY GOT IT!</P><LI-CODE lang="markup">| makeresults | eval STUDENT="ALICE" |eval EOY_GRADE=96 |eval GENDER="FEMALE" |eval STUDENT_STATUS="ACTIVE" | append [ makeresults | eval STUDENT="BOB" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="ACTIVE"] | append [ makeresults | eval STUDENT="CANDICE" |eval EOY_GRADE=92 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="DEBBIE" |eval EOY_GRADE=94 |eval GENDER="FEMALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="EDDIE" |eval EOY_GRADE=94 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] | append [ makeresults | eval STUDENT="FRANK" |eval EOY_GRADE=96 |eval GENDER="MALE" |eval STUDENT_STATUS="FORMER"] | eval MALE_GRADE=if(GENDER="MALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,"") | eval FEMALE_GRADE=if(GENDER="FEMALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,"") | eval PREVIOUS_GRADE=if(STUDENT_STATUS="FORMER",EOY_GRADE,"") | eval CURRENT_GRADE=if(STUDENT_STATUS="ACTIVE",EOY_GRADE,"") | eval STUDENT_STRING=STUDENT.",".EOY_GRADE.",".GENDER.",".STUDENT_STATUS | stats avg(CURRENT_GRADE) AS CURRENT_CLASS_AVG, avg(MALE_GRADE) AS PREV_MALE_AVG, , avg(FEMALE_GRADE) AS PREV_FEMALE_AVG, , avg(PREVIOUS_GRADE) AS PREV_CLASS_AVG,list(STUDENT_STRING) AS STUDENTS | mvexpand STUDENTS | search STUDENTS="*,ACTIVE" | rex field=STUDENTS "(?&lt;STUDENT&gt;.*),(?&lt;EOY_GRADE&gt;.*),(?&lt;GENDER&gt;.*),(?&lt;STUDENT_STATUS&gt;.*)" | eval PREV_GENDER_AVG=if(GENDER="MALE",PREV_MALE_AVG,PREV_FEMALE_AVG) | table STUDENT,EOY_GRADE,PREV_GENDER_AVG,PREV_CLASS_AVG,CURRENT_CLASS_AVG</LI-CODE> Sun, 02 Aug 2020 04:24:42 GMT https://community.splunk.com/t5/Splunk-Search/Moving-beyond-noob-queries-comparing-results/m-p/512036#M143597 awmorris 2020-08-02T04:24:42Z