Splunk Search

How to create a time chart with the percentage difference between two searches over time?

pr0n
Explorer

In the search below I have appended two identical searches that are 1 week apart.
I would like to find the difference in percent between the two over time.
I am aware of the timewrap function but that's not exactly sure how it could help here.
A timechart of percentage difference would be ideal.

index="blah" earliest=-192h latest=-168h | setfields when='1 week ago' | eval _time = _time+604800 | append [search index="blah" earliest=-24h latest=now | setfields when='0 week ago'] 
0 Karma
1 Solution

pr0n
Explorer
index="blah" earliest=-169h latest=-168h | timechart count AS count_1weekago | appendcols
[search index="blah" earliest=-1h latest=now | timechart count AS count_now]
| eval DiffPercent = (count_now - count_1weekago) / count_1weekago * 100

This is what worked.

View solution in original post

0 Karma

pr0n
Explorer
index="blah" earliest=-169h latest=-168h | timechart count AS count_1weekago | appendcols
[search index="blah" earliest=-1h latest=now | timechart count AS count_now]
| eval DiffPercent = (count_now - count_1weekago) / count_1weekago * 100

This is what worked.

0 Karma

niketn
Legend

@pr0n you can try the following:

 <yourCurrentSearch>
| timechart count by when
| eval "diff %"=round((('0 week ago'-'1 week ago')/'0 week ago')*100,2)
| fillnull "diff %" value=0

Once you have diff % you can create a chart overlay to plot it on top of your existing output.

However, at the same time since append will run into sub-search limitation, you can try the multisearch command instead. Following is a run anywhere search based on Splunk's _internal index.

| multisearch 
    [ search index="_internal" earliest=-192h latest=-168h 
    | setfields when="1 week ago" 
    | eval _time = _time+604800] 
    [ search index="_internal" earliest=-24h latest=now 
    | setfields when="0 week ago"] 
| timechart count by when 
| eval "diff %"=round((('0 week ago'-'1 week ago')/'0 week ago')*100,2) 
| fillnull "diff %" value=0
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

pr0n
Explorer

"diff %" ends up being null (and thus 0) when I attempt your top method. After experimenting it seems that '0 week ago' and '1 week ago' don't reference anything. Unfortunately I don't have the ability to query our _internal index but I think I can structure this to keep it under 10k.

0 Karma

niketn
Legend

@pr0n if you have null values for current week and/or previous week, you can get null for diff% and hence 0, which is expected. Do accept the answer if you found this helpful.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

pr0n
Explorer

Null is not expected, there are definitely values there. I have made an answer post which was my solution.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...