Dashboards & Visualizations

xyseries, reporting on multiple data series confusion

pde23
Explorer

I have log entries that contain, among other things, fields called AcctID and exec_time. I have a user who wants to do, essentially:

sourcetype=statslog | timechart count, avg(exec_time) by AcctID

Since I know this to not be directly possible in 4.1, I went to the strategy laid out in http://www.splunk.com/base/Documentation/4.1.6/User/ReportOfMultipleDataSeries. My search ends up being:

host=*prod* sourcetype=statslog "exec=getSingleAvailability" exec_time > 0 
| stats count as cnt, avg(exec_time) as avgexec by AcctID 
| eval s1="count avgexec" 
| makemv s1 | mvexpand s1 
| eval yval=case(s1=="count",cnt,s1=="avgexec",avgexec) | eval series=AcctID+":"+s1 

And I get results as expected, like:

     AcctID cnt  avgexec     s1        series        yval 
1   7490728 23  391.826087  count   7490728:count   23
2   7490728 23  391.826087  avgexec 7490728:avgexec 391.826087
3   5459551 22  193.954545  count   5459551:count   22
4   5459551 22  193.954545  avgexec 5459551:avgexec 193.954545

But when I add the final | xyseries _time,series,yval to the search, I get "No results found"

What am I missing?

Tags (2)
1 Solution

sideview
SplunkTrust
SplunkTrust

I just walked through the docs myself using some access data use cases and it looks to me like there are mistakes in the documentation.

The docs give this example:

index=application_servers 
| stats sum(handledRequests) as hRs, avg(sessions) as ssns by source 
| eval s1="handledReqs sessions" 
| makemv s1 | mvexpand s1 
| eval yval=case(s1=="handledReqs",hRs,s1=="sessions",ssns) 
| eval series=host+":"+s1 
| xyseries _time,series,yval

The main mistake is that the stats should be by source, _time not just by source. Without a _time field coming out of the stats clause, the xyseries would indeed yield no results because there wouldnt be any _time fields at that point.

There's also a second mistake although it's minor and it doesnt seem to have tripped you up at all -- the eval series=host+":"+s1 should be eval series=source+":"+s1

I think you were following the docs perfectly, but the docs themselves got garbled at some point. It happens.

So try this:

host=*prod* sourcetype=statslog "exec=getSingleAvailability" exec_time > 0 
| stats count as cnt, avg(exec_time) as avgexec by AcctID, _time
| eval s1="count avgexec" 
| makemv s1 | mvexpand s1 
| eval yval=case(s1=="count",cnt,s1=="avgexec",avgexec) 
| eval series=AcctID+":"+s1 
| xyseries _time, series, yval

View solution in original post

sideview
SplunkTrust
SplunkTrust

I just walked through the docs myself using some access data use cases and it looks to me like there are mistakes in the documentation.

The docs give this example:

index=application_servers 
| stats sum(handledRequests) as hRs, avg(sessions) as ssns by source 
| eval s1="handledReqs sessions" 
| makemv s1 | mvexpand s1 
| eval yval=case(s1=="handledReqs",hRs,s1=="sessions",ssns) 
| eval series=host+":"+s1 
| xyseries _time,series,yval

The main mistake is that the stats should be by source, _time not just by source. Without a _time field coming out of the stats clause, the xyseries would indeed yield no results because there wouldnt be any _time fields at that point.

There's also a second mistake although it's minor and it doesnt seem to have tripped you up at all -- the eval series=host+":"+s1 should be eval series=source+":"+s1

I think you were following the docs perfectly, but the docs themselves got garbled at some point. It happens.

So try this:

host=*prod* sourcetype=statslog "exec=getSingleAvailability" exec_time > 0 
| stats count as cnt, avg(exec_time) as avgexec by AcctID, _time
| eval s1="count avgexec" 
| makemv s1 | mvexpand s1 
| eval yval=case(s1=="count",cnt,s1=="avgexec",avgexec) 
| eval series=AcctID+":"+s1 
| xyseries _time, series, yval

pde23
Explorer

That's the ticket. Thanks, Doctor Nick!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Shouldn't the _time be binned before that first stats command?

0 Karma

sideview
SplunkTrust
SplunkTrust

gerald's the best. 😃

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

docs are fixed.

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