How to build multiple timecharts in dashboard from one field?

[Taruchit]

Hello All,

I have a lookup file with multiple columns: fieldA, fieldB, fieldC.

I need to publish timechart for each value under fieldA based on search conditions of fieldB and fieldC.

Thus, I want your guidance to understand how to build multiple timecharts from same field by reading the required field values from lookup file.

Any inputs and information would be very helpful.

Thank you

Taruchit

[Taruchit]

| used the below approach so far it seemed to have worked. But if I want to compute statistics like mean, median, that does not seem to work. 

index=custom_index
earliest=-4w@w latest=@d
|search
[
|inputlookup append=true table1.csv
|where relative_time(now(),"-1d@d")
|dedup fieldA
|where fieldB<fieldC
|fields + fieldA
|fields - _time
]
|timechart span=1d sum(xxx) AS xxx BY fieldA

To visualize each timechart separately, I used Trellis option in Visualization.

Thus, if you can help if there is more better method to achieve the result it would be very helpful.

And if you could help on computing statistical values such as mean, median in each timechart, that would be very helpful.

Thank you 

[PickleRick]

More words please.

Your problem description is relatively vague and your search only adds to confusion I must say.

All I understand is that you have some lookup and some data in the index. I have no idea what is the relation between the indexed events and the lookup and what you want to get as the result.

Generally, you can't create a single timechart with multiple aggregations. You could bin your data and then simply do stats over _time to get multiple "timecharted" functions but then you'd have to aggregate them somehow.

[Taruchit]

Hi @PickleRick,

I have an index with multiple fields. I need to plot the timechart for values based on fieldA. 

However, I need to pick the selected values based on a search condition from lookup file for fieldA and plot their timechart using the data fetched from the index.

Please share if the above explains the case or if you need any more details.

I was able to build multiple timecharts using the SPL shared, however, I need to add statistical value like median or mean in each timechart and I am looking for help on the same.

Thank you

[PickleRick · Accepted solution]

It would be best if you provided us with some mockup data and expected result.

Selecting based on values from the lookup requires a subsearch indeed, similarily to what you already did (but you don't need to specify append=t in case of a simple inputlookup; you need it only if you use that command later in the pipeline to append the results from the lookup to the earlier results).

Again - you can't use two separate aggregations in a single timechart command.

So you can't do, for example:

timechart span=1h sum(A) avg(A) 

 You need to do two separate timechart commands.

Or - as I said, do

| bin _time span=1h
| stats sum(A) as sum avg(a) as avg by _time

If you want to combine them now to a single time-based table you'd need to do something like

| stats values(sum) as sum values(avg) as (avg) by _time

It gets tricky if you try to split that by additional field.

Depending on your desired outcome you might want to either dynamically create fields or use some xyseries/untable tricks.

[Taruchit]

Thank you @PickleRick for your inputs.

I was able to build my solution using it as below: -

index=custom_index
earliest=-4w@w latest=@d
|search
[
|inputlookup append=true table1.csv
|where relative_time(now(),"-1d@d")
|dedup fieldA
|where fieldB<fieldC
|fields + fieldA
|fields - _time
]
|bin span=1d _time
|stats sum(xxx) AS xxx BY fieldA _time
|eventstats median(xxx) AS median_xxx BY fieldA