Combining two results from different queries

Follow

Jordi Hoock

• November 11, 2019 08:47

Hello 

I would like to show two different results in a same chart. My idea is to search two different queries from two different sources but they are related to each other and i would like to show them (results as numbers) in the same chart

 

Is there any way to do that? 

 

These are my two queries and first returns 7 as count and the second returns count as 35
I would like to show them in the same chart like

query1 = 7

query2=35

 

"Logging CompanyResponse"
AND 
_source = "wpb-auth-dev"
|parse "CompanyId=*," as CompanyId
|parse "DefaultMotoProvider=*:" as DefaultMotoProvider
| where !(DefaultMotoProvider matches "null")
| count_distinct(CompanyId) as Companies_Have_Set_Default_Provider

"(updatePaymentSubmission method) Transaction Succeed"
"TransactionStatus=SUCCEEDED"

AND _source = "payments-dev"
| parse "invoiceCurrencyAmount=*," as PaidAmount 
| parse "invoiceCurrency=*," as Currency
| parse "servicerName=*," as Provider
| parse "applicationId=*," as ProductId
| parse "paymentMethod=*," as PaymentMethod
| parse "companyId=*," as CompanyId
| where PaymentMethod="card-Moto"

| count_distinct(CompanyId) as Unique_CompanyIds_Accepted_MotoPayment

0

• 

  Rick Jury

  • November 11, 2019 21:20

  You could try something like this example below. 

  (
  "Logging CompanyResponse"
  AND
  _source = "wpb-auth-dev"
  )
  OR
  (
  "(updatePaymentSubmission method) Transaction Succeed"
  "TransactionStatus=SUCCEEDED"
  AND _source = "payments-dev"
  )
  | parse "CompanyId=*," as CompanyId nodrop
  | parse "DefaultMotoProvider=*:" as DefaultMotoProvider nodrop
  | where !(DefaultMotoProvider matches "null" ) and _source = "wpb-auth-dev"

  /* parsing these probably pointless as not used in aggregate??
  | parse "invoiceCurrencyAmount=*," as PaidAmount nodrop
  | parse "invoiceCurrency=*," as Currency nodrop
  | parse "servicerName=*," as Provider nodrop
  | parse "applicationId=*," as ProductId nodrop
  | parse "paymentMethod=*," as PaymentMethod nodrop
  | parse "companyId=*," as CompanyId nodrop
  | where PaymentMethod="card-Moto" nodrop
  */

  | if (_source = "wpb-auth-dev","Companies_Have_Set_Default_Provider","Unique_CompanyIds_Accepted_MotoPayment") as type
  | count_distinct(CompanyId) by type

  0

  Comment actions Permalink
• 

  Jordi Hoock

  • November 12, 2019 01:02

  actually this returns only one count which is 

  Companies_Have_Set_Default_Provider

   

  but it doesn't return the 

  Unique_CompanyIds_Accepted_MotoPayment

   

  i think there must be something missing, the one you commented about parsing it is pointless, i guess we need those informations in order to get Unique_CompanyIds_Accepted_MotoPayment

   

  thanks in advance

  0

  Comment actions Permalink
• 

  Rick Jury

  • November 12, 2019 01:49

  make sure both parts of the query return results with something like:

  (
  "Logging CompanyResponse"
  AND
  _source = "wpb-auth-dev"
  )
  OR
  (
  "(updatePaymentSubmission method) Transaction Succeed"
  "TransactionStatus=SUCCEEDED"
  AND _source = "payments-dev"
  )
  | count by _source

   

  it may be the 2nd half of the OR is just busted.

   

  The only field you parse and use later is  parse "CompanyId=*," as CompanyId nodrop and for both types of log the parse statement is very similar  so it should parse fine from both.
  
  you could try changing that to:

  parse "ompanyId=*," as CompanyId nodrop

   

  in case the C vs c matters.

  0

  Comment actions Permalink

Please sign in to leave a comment.