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!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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