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

[EricLloyd79]

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

[spammenot66]

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 

[lguinn2 · Accepted solution]

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]

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!)

[sumitnagal]

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]

I have exactly the same issue.

[EricLloyd79]

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.

[okrabbe]

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

[EricLloyd79]

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.

[okrabbe]

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?