How to write a search to list all hosts and their count of triggered alerts from a CSV file, even if the alert count is 0?

[Honey0308]

Hello All,

I have obtained the list of all alerts via REST API search as:

| rest /servicesNS/-/-/saved/searches search="is_scheduled=1" | table title | dedup title 

Second, I have obtained the list of all triggered alerts via search:

search index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" alert_actions=email earliest = -24h | table savedsearch_name | rename savedsearch_name as title | dedup title

Also, there is a CSV file consisting of a list of hosts along with the list of alerts associated with the host.

The task we need to perform is to write a search that returns the list of all hosts along with the count of triggered alerts.
The problem we are facing is that the hosts which have no alerts triggered (but have configured alerts) are not showing up in the table, because those hosts are not being connected with the triggered alerts.

We are trying to solve the problem by retrieving the list of hosts from the configured alerts list and the count from triggered alerts list, but so far have been unsuccessful to devise a search.

I hope the issue is clear to understand.

Please suggest a suitable way to solve the problem.

Any help is appreciated.
Thanks in advance.

[sundareshr]

Try this

| rest /servicesNS/-/-/saved/searches search="is_scheduled=1" | table title | dedup title | appendcols [search index="_internal" sourcetype="scheduler" thread_id="AlertNotifier*" alert_actions=email earliest = -24h | rename savedsearch_name as title | stats count by title] | fillnull count | lookup csvfileconsistingofalistofhosts.csv title as titlefieldincsv OUTPUT host