Trying to join columns to an initial query

[sbedard]

Hello,

I wonder if someone could help me out with a query. I'm trying to compare a value against different point in time, for multiple sources. For some reason, the appendcols seems to be adding columns without matching with another field  so if the values come out in a different order (not all datasource report at different times), then the report gets mixed results from different datasources so they don't match. 

Basically, I need this query to join/group on the actual_data_source field

index=bi sourcetype=dbx_bi source=automation earliest=-1h@h latest=@h 
| bin _time span=1h 
| stats sum(error_percentage) as last_hour_percentage by _time,actual_data_source
| appendcols 
  [search index=bi sourcetype=dbx_bi source=automation earliest=-169h@h latest=-168h@h 
  | bin _time as last_week span=1h 
  | stats sum(error_percentage) as last_week_percentage by last_week,actual_data_source  ] 
| appendcols 
  [search index=bi sourcetype=dbx_bi source=automation earliest=-673h@h latest=-672h@h 
  | bin _time as last_month span=1h 
  | stats sum(error_percentage) as last_month_percentage by last_month,actual_data_source ] 
| eval last_hour=strftime(_time,"%Y-%m-%d %H:%M (%:::z %Z)"),last_week=strftime(last_week,"%Y-%m-%d %H:%M (%:::z %Z)"), last_month=strftime(last_month,"%Y-%m-%d %H:%M (%:::z %Z)"), change=(last_hour_percentage-last_week_percentage) 
| search last_hour_percentage>10 
| table actual_data_source, last_hour, last_hour_percentage, last_week, last_week_percentage, last_month, last_month_percentage

Thanks!

[yuanliu]

That is Splunk telling you that appendcol is not the correct command if you expect matching.  Try something like

index=bi sourcetype=dbx_bi source=automation ((earliest=-1h@h latest=@h) OR (earliest=-169h@h latest=-168h@h) OR (earliest=-673h@h latest=-672h@h))
| bin _time span=1h@h
| stats sum(error_percentage) as error_percentage by _time, actual_data_source
| eval period = case(_time > relative_time(now(), "-2h", "last_hour", _time < relative_time(now(), "-200h"), "last_week", true(), "last_month")
| foreach last_hour last_week last_month
    [eval <<FIELD>>_percentage = if(period == "<<FIELD>>", error_percentage, null())
    | eval <<FIELD>> = if(period == "<<FIELD>>", _time, null())]
| fields - error_percentage
| stats values(*_percentage) as *_percentage values(last_*) as last_* by actual_data_source
| search last_hour_percentage>10 
| eval last_hour=strftime(_time,"%Y-%m-%d %H:%M (%:::z %Z)"),last_week=strftime(last_week,"%Y-%m-%d %H:%M (%:::z %Z)"), last_month=strftime(last_month,"%Y-%m-%d %H:%M (%:::z %Z)"), change=(last_hour_percentage-last_week_percentage) 
| table actual_data_source, last_hour, last_hour_percentage, last_week, last_week_percentage, last_month, last_month_percentage

[johnhuang · Accepted solution]

Something like this:

index=bi sourcetype=dbx_bi source=automation earliest=-1h@h latest=-0h@h 
| eval last_hour=strftime(_time,"%Y-%m-%d %H:00 (%:::z %Z)")
| stats sum(error_percentage) AS last_hour_percentage BY last_hour actual_data_source
| append [search index=bi sourcetype=dbx_bi source=automation earliest=-169h@h latest=-168h@h 
| eval last_week=strftime(_time,"%Y-%m-%d %H:00 (%:::z %Z)")
| stats sum(error_percentage) AS last_week_percentage BY last_week actual_data_source]
| append [search index=bi sourcetype=dbx_bi source=automation earliest=-673h@h latest=-672h@h
| eval last_month=strftime(_time,"%Y-%m-%d %H:00 (%:::z %Z)")
| stats sum(error_percentage) AS last_month_percentage BY last_month actual_data_source]
| stats max(*) AS * BY actual_data_source
| table actual_data_source last_hour last_hour_percentage last_week last_week_percentage last_month last_month_percentage

[sbedard]

wow thanks! that worked!