Reporting

dedup msg with and results only display host with the highest count in report

Contributor

Hello Friends!!!

In the results for a search, I would like to have only a single host with the highest count of the deduped 'MESSAGE' that contains the string "down". Under host, I would like to have it titled "Host w/ Highest Count". The idea should result in a table that only shows one host with the highest deduped "down" messages across many hosts so the report isn't 500 rows long with multiple hosts reporting the same message.

If posssible, I would like 'MostRecent' to include the most recent message based on the "date_" fields seen below
.

*The MostRecent format appears like this

'march-15 10:00:00 GMT'

Here's the search so far:

index=serversourcetype="server" MESSAGE="*" "down" | eval MostRecent= date_month."-".date_mday." ".date_hour.":".date_minute.":".date_second." GMT" | top limit=100 MostRecent,MESSAGE,host, | DEDUP MESSAGE

Here are the table columns in the result:
MostRecent | MESSAGE| host*(replace with 'Host w/ Highest Count')* | count | percent

Any help would be greatly appreciated!

0 Karma

Ultra Champion

I think that this might help you a little bit along the way

sourcetype=XXX index=YYY MESSAGE="* down *"
| stats dc(MESSAGE) as count first(MESSAGE) as MESSAGE first(date_month) as fdmo first(date_mday) as fdmd first(date_hour) as fdh first(date_minute) as fdmi first(date_second) as fds by host 
| sort - count | head 1 | eval MostRecent = fdmo."-".fdmd." ".fdh.":".fdmi.":".fds." GMT" 
| fields - fdmo fdmd fdh fdmi fds 
| rename host as "Host with the highest count"

If you can live with the time that splunk uses, you can skip most of the date_xxx stuff. Also I don't really understand what the percent is supposed to mean.

/k

0 Karma

Contributor

it still seems to be counting by the host.

0 Karma

Contributor

index=xx eventtype="error" | stats c(MESSAGE) as count

first(MESSAGE) as MESSAGE first(date_month) as fdmo first(date_mday) as

fdmd first(date_hour) as fdh first(date_minute) as fdmi first(date_second) as fds

by host | sort - count | head 100 | eval MostRecent = fdmo."-".fdmd."

".fdh.":".fdmi.":".fds." GMT" | fields - fdmo fdmd fdh fdmi fds | rename host as

"Host with the highest count" | dedup MESSAGE

0 Karma

Contributor

this is definately the right direction. I would like to see all the different messages in an error index so i changed the head 1 to head 100. it gave me about 80 rows. some of the messages were the same. I would like to count those messages, dedup them and have a host with highest count. here's where i'm at

0 Karma