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!

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...