https://community.splunk.com/t5/Splunk-Search/Show-ratio-in-map-and-Change-marker-size-and-color-based-on/m-p/393839#M195739 <P>Hi, I'm trying to show ratio of active vs total count of students by school in a map. Whereever the ratio is less than 50% need to color that bubble by red and green otherwise.<BR /> The query im using :</P> <P>|inputlookup qa.csv | table student_id, school_name| stats dc(student_id) as total by school_name | join type=left school_name [search index="qa_xapi" | table student_id,school_name| stats dc(student_id) as active by school_name] |fillnull active value=0 | eval ratio=(active/total)*100 | table school_name, ratio </P> <P>Now <STRONG>qa.csv file contain lat,long information but the qa_xapi index doesn't contain lat,long field</STRONG>. The index contain active users where the lookup has total users dump so we need a left join to get active/total ratio.</P> <P>So how can I bring this ratio correlation with lat,long and use geom or geostats to render this in a map. If this can be solved 90% is done then we can render the color's based on ratio using eval condition.</P> <P>Also if you can suggest any other solution other than left join for this.</P> Wed, 30 Sep 2020 01:21:25 GMT jonu4u 2020-09-30T01:21:25Z https://community.splunk.com/t5/Splunk-Search/Show-ratio-in-map-and-Change-marker-size-and-color-based-on/m-p/393839#M195739 <P>Hi, I'm trying to show ratio of active vs total count of students by school in a map. Whereever the ratio is less than 50% need to color that bubble by red and green otherwise.<BR /> The query im using :</P> <P>|inputlookup qa.csv | table student_id, school_name| stats dc(student_id) as total by school_name | join type=left school_name [search index="qa_xapi" | table student_id,school_name| stats dc(student_id) as active by school_name] |fillnull active value=0 | eval ratio=(active/total)*100 | table school_name, ratio </P> <P>Now <STRONG>qa.csv file contain lat,long information but the qa_xapi index doesn't contain lat,long field</STRONG>. The index contain active users where the lookup has total users dump so we need a left join to get active/total ratio.</P> <P>So how can I bring this ratio correlation with lat,long and use geom or geostats to render this in a map. If this can be solved 90% is done then we can render the color's based on ratio using eval condition.</P> <P>Also if you can suggest any other solution other than left join for this.</P> Wed, 30 Sep 2020 01:21:25 GMT https://community.splunk.com/t5/Splunk-Search/Show-ratio-in-map-and-Change-marker-size-and-color-based-on/m-p/393839#M195739 jonu4u 2020-09-30T01:21:25Z