Fetching data from lookup file

[JagsP]

Hello everyone , 

I have the below query which is fetching data for a particular index but i also want few fields from a look up file say ABC.csv and columns are 'Salary' and 'Date' from that . I am trying to fetch it but the data is coming as blank . Please help :

index=*infra* metric_label ="Host : Reporting no data" 
| bin span=6m@m metric_value as 6_min_data
| stats
     count(eval(metric_value=0)) as uptime
     count(eval(metric_value=1)) as downtime
     by 6_min_data, source_host
| eval total_uptime = uptime*360 | eval total_dowtime = downtime*360
| eval total_uptime = if(isnull(total_uptime),0,total_uptime)
| eval total_downtime = if(isnull(total_dowtime),0, total_dowtime)
| eval avg_uptime_perc =  round((total_uptime/(total_uptime+total_downtime))*100 ,2)
| eval avg_downtim_perc =  round((total_downtime/(total_uptime+total_downtime))*100,2)
| eval total_uptime = tostring(total_uptime, "duration")
| eval total_downtime = tostring(total_downtime, "duration")
| rename "total_uptime" as "Total Uptime", "total_downtime" as "Total Downtime", avg_uptime_perc as "Average uptime in %", avg_downtim_perc as "Average Downtime in %" source_host as "Source Host"
| table  "Source Host"  "Total Uptime" "Total Downtime" "Average uptime in %" "Average Downtime in %"

[ITWhisperer]

It is not clear how the content of the lookup relate to the fields in your search - please can you expand a bit more?

[JagsP]

Hi @ITWhisperer , The look up file contains the data like server OS and server environment (production or non -prod) which i will be needing in the search results along with the data coming for the mentioned index hence i have to fetch data from lookup file as well. I hope i cleared your doubt. 

[ITWhisperer]

Assuming "Source Host" contains the same values as server, you could try this

| lookup ABC.csv server AS "Source Host"

[JagsP]

@ITWhisperer  This is my final query but still fields are not coming from look up file . Source_host and key field in look up file is same .

index=*infra* metric_label ="Host : Reporting no data"
| bin span=6m@m metric_value as 6_min_data
| stats
count(eval(metric_value=0)) as uptime
count(eval(metric_value=1)) as downtime
by 6_min_data, source_host
| eval total_uptime = uptime*360 | eval total_dowtime = downtime*360
| eval total_uptime = if(isnull(total_uptime),0,total_uptime)
| eval total_downtime = if(isnull(total_dowtime),0, total_dowtime)
| eval avg_uptime_perc = round((total_uptime/(total_uptime+total_downtime))*100 ,2)
| eval avg_downtim_perc = round((total_downtime/(total_uptime+total_downtime))*100,2)
| eval total_uptime = tostring(total_uptime, "duration")
| eval total_downtime = tostring(total_downtime, "duration")
| rename "total_uptime" as "Total Uptime", "total_downtime" as "Total Downtime", avg_uptime_perc as "Average uptime in %", avg_downtim_perc as "Average Downtime in %" source_host as "Source Host"
| lookup *_common_lookup_topology-technical-detail_001.csv key as source_host
| table key type system "Source Host" "Total Uptime" "Total Downtime" "Average uptime in %" "Average Downtime in %"

[ITWhisperer]

Try doing the lookup before you rename the field you are using for the lookup!

Also, does your lookup file really start with a "*", if so, try renaming it to something without a wildcard in it.

[JagsP]

@ITWhisperer  Now tried this but still no luck 😞

index=atn*infra*tier3*
| bin span=6m@m metric_value as 6_min_data
| stats
count(eval(metric_value=0)) as uptime
count(eval(metric_value=1)) as downtime
by 6_min_data, source_host
| eval total_uptime = uptime*360 | eval total_dowtime = downtime*360
| eval total_uptime = if(isnull(total_uptime),0,total_uptime)
| eval total_downtime = if(isnull(total_dowtime),0, total_dowtime)
| eval avg_uptime_perc = round((total_uptime/(total_uptime+total_downtime))*100 ,2)
| eval avg_downtim_perc = round((total_downtime/(total_uptime+total_downtime))*100,2)
| eval total_uptime = tostring(total_uptime, "duration")
| eval total_downtime = tostring(total_downtime, "duration")
| lookup atn_common_lookup_topology-technical-detail_001.csv key as source_host
| rename "total_uptime" as "Total Uptime", "total_downtime" as "Total Downtime", avg_uptime_perc as "Average uptime in %", avg_downtim_perc as "Average Downtime in %" source_host as "Source Host"
| table "type" system "Source Host" "Total Uptime" "Total Downtime" "Average uptime in %" "Average Downtime in %"

[ITWhisperer]

I presume atn_common_lookup_topology-technical-detail_001.csv has fields "key", "type" and "system"?

Do you have proper access to the lookup file

| inputlookup atn_common_lookup_topology-technical-detail_001.csv