Splunk Search

How to create a timechart using a root search with data models and pivots?

EricLloyd79
Builder

According to the documentation here, http://docs.splunk.com/Documentation/Splunk/6.0.2/Knowledge/Designdatamodelobjects,

"You should create root search objects for any searches that do not map directly to Splunk events. In other words, searches that involve input or output that is not in the format of an event. This includes searches that:

Make use of transforming commands such as stats, chart, and timechart. Transforming commands organize the data they return into tables rather than event lists."

Can someone please give me an example or an idea of how to create a timechart using a root search with data models and pivots? Root search does not extract the _time timestamp so I don't see how I can create a pivot chart with a root search. Is there a way I can get the timestamp attributes into the root search so I can create a timechart? I am very confused.

Thanks for any help.
Eric

1 Solution

lguinn2
Legend

Well, first you can have _time as part of a root search in a pivot. All you have to do is to include _time as one of the fields after the by

But - just because you want to create a timechart doesn't mean that you need a root search. You could have a root event object - no problem. Then open the pivot and choose the object. For the Split Rows, choose _timeand choose the interval. For the Column Values, choose the statistic that you want. Then you can click on the Line Chart in the black bar on the left, and go from there...

View solution in original post

spammenot66
Contributor

This seems to work in 6.3. Just make sure you are passing in _time into the pivot query.

| pivot DataModel_AccessService perf count(TPS) AS hits SPLITROW _time AS _time PERIOD AUTO SPLITROW host AS hostname | timechart sum(hits) by hostname 
0 Karma

lguinn2
Legend

Well, first you can have _time as part of a root search in a pivot. All you have to do is to include _time as one of the fields after the by

But - just because you want to create a timechart doesn't mean that you need a root search. You could have a root event object - no problem. Then open the pivot and choose the object. For the Split Rows, choose _timeand choose the interval. For the Column Values, choose the statistic that you want. Then you can click on the Line Chart in the black bar on the left, and go from there...

lguinn2
Legend

Well, first - if you want a timeline then you need to use _time (or some time field!) When you use the timechart command, it takes care of this for you automatically.

Try this

sourcetype=xxx-yyy (FOO OR BAR)
| eval var=(ABC - (DEF + GHI + FOO + BAR))
| timechart span=30m sum(var) by host

You don't need the join at all, as far as I can see... (And sorry about the updates, but I just noticed that both searches use the same sourcetype - even less cause for a subsearch!)

0 Karma

sumitnagal
Path Finder

Lisa,

I have same issue, from pivot I am not able to plot the timechart graph, though by _time I am able to get require graph.

Not Working
| pivot DataModel_AccessService perf count(TPS) AS "tps" sum(execTime) AS
"execTime" SPLITROW _time AS _time PERIOD AUTO SPLITROW host AS hostname|
timechart sum(execTime)
Working
| pivot DataModel_AccessService perf count(TPS) AS "tps" sum(execTime) AS
"execTime" SPLITROW _time AS _time PERIOD AUTO SPLITROW host AS hostname|
chart sum(execTime) by _time

Thanks,
Sumit

helge
Builder

I have exactly the same issue.

0 Karma

EricLloyd79
Builder

Here is an example of the query I am trying to do:

sourcetype=xxx-yyy FOO | join host [search sourcetype=xxx-yyy BAR] | eval var=(ABC - (DEF + GHI + FOO + BAR)) | timechart span=30m sum(var) by host

Im trying to divide the columns up by the host so not sure how to use "by _time" with this one in data models and generate a pivot from it. Thanks for all your help.

0 Karma

okrabbe
Explorer

lguinn, have you tried that? i tried adding _time to a root search and then the data model would not load in pivot.

0 Karma

EricLloyd79
Builder

I appreciate your feedback about the by _time. I will try that. In regards to using an root event I have tried that with this query but since it includes a join, I cannot use a root event. I posted a separate question regarding the join in a root event. Thanks again. I'll comment back on if it works or not.

0 Karma

okrabbe
Explorer

This doesn't seem to be what root search was designed to do.

Could you give an example of why you need to create a root search and have the _time field?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...