I want to see the list of alerts which were fired yesterday and the hosts affected from respective alert ? Please help me with this

[sandyIscream]

In the results tab I want to see the columns for Alert name, Hosts affected for each alert, IP of that host, Triggered Time and Count

[cmerriman]

something like this might get you close:

index="_internal" sourcetype="scheduler" alert_actions=email
            | eval scheduled=strftime(scheduled_time, "%Y-%m-%d %H:%M:%S") 
            | eval dispatch_time=strftime(dispatch_time, "%Y-%m-%d %H:%M:%S") 
            | stats values(scheduled) as scheduled
                    values(dispatch_time) as dispatched
                    values(host) as host
                    values(status) as status
                    values(run_time) as run_time 
                    values(result_count) as result_count
                    values(sid) as sid
                    by _time,savedsearch_name |  sort -scheduled 

[sandyIscream]

This isn't what i was looking for. this will give the list of alerts which were fired yesterday but I want the hosts which are affected for each alert.