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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...