How can I get max number of hours without events for all indexes, myindex=* ?

[vl951f]

I have the summary index to record hourly event count for all device (de_count). I have the following search to get max number of hours without events for myindex=router:

myindex=router

| bucket _time span=1h  | stats sum(de_count) as event_count by _time  (get hourly event count by _time)

| search event_count!=0 | delta _time as mydelta ( get max number of hours without events)

| eval number_of_zeros=floor(mydelta/3600.00)-1

| stats max(number_of_zeros)

| rename "max(number_of_zeros)" as maxgap

| table myindex maxgap

 How can I get max number of hours without events for all indexes, myindex=* ?

[richgalloway]

Have you tried adding by index to the stats commands?

[vl951f]

I tried the following. It didn't work. Looks like it put all results in one line.  I only got the result for one myindex, and not showing myindex in the table.

myindex=router

| bucket _time span=1h  | stats sum(de_count) as event_count by _time,myindex  (get hourly event count by _time)

| search event_count!=0 | delta _time as mydelta ( get max number of hours without events)

| eval number_of_zeros=floor(mydelta/3600.00)-1

| stats max(number_of_zeros) by myindex

| rename "max(number_of_zeros)" as maxgap

| table myindex maxgap

[vl951f]

I tried the following search, didn't work

myindex=*

| bucket _time span=1h  | stats sum(de_count) as event_count by _time,myindex  (get hourly event count by _time)

| search event_count!=0 | delta _time as mydelta ( get max number of hours without events)

| eval number_of_zeros=floor(mydelta/3600.00)-1

| stats max(number_of_zeros) by myindex

| rename "max(number_of_zeros)" as maxgap

| table myindex maxgap

[vl951f · Accepted solution]

Got it working by changing "by _time,myindex" to "by myindex,_time".