Subsearch

[yifatcy]

Hi,

I've been trying for hours and nothing works, so I figure you might help me out.

I have the following very long query:

| tstats SUM(requested_cpus) as requested_cpus, SUM(reserved_ram) as reserved_ram, SUM(requested_ram) as requested_ram, SUM(used_ram) as used_ram, SUM(compute_ram_total) as compute_ram_total, count as agg_field_seen WHERE (index=monitor (host="$queuename$") fs_group=$fsgroup$ project=$project$ site=$site$            (slave_resource{} IN ("*")) (NOT slave_resource{} IN ("___VALUE_NONE___"))     ( host_state="normal" OR host_state="full" OR host_state="ovrld" OR host_state="sick" OR host_state="susp" OR  host_state="base" OR host_state="ready")     )
          OR (index=ncstat_monitor (host="$queuename$") fs_group=$fsgroup$ project=$project$ site=$site$ (compute_slave_res{} IN ("*")) (NOT compute_slave_res{} IN ("___VALUE_NONE___"))     ( host_state="normal" OR host_state="full" OR host_state="ovrld" OR host_state="sick" OR host_state="susp" OR  host_state="base" OR host_state="ready") ) BY _time, site, fs_group span=15min | eval query_enabled=1
                | eventstats sum(agg_field_seen) AS sum_agg_field_seen BY fs_group
                | sort 0 - sum_agg_field_seen
                | streamstats dc(fs_group) AS rank
                | eval agg_field_ranked=if(rank <= 50 - 1, 'fs_group', "Other")
                | rename agg_field_ranked as fs_group
          | stats SUM(reserved_ram) as reserved_ram, SUM(requested_ram) as requested_ram, SUM(used_ram) as used_ram, SUM(compute_ram_total) as compute_ram_total, SUM(requested_cpus) as requested_cpus BY _time, site, fs_group
| eval slots=max(reserved_ram/32,requested_cpus) | eval full_fsgroup=site.":".fs_group 
          | timechart span=15min limit=50 partial=false MAX(slots) as Slots BY full_fsgroup

In addition, I have another query from different index:

`p_flow("*",dv)` "***" reg_name=*$reg$* event_type=flow | eval fairshare = coalesce(fairshare, fsgroup) | table  fairshare | dedup  fairshare

 The issue is, I would like to limit the results to match only the output of the fairshare field from the second query.

Please notice that fairshare in the first query called fs_group

I've been trying many options from different past answers and nothing seem to be working. Please assist and be blessed forever.

[richgalloway · Accepted solution]

At the risk of suggesting something you've already tried, have you tried this?

| tstats SUM(requested_cpus) as requested_cpus, SUM(reserved_ram) as reserved_ram, SUM(requested_ram) as requested_ram, SUM(used_ram) as used_ram, SUM(compute_ram_total) as compute_ram_total, count as agg_field_seen WHERE (index=ncstat_monitor (host="$queuename$") [`p_flow("*",dv)` "***" regression_name=*$regression$* event_type=flow | eval fs_group = coalesce(fairshare, fsgroup) | fields fs_group | dedup  fs_group | format ] project=$project$ site=$site$            (slave_resource{} IN ("*")) (NOT slave_resource{} IN ("___VALUE_NONE___"))     ( host_state="normal" OR host_state="full" OR host_state="ovrld" OR host_state="sick" OR host_state="susp" OR  host_state="base" OR host_state="ready")     )
          OR (index=ncstat_monitor (host="$queuename$") fs_group=$fsgroup$ project=$project$ site=$site$ (compute_slave_res{} IN ("*")) (NOT compute_slave_res{} IN ("___VALUE_NONE___"))     ( host_state="normal" OR host_state="full" OR host_state="ovrld" OR host_state="sick" OR host_state="susp" OR  host_state="base" OR host_state="ready") ) BY _time, site, fs_group span=15min | eval query_enabled=1
...

[yifatcy]

Perfect, the "format" at the end did the job.

Just added [search `p_flow......... ] and it worked!

Thanks