SPL

[SN1]

| makeresults
| eval sourcetype=split("BBCN-Kunshan,BSCN-Suzhou,BBSP-Malasiya,BTCN-Tianjin,BXCN-Xian,BCCN-Suzhouheadquarters,BCIT-Italy", ",")
| mvexpand sourcetype
| eval index="bbs-firewall"
| join type=left index sourcetype
[ | tstats count
where index="bbs-firewall" earliest=-24h
by index sourcetype ]
| eval count=coalesce(count, 0)
| where count=0
| stats values(sourcetype) as sourcetypes by index
| eval message ="Sourcetypes With 0 Events Last 24hr"
| table index sourcetypes message

this is the search that shows which sourcetype is having 0 events last 24 hr . Now i also wanted to show the time of the last log that came in splunk .

[PickleRick]

Adding to @richgalloway 's remarks about your problem being incorrectly formulated (maybe you wanted something else but didn't word it properly), this is a very badly used join command. As a rule of thumb the join command is to be avoided whenever possible.

Your search can be equally well rewritten without it.

Oh, and if you limit yourself to just one index with tstats' where condition there's no point of adding index to the by clause.

So effectively your initial search might be swapped around and rewritten as

| tstats count where index="bbs-firewall" earliest=-24h by sourcetype
| append
   [ |  makeresults
| eval sourcetype=split("BBCN-Kunshan,BSCN-Suzhou,BBSP-Malasiya,BTCN-Tianjin,BXCN-Xian,BCCN-Suzhouheadquarters,BCIT-Italy", ",")
| mvexpand sourcetype
| eval count=0 ]
| stats sum(count) as count by sourcetype
| ...

[richgalloway]

If there were no events in the last 24 hours then there is no last timestamp to display.

The only way to get the timestamp would be to join the current search with one that scans the logs for the most recent entry for each sourcetype in some larger time window (perhaps 30 days).