Compare the output of two searches and timechart the latest output only if different than the the older output

[robrang558]

I have two timecharts that only hit on http status code of 500 (one for the past hour and one for the same hour but last week). I want to display the value of the past hour only if it differs from the value of the same hour of last week. I believe that using stdev is the way to go but am unable to figure out exactly how to place it to get it to work (append/join the searches together then test or if it can be done in one search). The final result that I am looking for is a timechart with the hits of the status code of 500 only if the past hour's output is different than the same hour of last week. The main search that I am working with is as follows:

index=myindex sourcetype=mysourcetype field1=myfield1 http_status="500" field2!="what_i_dont_want" | timechart count by field2 limit=20 useother=false | sort -count

Im not sure if the following would work at getting what I want to see but looking through some other answers similar to what I want, I believe this should work but I do not receive any output in the statistics tab for some reason:

index=myindex sourcetype=mysourcetype field1=myfield1 http_status="500" field2!="what_i_dont_want"  earliest=-60m@m latest=now | timechart count AS TodayLastHour by field2 limit=20 useother=false | appendcols [search index=myindex sourcetype=mysourcetype field1=myfield1 http_status="500" field2!="what_i_dont_want"  earliest=-169h@h latest=-168h@h | timechart count AS LastWeekLastHour by field2 limit=20 useother=false] | where TodayLastHour != LastWeekLastHour | timechart count by TodayLastHour limit=20 useother=false

I plan on visualizing the chart as a linechart and am not sure if there is a way to show a linechart that contains only differences (If the values are the same as last week, dont show).

[robrang558 · Accepted solution]

Using union as a multisearch and comparing the output of the two searches seemed to have worked best for my needs. I was able to create a line chart off of the final timechart which only outputted the servers that were different from the same time period last week.

| union [search index=myindex sourcetype=mysourcetype field=myfield1 http_status="500" field2!="what_i_dont_want" earliest=-169h@h latest=-168h@h 
| timechart count AS LastWeek by field2 limit=20 useother=false]  [search NOT index=myindex sourcetype=mysourcetype field=myfield1 http_status="500" field2!="what_i_dont_want" earliest=-60m@m latest=now 
| timechart count AS Today by field2 limit=20 useother=false 
| fields _time field2 Today LastWeek 
| timechart count by Today limit=20 useother=false 
| where Today != LastWeek]

I tried it without the NOT in the second search and without the "where Today != LastWeek" but the output is not what it should be. After testing, I notice that the above search gives me the output from the last hour that is different than the output for the same hour a week ago. Once again, I would like to thank the other answers given as they helped me figure this out.

[richgalloway]

When you finish testing, please come back and accept an answer. It will help future readers.

[woodcock]

I completely do not get what you are trying to do but this search should show you all of the pieces of the puzzle and allow you to craft your own solution. This makes the entire line disappear if the last/latest value is the same as the one from a week prior:

index=_internal sourcetype=*d
| rename sourcetype AS field2
| timechart span=1h count BY field2 limit=20 useother=false
| untable _time field2 count
| eventstats max(_time) AS last_time BY field2
| eval compare_time = last_time - (7 * 24 * 60 * 60)
| eval last_value=if(_time = last_time, count, null())
| eval compare_value=if(_time = compare_time, count, null())
| eventstats values(*value) AS *value BY field2
| where compare_value != last_value
| timechart span=1h avg(count) AS count BY field2 limit=20 useother=false

[robrang558]

This one is the closet to what I am trying to do but not quite there. I should mention that I am looking at a group of servers that are represented by field2 (not the splunk internals) and only want the timechart value (eventually a line chart) to show on the dashboard panel if the value (http status code 500) for the last hour (whenever ran) is different than the value for the same time-frame one week ago. If the values are the same, I am not looking to have the value charted. I am comparing values for the past hour against a week ago and if it is different, I want to see it in the chart (eventually alert on it). I will tinker with this one and see if I can get it to work the way that I need. Thanks again.

[Lamar]

Take a look at timewrap.

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Timewrap

It should give you what you need.

[Lamar]

Here is an example of how you might achieve the results you're looking for:

<search>
| timechart count 
| timewrap h 
| search 1hour_before!=latest_hour

EDIT: Just to be clear on this search. The timespan you search is important...meaning, if you want to check the current hour against the last hour you need to open your search to the last two hours.

[robrang558]

I have found a different use for this search and am going to run it by the group that I work with to see if we can implement it they way that I think it will work for us. Thank you for your assistance with this.

[robrang558]

Hi and thanks for the answer. I edited the title as I guess the previous title was a bit misleading. I am looking to compare the output of a timechart the last hour when the search is ran to the output of a timechart for the same hour the previous week and looking to output the timechart for the past hour only for the systems that have different output from the previous week. Playing with timewrap, I do see how I will be able to output a timechart I am unable to get it to work properly. I use series=exact and time_format=relative_time(now(),"-1w") but Im not sure if the output is merging the two timeperiods or combining over the week as the output is not similar to either the past 60 minutes search or the same time period last week. do you happen to have an example that is similar to what I am trying to do so that I can try to compare and work off of that? Once again, thank you for your answer and info that I'm sure I will be able to use in the future.