Why is my appendcols subsearch sorting incorrectly, causing data to display in the wrong rows of the table results?

[blieberman]

I have a search as below :

index="network_wireless" sourcetype="Wireless_Client_Count*"   
| rex "(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*),(?[^,]*)"  
| search  ((date_hour >=10 AND date_hour <= 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 >=10 AND date_hour <= 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"

With the relevant subsearch being:

appendcols [search index=network_wireless sourcetype=Wireless_AirQuality_Index* 
| search ((date_hour >=10 AND date_hour <= 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 ] 

This produces a table like so : http://img42.com/LPHmm

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.

[dolivasoh · Accepted solution]

Have you tried using join instead and join by the date_month field?

[blieberman]

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.

[dolivasoh]

Try join type=left

[blieberman]

That worked perfect, thanks a bunch!