How to list of events per host, with heading per host, emailed in a report?

[mrs_whipple]

Hi there,

I'm a noob. I'm looking to generate a report containing a list of events per host for a specific timeframe (e.g. 5 mins), grouped by host, and with a heading per host, like this:

----------------------------------------
Host: host1.somedomain.com
----------------------------------------
2023-05-26T15:36:46.000001+10:00 [2023-05-26T15:36:46+10:00] host1.somedomain.com - kernel: <blah1>
2023-05-26T15:36:46.012345+10:00 [2023-05-26T15:36:46+10:00] host1.somedomain.com - kernel: <blah2>

----------------------------------------
Host: host2.somedomain.com
----------------------------------------
2023-05-26T15:36:46.004567+10:00 [2023-05-26T15:36:46+10:00] host2.somedomain.com - kernel: <blah3>
2023-05-26T15:36:46.005678+10:00 [2023-05-26T15:36:46+10:00] host2.somedomain.com - kernel: <blah4>

etc. etc.

I have got to the point where I'm able to generate a report containing all events for the timeframe using this search, but there is no grouping by host, and therefore no heading per host:

index=myindex
| sort 0 host, _time

Can anyone suggest how I might achieve the above?

Many thanks.

[isoutamo]

Hi

this will be quite unreadable as soon as you will get more events and hosts 😞

So what is your real issue which you try to solve with this query?

r. Ismo

[mrs_whipple]

Posting this reply again -- somehow I replied to my own post.

Yes, I'm anticipating quite large emails full of events. I'm thinking that another evolution of this might be to filter out uninteresting results by having a lookup table of events to be ignored... or something similar.

I'm essentially just wanting an emailed digest of events from host syslogs, sorted by host, in ascending order by time.

I've played around a bit more and got to this:

index=myindex
| sort 0 host, _time
| stats count as events, values(_raw) by host

This gives a table of results with 3 columns -- host, events and a list of raw events for the host. This is almost what I want, but it would be nice not to have the first two columns taking up space on the left, but instead to have that information as a heading for the list of events in a single column.

Thanks.

[ITWhisperer · Accepted solution]

Splunk is not really a report generating tool. Having said that, you can spoof it by manipulating the events to make it look like a report

index=myindex
| stats count as events values(_raw) by host
| eval row=mvrange(0,2)
| mvexpand row
| eval "values(_raw)"=if(row=0,"-----------------
Host: ".host."
-----------------",'values(_raw)')
| fields - host events row

Note that you don't need the sort since values() will sort for you and given that your events already start with a timestamp, which, when sorted lexicographically, will appear in time order, and stats will sort the hosts for you as this field is in the by clause.

[mrs_whipple]

@ITWhisperer , that's exactly what I'm after. Many thanks!