Returning a new constant field in a subquery

Follow

Matt Evans

• November 02, 2018 19:02
• Edited

I have two log statements I'm trying to compare. One statement I'm parsing to return a list of ids, the other I'm parsing to return a single id.

I would like to return a result set whenever a single id also matches an id in a list of ids.  I'm trying to use a subquery to do this and I think I have the logic right but it appears that I cannot return a custom field (using the as operator). 

_sourceCategory=category1  |  parse "Ids [*]" as ids 
| where ids matches toString([subquery:_sourceCategory=category2 | parse "Id * " as singleId 
| compose singleId])

This results in an error:

Subquery failed with error: Field singleId not found, please check the spelling and try again.

However if I change compose singleId to compose Message, it works, and by works I mean it doesn't error, however since I'm now passing Message and not singleid, I don't get expected results.

 

 

 

 

 

 

1

• Official comment

  

  Matt Sullivan

  • November 11, 2018 13:44

  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
• 

  Alex Kanjirakattu Jose

  • May 23, 2019 19:55

  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
• 

  Matt Wilson

  • July 14, 2021 14:40

  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
• 

  Matt Wilson

  • July 20, 2021 16:19

  Got some resolution for my issue here: 
  
  https://support.sumologic.com/hc/en-us/community/posts/4404270963351-Do-math-on-two-count-distinct-results?page=1#community_comment_4404631151127

  0

  Comment actions Permalink

Please sign in to leave a comment.