How to combine my two searches pulling data from two different indexes into one search?

[dhavamanis]

Need help.

We have two indexes and one index data stats as another index query input,

Over all cost calculation (first search) :

index=aws-bill Subscription="shared-identity" | stats sum(UnBlendedCost) as idxcost 

output:

idxcost 
60

Brand usage from another index (Second search):

index="idxweblog" source="/opt/apache2/logs/idxapi.nbcuni.com-access_log"   BRAND_NAME="*" | stats count as apicount by BRAND_NAME |sort + BRAND_NAME 

Output:

BRAND_NAME  apicount 
aaaaaaaaa      20
bbbbbb          5
ccccccccc       5

We want to split the idxcost to BRAND_NAME based on apicount,

Final results:

BRAND_NAME  apicount   brand_Cost
aaaaaaaaa      20          40
bbbbbb          5          10
ccccccccc       5          10

Can you please help us with how to write a single search to achieve the final result?

[sanjay_shrestha · Accepted solution]

aha.. add "search" keyword...
so... it would be like..

index="idxweblog" source="/opt/apache2/logs/idxapi.nbcuni.com-access_log" BRAND_NAME="*" | stats count as apicount by BRAND_NAME |sort + BRAND_NAME 
 |eventstats sum(apicount) as apicountsum
 |eval brand_Cost = apicount * [search index=aws-bill Subscription="shared-identity" | stats sum(UnBlendedCost) as idxcost| rename idxcost as query | fields query | head 1 ]/apicountsum

[dhavamanis]

Thank you so much. i just altered the query like this and its working fine.

index="idxweblogaws" source="/opt/apache2/logs/idxapi.nbcuni.com-access_log" BRAND_NAME="*" | stats count as apicount by BRAND_NAME |sort + BRAND_NAME
|eventstats sum(apicount) as apicountsum
|eval idxcost1 = [search index=aws-bill Subscription="shared-identity" | stats sum(UnBlendedCost) as idxcost| rename idxcost as query | fields query | head 1 ] | eval brand_Cost = round(apicount * (round(idxcost1,4) /apicountsum), 4)

[dhavamanis]

Now i am getting this error,

"Error in 'eval' command: The 'not' function is unsupported or undefined."

[sanjay_shrestha]

I tried similar query:

index=_audit|stats count AS C by user|eventstats sum(C) as tot 
| eval res = C * [search index=_audit|stats dc(user) as result
| rename result as query | fields query | head 1]/tot

and it worked. You can refer to same idea.

[sanjay_shrestha]

Is there any typo?

[sanjay_shrestha]

You can try like this:

index="idxweblog" source="/opt/apache2/logs/idxapi.nbcuni.com-access_log" BRAND_NAME="*" | stats count as apicount by BRAND_NAME |sort + BRAND_NAME 
|eventstats sum(apicount) as apicountsum
|eval brand_Cost = apicount * [index=aws-bill Subscription="shared-identity" | stats sum(UnBlendedCost) as idxcost| rename idxcost as query | fields query | head 1 ]/apicountsum

[dhavamanis]

i am getting error like "Unknown search command 'index'."