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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...