Returning a new constant field in a subquery

Comments

4 comments

  • Official comment
    Avatar
    Matt Sullivan

    assuming you want to just show results from category1 where Id from category 2 is present, you might opt to move the subquery to the scoping portion of the query, and presuming category1 does not have a field named singleId, you must use the keywords modifier on the subquery. Net result is something like below

    _sourceCategory=category1
    [subquery:_sourceCategory=category2
    | parse "Id * " as singleId
    | compose singleId keywords]

    Comment actions Permalink
  • Avatar
    Alex Kanjirakattu Jose

    Im facing the same problem where a subquery within a where clause is not returning the field I want

    //Base Query 

    | where (value_from_base_query/[subquery from=(-15m):(scoping for subquery ) | timeslice 15m
    | count as transaction_count by _timeslice | compare with timeshift 7d 4 avg as AVG|sum(transaction_count_AVG) as AVERAGE|compose AVERAGE ] )<50

     

    Returns --> "Subquery failed with error: Field AVERAGE not found, please check the spelling and try again." 

     

    How does subquery work with where /if ?

    1
    Comment actions Permalink
  • Avatar
    Matt Wilson

    Having the same issue.  In my case, I'm trying to produce a single value for a dashboard.  I want to chart the rate between two different count_distinct results.  But Sumo Logic doesn't seem to allow math operations upon aggregate operation results.

    <query>
    | parse store_id ...
    | parse order_id ...
    | count_distinct(order_id) as total_orders, count_distinct(store_id) as total_stores

    // this doesn't work
    // | (total_orders / total_stores) as avg_orders_per_store 

    So I decided to try using a subquery to pass the totals out, instead.  That works fine when I follow the guidance here and run the query with a compose: 

    // this works, but stops working as soon as I wrap it in a subquery
    | save temp/store_orders
    | compose total_orders, total_stores

    ... but that breaks when I wrap it.  The error message I get as soon as I put it in a subquery is the same as Alex's: 
    "Subquery failed with error: Field total_orders,total_stores not found, please check the spelling and try again."

    1
    Comment actions Permalink

Please sign in to leave a comment.