add a count column in the table

[ringbbg]

Hi All.

I want to add an additional column in the table to display how many times a particular host in the set time frame had a distinct hit based on the search query below. Can anyo0ne advise how I can add it to the table?

    <search>
      <query>index=nm "RPD_OSPF_NBR*" host=$hostname2$ syslog_message!="*ExStart due to*" syslog_message!="*UI_CMDLINE_READ_LINE*" | dedup syslog_message | table _time, host, ip, state, syslog_message | sort 0 -_time</query>
      <earliest>$_time.earliest$</earliest>
      <latest>$_time.latest$</latest>
    </search>
    <option name="count">20</option>

[woodcock · Accepted solution]

Like this:

index=nm "RPD_OSPF_NBR*" host=$hostname2$ syslog_message!="*ExStart due to*" syslog_message!="*UI_CMDLINE_READ_LINE*" | stats count max(_time) AS _time BY host ip state syslog_message | sort 0 -_time

[ringbbg]

thanks! is there a way to rearrange the order of the column? with the syntax above, the _time column is at the rightmost part of the table. I would like it to be in the left most side, or the first column. Thanks

[woodcock]

Just add | table Your Preferred Order Of Field Names Here. Be sure to click Accept to close the question if the answer worked for you.