How to display multiple _time span in single report?

[Steve_A200]

Hi,

I am struggling with an SPL.  I am trying to create a report which lists the Online status of specific Site/location pending if there is a message received from it.

I need the Online (or Offline) status to be group in a daily format which I have achieved so far with the below SPL.  However, the challenge for me is, when a Site/location goes "Offline", I would like to know the exact hour:min that Last communication was logged.  Currently, the Last_Communication Column is showing me the Date but time is 00:00:00 which I know is not true, I need the exact hour/min the last event got logged for that specific day if it was "Online".

Current SPL:

| from datamodel:"mydatamodel"
| bin _time span=1d
| search  field1="comm_message" 
| eval Online_Status=if(like(Location_field,"xyz"),1,0)
| stats sum(Online_Status) AS Message_Counts by _time
| eval Online_Status=if(Message_Counts=0,"OFFLINE", "ONLINE")
| eval Last_Communication=if(Online_Status="ONLINE",(_time), "OFFLINE")
| convert ctime(Last_Communication)

Any help would be greatly appreciated.

Thanks

[Steve_A200]

Thank you richgalloway.  I did get a display of the time in hours, however, it was showing me the time for events in general, not specific to that location "xyz".

[richgalloway]

If you only want to see results for a certain location then the query will need to filter out other locations.

| from datamodel:"mydatamodel"
| eval time=_time
| bin _time span=1d
| search  field1="comm_message" 
| where like(Location_field,"xyz")
| stats count AS Message_Counts, min(time) as time by _time
| eval Online_Status=if(Message_Counts=0,"OFFLINE", "ONLINE")
| eval Last_Communication=if(Online_Status="ONLINE",(time), "OFFLINE")
| convert ctime(Last_Communication)

[Steve_A200]

Thanks @richgalloway .

With the latest suggests SPL, I modified the min to latest, so it looks like latest(time), and it is displaying the time correctly/accurately.

However, I only see ONLINE records, and there OFFLINE rows are no longer visible.  What would like to see is within the specified date range selection, which calendar days are offline status (i.e. no events recorded) and which says were there event records (will be tagged as Online).

[richgalloway]

The bin command "rounds off" timestamps so that is why you see "00:00:00" for the time.  The solution is use two time fields - one for grouping that you'll pass to bin and the other for reporting the exact time of an event.

| from datamodel:"mydatamodel"
| eval time=_time
| bin _time span=1d
| search  field1="comm_message" 
| eval Online_Status=if(like(Location_field,"xyz"),1,0)
| stats sum(Online_Status) AS Message_Counts, min(time) as time by _time
| eval Online_Status=if(Message_Counts=0,"OFFLINE", "ONLINE")
| eval Last_Communication=if(Online_Status="ONLINE",(time), "OFFLINE")
| convert ctime(Last_Communication)