Splunk Search

Search results for sparkline null despite there being event values

hrecker
New Member

I'm trying to create a sparkline following the magnitude example from https://docs.splunk.com/Documentation/Splunk/7.3.0/Search/Addsparklinestosearchresults

My search is:

index="stats" | stats sparkline(avg("Search Ops")), avg("Search Ops")

My data is from a CSV file which looks like this:
Date, Time, Search Ops, Modify Ops
2019/07/22,13:41:50.004,8000,4000

When I run the above search the sparkline returns null, despite there being 500+ events returned, and a returned valid value for avg("Search Ops").

When I instead do this search:

 index="medium-dsstats-zeros" | stats sparkline avg("Search Ops")

I do get a sparkline, buts values are the numbers of events/lines in the CSV, not the values of the "Search Ops" field.

How can I get a sparkline of Search Ops values?

0 Karma
1 Solution

rbechtold
Communicator

It isn't a perfect answer, but I've found a solution that seems to work for me.

I've created a dataset for you that should mimic what you have and added comments in the search so that you can follow along with what's going on. Try seeing how it works, and then try to apply it to your search.

Copy and paste this directly into your Splunk:

| gentimes start=-10 end=1 increment=5m
| rename starttime AS _time
| eval date = strftime(_time, "%Y/%m/%d")
| eval time = strftime(_time, "%H:%M:%S")
| table date time 
| eval randomizer = random()%2
| eval so = random()%4000 + 4000
| eval "Modify Ops"  = random()%2000 + 2000
| eval "Search Ops" = if(so > 5600 AND randomizer = 0, so - (random()%2000 + 3600), if(so > 5600 AND randomizer = 1, so + random()%2000 + 5000, so))
`comment("Adding a comment here to let you know you don't have to worry about anything above this line, I'm just generating a dataset to work with")`

| fields date time "Search Ops" "Modify Ops"
| rename "Search Ops" AS search_ops, "Modify Ops" AS modify_ops
`comment("Working with spaces in field names is a pain. Let's re-name these fields to use underscores instead until we're done working with the data.")`
| eval datetime = date." ".time
| eval _time = strptime(datetime, "%Y/%m/%d %H:%M:%S")
`comment("I'm not sure if you have a _time field, but in the data you provided I'm only seeing "Date" and "Time" fields. In order for the sparkline to work, we'll need a proper _time field. The two pipes above should create that field for us.")`
| table _time search_ops modify_ops
| bucket _time span=1m
`comment("For some reason the only way I can get it to work is to bin the time. Right now I'm putting the time in 1 minute buckets, but you can change this to suit your needs")`
| stats sparkline(avg(search_ops),10m) AS "Average Modify Ops Sparkline", avg(search_ops) AS "Average Modify Ops"
`comment("specify a time range in the second part of the sparkline command to get an average across that timeframe. (change 10m to 30m/1h/1d)")`

I hope this helps!

View solution in original post

0 Karma

rbechtold
Communicator

It isn't a perfect answer, but I've found a solution that seems to work for me.

I've created a dataset for you that should mimic what you have and added comments in the search so that you can follow along with what's going on. Try seeing how it works, and then try to apply it to your search.

Copy and paste this directly into your Splunk:

| gentimes start=-10 end=1 increment=5m
| rename starttime AS _time
| eval date = strftime(_time, "%Y/%m/%d")
| eval time = strftime(_time, "%H:%M:%S")
| table date time 
| eval randomizer = random()%2
| eval so = random()%4000 + 4000
| eval "Modify Ops"  = random()%2000 + 2000
| eval "Search Ops" = if(so > 5600 AND randomizer = 0, so - (random()%2000 + 3600), if(so > 5600 AND randomizer = 1, so + random()%2000 + 5000, so))
`comment("Adding a comment here to let you know you don't have to worry about anything above this line, I'm just generating a dataset to work with")`

| fields date time "Search Ops" "Modify Ops"
| rename "Search Ops" AS search_ops, "Modify Ops" AS modify_ops
`comment("Working with spaces in field names is a pain. Let's re-name these fields to use underscores instead until we're done working with the data.")`
| eval datetime = date." ".time
| eval _time = strptime(datetime, "%Y/%m/%d %H:%M:%S")
`comment("I'm not sure if you have a _time field, but in the data you provided I'm only seeing "Date" and "Time" fields. In order for the sparkline to work, we'll need a proper _time field. The two pipes above should create that field for us.")`
| table _time search_ops modify_ops
| bucket _time span=1m
`comment("For some reason the only way I can get it to work is to bin the time. Right now I'm putting the time in 1 minute buckets, but you can change this to suit your needs")`
| stats sparkline(avg(search_ops),10m) AS "Average Modify Ops Sparkline", avg(search_ops) AS "Average Modify Ops"
`comment("specify a time range in the second part of the sparkline command to get an average across that timeframe. (change 10m to 30m/1h/1d)")`

I hope this helps!

0 Karma

hrecker
New Member

I appreciate the detailed response! This worked for me. It's not clear to me why using bin/bucket was needed for this, but it does work.

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 ...