topic Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations? in Splunk Search

How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

jward6004 — Tue, 29 Sep 2020 10:56:08 GMT

I have recently started indexing a private log generated from a Hostmon URL check. The Hostmon check runs during M-F business hours and returns the following basic log information :

[9/8/2016 10:48:55 AM]      sitename.com    Host is alive   18 ms   URL request 27061

I've added the extracted fields for 'site', 'state' 'response_time', 'test_type' 'bytes' but now I want to build reporting around the data and am not very experienced using charting searches with Splunk. The 'state' field will return data that is simplistic as 'Host is alive', 'Host is down', or 'Out of schedule'.

Can someone help me understand how to pipe in a timechart avg of the 'state' field values so I can add it to a scheduled report for how often my site was available?

Example of a search that I was trying is:

index=main host=* sourcetype=Hostmon site=* state=* | timechart span=1d avg(state) as Site_Availability

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

dbcase — Fri, 09 Sep 2016 21:02:44 GMT

try this

index=main host=* sourcetype=Hostmon site=* state=* | stats count values(state) by _time|timechart span=1d avg(state) as Site_Availability

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

sundareshr — Fri, 09 Sep 2016 21:11:31 GMT

Try this

index=main host=* sourcetype=Hostmon site=* state=* | bin span=1h _time | stats count by _time state | timechart span=1d avg(count) as Site_Availability by state

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

jward6004 — Fri, 09 Sep 2016 21:49:15 GMT

For testing purposes I added a 'host is down' entry in the log but my pie chart is showing three data groups on the virtualization :

Host is alive, Host is alive, and other

I'm looking for the chart to show basically 99% host is alive and for that one entry that is Host is down shouldn't it show a sliver for that 1%?

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

dbcase — Fri, 09 Sep 2016 21:52:12 GMT

You wouldn't use timechart with a pie chart representation. Time charts are suited for line charts and the like.

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

sundareshr — Fri, 09 Sep 2016 22:03:52 GMT

You will not be able to do a timechart and display on a piechart. For a piechart, you can do this

index=main host=* sourcetype=Hostmon site=* state=* | stats count by state

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

dbcase — Fri, 09 Sep 2016 22:13:02 GMT

if you still need to express the average of the count try this

 index=main host=* sourcetype=Hostmon site=* state=* | stats count by state|stats avg(count) as Average by state

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

jward6004 — Fri, 09 Sep 2016 22:40:22 GMT

Awesome! thanks guys. One last virtualization question, can you help me with the creating a bar graph for showing the hourly/weekly /monthly and yearly performance.

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

dbcase — Fri, 09 Sep 2016 22:44:17 GMT

That goes back to sundareshr's earlier answer

try this

 index=main host=* sourcetype=Hostmon site=* state=* | bin span=1h _time | stats count by _time state | timechart span=1d avg(count) as Site_Availability by state

change the span=1h to span=1w for 1week or span=1m for 1month or span=1y for 1year

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

dbcase — Fri, 09 Sep 2016 22:45:25 GMT

sorry that should be the timechart span

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

aaraneta_splunk — Sat, 10 Sep 2016 00:44:27 GMT

Hi @jward6004 - If your original question has been answered, don't forget to resolve the original post by clicking "Accept" below the answer. Also, be sure to upvote any comments by @sundareshr and @dbcase that you found helpful.

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

jward6004 — Tue, 29 Sep 2020 11:00:19 GMT

Thank you dbcase! I'm trying to get a total number of tests or sum.. and then divide total tests by total success (host is alive) and total failures (host is down) using EVAL.

This the current query I'm using for the past week of test data

index=main host=* sourcetype=Hostmon site=* state=* | stats count by _time state | timechart span=1w count as Site_Availability by state

This bar graph is showing total tests of either 'host is alive' or 'host is down' for the past week but I'm trying to display a more granular output of the data.

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

dbcase — Mon, 12 Sep 2016 22:06:07 GMT

I don't quite understand is there anyway you could send a result (mock up) of what you are looking for?

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

jward6004 — Mon, 12 Sep 2016 22:18:53 GMT

Here's a screengrab for my panels now.

https://www.dropbox.com/s/p3d5sbc0rcvjaq3/plscreengrab.JPG?dl=0

It's showing the number of times that the event showed 'host is alive' and 'host is down' but I don't really care to display the number of tests in my graph. I'd like to create two new fields using EVAL for the expected values of the field 'state' then use the graph to report on those new fields

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

jward6004 — Mon, 12 Sep 2016 23:31:17 GMT

maybe displayed as percentage for each of those fields over the timechart span= 1w

Re: How to use timechart average of a field from a simple Hostmon URL Check log file to create visualizations?

dbcase — Tue, 13 Sep 2016 01:08:01 GMT

Still not quite understanding but.....

I think you would need eventstats to get your totals

https://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/Eventstats

http://blogs.splunk.com/2014/04/01/search-command-stats-eventstats-and-streamstats-2/

or possibly accum...

https://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Accum

sorry for the vagueness, I'm still not getting quite what you are looking for.