Overall our requirement is that " We are passing a lookup where both Primary and Secondary devices defined , if only both stopped sending the events then we need to display the hosts "
this is the query I prepared , but not giving the exact requirement , can you optimize , help .
| tstats count max(_time) AS latest_event_time where index=firewall sourcetype="cisco:ftd" [| inputlookup Firewall_list.csv | table Primary | Rename Primary AS host] groupby host
| append [|inputlookup Firewall_list.csv | table Primary | Rename Primary AS host | eval count=0]
| stats sum(count) as count max(latest_event_time) AS latest_event_time by host |rename host as Pri
|append [| tstats count max(_time) AS latest_event_time where index=firewall sourcetype="cisco:ftd" [| inputlookup Firewall_list.csv | table Secondary | Rename Secondary AS host] groupby host
| append [|inputlookup Firewall_list.csv | table Secondary | Rename Secondary AS host | eval count=0]
| stats sum(count) as count max(latest_event_time) AS latest_event_time by host |rename host as Sec]
Host are in lookup
... View more