Combine multiple queries

Follow

Mouli Veera

• November 18, 2020 13:03

We have 2 different queries. Need to combine them.
The request is If the sumologic quota exceeds[50Gb] then return which hosts are logging high

Query for sumologic quota:
------------------------------------
_index=sumologic_audit _sourceName=VOLUME_QUOTA _sourceCategory=account_management "Budget" "last reset" "Exceeded"
| parse "Budget * Budget with field value" as env
| timeslice by 1d
| where env="XXXX"



High logging host info:
-----------------------------
* _index=sumologic_volume|where _sourceCategory="sourcecategory_volume"
| parse regex "(?<sourcename>\"[^\"]+\")\:\{\"sizeInBytes\"\:(?<bytes>\d+),\"count\"\:(?<count>\d+)\}" multi
| timeslice 1d
| bytes/1024/1024/1024 as gbytes
| parse field=sourcename "*/*/*" as product, env, component
| where env="XXX"
| sum(gbytes) as gbytes by sourcename, _timeslice
| format("%.0f",gbytes) as gbytes
| total gbytes as total
| num(gbytes)
| sort gbytes
| limit 5
| where total > 50 and gbytes > 0

Please advise.

0

• Official comment

  

  Raghu Murthy

  • November 27, 2020 01:02

  Hi Mouli,

   

  You can take advantage of the subquery functionality at this link

  https://help.sumologic.com/05Search/Subqueries

  You can capture the first query as a child or subquery -for example

  _index=sumologic_audit _sourceName=VOLUME_QUOTA _sourceCategory=account_management "Budget" "last reset" "Exceeded"
  | parse "Budget * Budget with field value" as env
  | timeslice by 1d
  | compose env

  Above query for example returns output such as

  ((env="Blah Blah"))

  When the above subquery is embedded into the parent query into a where statement, the results in the parent query will be limited to specific env field values

   

  Your final query could look like this subject to testing

   _index=sumologic_volume|where _sourceCategory="sourcecategory_volume"
  | parse regex "(?<sourcename>\"[^\"]+\")\:\{\"sizeInBytes\"\:(?<bytes>\d+),\"count\"\:(?<count>\d+)\}" multi
  | timeslice 1d
  | bytes/1024/1024/1024 as gbytes
  | parse field=sourcename "*/*/*" as product, env, component

  
  | where [subquery: _index=sumologic_audit _sourceName=VOLUME_QUOTA _sourceCategory=account_management "Budget" "last reset" "Exceeded"
  | parse "Budget * Budget with field value" as env
  | timeslice by 1d
  | compose env ]

  | sum(gbytes) as gbytes by sourcename, _timeslice
  | format("%.0f",gbytes) as gbytes
  | total gbytes as total
  | num(gbytes)
  | sort gbytes
  | limit 5
  | where total > 50 and gbytes > 0

   

  Hope that helps

   

  Kind regards

  Raghu

   

  Comment actions Permalink

Please sign in to leave a comment.