query help inner query

[surekhasplunk]

| mstats max(_value) as Bits_in_sec where index=ehealth (host="SC2CLK-CLOUD-CFD-VDC2" OR host="SC2BJV-CLOUD-CFD-VDC2" OR host="DC2A4-CLOUD-CFD-VDC2") AND metric_name="*in"   
    by host,metric_name, info span=1d | rex field="metric_name" "(?P<Interface>.*)\_(?P<InOut>[^\_]+)$" |  eval BandW=trim('info',"Bandwidth:")
| eval "BW_in"=round(Bits_in_sec/300/BandW*100,5) | appendcols [| mstats max(_value) as Bits_out_sec where index=ehealth (host="SC2CLK-CLOUD-CFD-VDC2" OR host="SC2BJV-CLOUD-CFD-VDC2" OR host="DC2A4-CLOUD-CFD-VDC2") AND metric_name="*out"
    by host,metric_name, info span=1d | rex field="metric_name" "(?P<Interface>.*)\_(?P<InOut>[^\_]+)$" |  eval BandW=trim('info',"Bandwidth:")
| eval "BW_out"=round(Bits_out_sec/300/BandW*100,5)] |rename BandW as BandWidth| table _time host Interface BandWidth Bits_in_sec BW_in BW_out Bits_out_sec | rename BW_in as "BW_in%" BW_out as "BW_out%" | sort - "BW_out%" |head 20

In my above query i want to replace (host="SC2CLK-CLOUD-CFD-VDC2" OR host="SC2BJV-CLOUD-CFD-VDC2" OR host="DC2A4-CLOUD-CFD-VDC2") with value from a csv file.
I have a mapping file where i have a HostName column
|inputlookup preview_infra.csv | search InfraName="FRA-CLOUD GTS" | fields HostName
I get below output for above query.
host="SC2CLK-CLOUD-CFD-VDC2" OR host="SC2BJV-CLOUD-CFD-VDC2" OR host="DC2A4-CLOUD-CFD-VDC2"

Now how to add this query to above query . Please help. urgent.

in place hos that host =bla bla i want to replace host=$HostName$

[koshyk]

Quite simple,
Assuming |inputlookup preview_infra.csv | search InfraName="FRA-CLOUD GTS" | fields HostName gives you the host, just add this as host within the search like below

 | mstats max(_value) as Bits_in_sec where index=ehealth  [|inputlookup preview_infra.csv | search InfraName="FRA-CLOUD GTS" | fields HostName| rename HostName as host]  metric_name="*in" 

Just to add , your query is too complex. You can make it much simpler imo

[surekhasplunk]

hi @koshyk,

Thanks for the reply but am not getting the desired result if i explicitly give host=A then only am getting the result.