https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122190#M32894 <P>That worked perfect, thanks a bunch! </P> Thu, 29 Jan 2015 20:18:02 GMT blieberman 2015-01-29T20:18:02Z https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122186#M32890 <P>I have a search as below :</P> <PRE><CODE>index="network_wireless" sourcetype="Wireless_Client_Count*" | rex "(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*)" | search ((date_hour &gt;=10 AND date_hour &lt;= 16) AND (date_wday != saturday AND date_wday != sunday)) AP_Name="*" AND AP_Name != "AP Name" | eval site=substr(AP_Name,1,2) |search site="HQ" | bin _time span=1mon | eval date_month = upper(substr(date_month,1,1)).substr(date_month,2) | eval date_month = date_month. " " .date_year | stats avg(Client_Count) by date_month,_time |eval "Ideal Percent Utilization"= round(('avg(Client_Count)' / 25) * 100, 0). "%" |appendcols [search index=network_wireless sourcetype=Wireless_AirQuality_Index* | search ((date_hour &gt;=10 AND date_hour &lt;= 16) AND (date_wday != saturday AND date_wday != sunday)) | eval date_month = upper(substr(date_month,1,1)).substr(date_month,2) | rex "(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*)" | search AP_Name="*" AND AP_Name != "AP Name" | eval site=substr(AP_Name,1,2)|search site="HQ" | bin _time span=1mon | eval date_month = date_month. " " .date_year | eval 24Quality = case(match(Radio_Type,"802.11b/g/n"),QualityIndex) | eval 5Quality = case(match(Radio_Type,"802.11a/n"),QualityIndex) |stats avg(24Quality), avg(5Quality) by date_month, _time ] | sort - _time | fields - _time | eval avg(24Quality) = round('avg(24Quality)',2). "%" | eval avg(5Quality) = round('avg(5Quality)',2). "%" | eval avg(Client_Count) = round('avg(Client_Count)',2) | rename date_month as "Month", avg(Client_Count) as "Average Clients per AP", avg(24Quality) as "Average 2.4GHz Quality", avg(5Quality) as "Average 5GHz Quality" </CODE></PRE> <P>With the relevant subsearch being:</P> <PRE><CODE>appendcols [search index=network_wireless sourcetype=Wireless_AirQuality_Index* | search ((date_hour &gt;=10 AND date_hour &lt;= 16) AND (date_wday != saturday AND date_wday != sunday)) | eval date_month = upper(substr(date_month,1,1)).substr(date_month,2) | rex "(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*)" | search AP_Name="*" AND AP_Name != "AP Name" | eval site=substr(AP_Name,1,2)|search site="HQ" | bin _time span=1mon | eval date_month = date_month. " " .date_year | eval 24Quality = case(match(Radio_Type,"802.11b/g/n"),QualityIndex) | eval 5Quality = case(match(Radio_Type,"802.11a/n"),QualityIndex) |stats avg(24Quality), avg(5Quality) by date_month, _time ] </CODE></PRE> <P>This produces a table like so : <A href="http://img42.com/LPHmm">http://img42.com/LPHmm</A></P> <P>The issue I have is the subsearch data is populated from November to now. So it looks like there's some sorting issue causing it to be keyed to the wrong rows in my resulting table, maybe from all the date manipulation? Any help or ideas would be much appreciated. </P> Thu, 29 Jan 2015 19:28:31 GMT https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122186#M32890 blieberman 2015-01-29T19:28:31Z https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122187#M32891 <P>Have you tried using join instead and join by the date_month field?</P> Thu, 29 Jan 2015 19:40:59 GMT https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122187#M32891 dolivasoh 2015-01-29T19:40:59Z https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122188#M32892 <P>if I switch the "appendcols" to a "join date_month " it seems to work but now only returns the results that contain the subsearch data (i.e. the last 3 months) even though the search was run over the previous year and the main search's columns should contain data. </P> Thu, 29 Jan 2015 19:55:17 GMT https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122188#M32892 blieberman 2015-01-29T19:55:17Z https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122189#M32893 <P>Try join type=left</P> Thu, 29 Jan 2015 19:58:42 GMT https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122189#M32893 dolivasoh 2015-01-29T19:58:42Z https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122190#M32894 <P>That worked perfect, thanks a bunch! </P> Thu, 29 Jan 2015 20:18:02 GMT https://community.splunk.com/t5/Splunk-Search/Why-is-my-appendcols-subsearch-sorting-incorrectly-causing-data/m-p/122190#M32894 blieberman 2015-01-29T20:18:02Z