Trend Data

[praja1983]

We have a data like below and we need to trend this based on one date and time.
Date Time KPI BEndTime App Time Infra_Avg OverallCustomer
3/26/2017 1:10:00 KPI1 0 0.1 0 1.8
3/26/2017 1:10:00 KPI2 0 0.1 0 1.95
3/26/2017 10:10:00 KPI1 0 0.1 0 1.29
3/26/2017 10:10:00 KPI2 1.66 1.7 0.48 3.44
3/27/2017 1:10:00 KPI1 1.93 1.95 1.66 5.29
3/27/2017 1:10:00 KPI2 1.95 1.95 0.57 3.58
3/28/2017 2:55:00 KPI1 1.03 1.2 1.03 3.38
3/28/2017 2:55:00 KPI2 1.31 1 0.21 2.06
. .
. .
. .

we have the test data like above for each test we are doing. Consider the test executed on 03/26/2017 at 1:10:00 will be the baseline. We need to compare the other tests timings with this baseline and display how much % improved or degraded in each layer. Can someone help me on this, on how to do it?

[somesoni2]

Thanks @DalJeanis for that.

Give this a try. Showing trend of percent difference of each metrics over each KPI. Again everything this before | chart is amazing search which Daljeanis has provided to generate sample data and query assumes that you get a field _time with correct value (same as Date Time of yours).

| gentimes start=-1
 | eval mydata="3/26/2017:1:10:00 KPI1 0 0.1 0 1.8!!!!3/26/2017:1:10:00 KPI2 0 0.1 0 1.95!!!!3/26/2017:10:10:00 KPI1 0 0.1 0 1.29!!!!3/26/2017:10:10:00 KPI2 1.66 1.7 0.48 3.44!!!!3/27/2017:1:10:00 KPI1 1.93 1.95 1.66 5.29!!!!3/27/2017:1:10:00 KPI2 1.95 1.95 0.57 3.58!!!!3/28/2017:2:55:00 KPI1 1.03 1.2 1.03 3.38!!!!3/28/2017:2:55:00 KPI2 1.31 1 0.21 2.06" | makemv delim="!!!!" mydata | mvexpand mydata 
 | makemv mydata
 | eval datetime=mvindex(mydata,0) | eval KPI =mvindex(mydata,1) | eval BEndTime =mvindex(mydata,2) | eval AppTime=mvindex(mydata,3) | eval Infra_Avg=mvindex(mydata,4) | eval OverallCustomer=mvindex(mydata,5)
 | eval _time=strptime(datetime,"%m/%d/%Y:%H:%M:%S")
 | table _time KPI BEndTime AppTime Infra_Avg OverallCustomer 
| chart max(BEndTime) as BEndTime max(AppTime) as AppTime max(Infra_Avg) as Infra_Avg max(OverallCustomer) as OverallCustomer by _time KPI | streamstats current=f window=1 values(*) as *_prev | foreach *_prev [eval "<<FIELD>>"=if(isnull('<<FIELD>>'),0,coalesce(('<<MATCHSTR>>'-'<<FIELD>>')*100/'<<FIELD>>',0))] | table _time *_prev | rename *_prev as *

[praja1983]

thanks @DalJeanis and @somesonei2..... This helps. Currently we are storing all the data in CSV lookup table. Consider all the fields Date, Time, KPI, BEndTime, App Time, Infra_Avg, OverallCustomer are different columns in the lookup file. How we need to read the data from the CSV lookup file ?

Thanks,
Raja

[somesoni2]

YOu need to replace (in above query) everything before the chart command with your lookup table fetch. Like this

| inputlookup your_lookup.csv 
| chart max(BEndTime) as BEndTime max(AppTime) as AppTime max(Infra_Avg) as Infra_Avg max(OverallCustomer) as OverallCustomer by _time KPI | streamstats current=f window=1 values(*) as *_prev | foreach *_prev [eval "<<FIELD>>"=if(isnull('<<FIELD>>'),0,coalesce(('<<MATCHSTR>>'-'<<FIELD>>')*100/'<<FIELD>>',0))] | table _time *_prev | rename *_prev as *

[DalJeanis]

All right. This puts your sample data in. I changed the field names to have detail_* prefixes to make some of the coding more convenient.

| makeresults 
| eval mydata="3/26/2017:1:10:00 KPI1 0 0.1 0 1.8!!!!3/26/2017:1:10:00 KPI2 0 0.1 0 1.95!!!!3/26/2017:10:10:00 KPI1 0 0.1 0 1.29!!!!3/26/2017:10:10:00 KPI2 1.66 1.7 0.48 3.44!!!!3/27/2017:1:10:00 KPI1 1.93 1.95 1.66 5.29!!!!3/27/2017:1:10:00 KPI2 1.95 1.95 0.57 3.58!!!!3/28/2017:2:55:00 KPI1 1.03 1.2 1.03 3.38!!!!3/28/2017:2:55:00 KPI2 1.31 1 0.21 2.06" | makemv delim="!!!!" mydata | mvexpand mydata 
| makemv mydata
| eval datetime=mvindex(mydata,0), KPI =mvindex(mydata,1), detail_BEndTime =mvindex(mydata,2), detail_AppTime=mvindex(mydata,3), detail_Infra_Avg=mvindex(mydata,4), detail_OverallCustomer=mvindex(mydata,5)
| eval _time=strptime(datetime,"%m/%d/%Y:%H:%M:%S")
| table _time KPI detail*
| sort 0 _time 
...

Based on the above, this next set calculates the percentage change from baseline (changeoverallpct) or from the prior event (changepriorpct)

...
| sort 0 _time
| eventstats earliest(detail_*) as earliest_* by KPI
| streamstats latest(detail_*) as prior_* current=false window=2 global=true  by KPI
| foreach detail_* 
    [ 
    eval prior_<<MATCHSTR>> =coalesce('prior_<<MATCHSTR>>','detail_<<MATCHSTR>>') 
    | eval changeprior_<<MATCHSTR>>=('<<FIELD>>'-'prior_<<MATCHSTR>>') 
    | eval changepriorpct_<<MATCHSTR>> =('changeprior_<<MATCHSTR>>' / if('prior_<<MATCHSTR>>' = 0,1,'prior_<<MATCHSTR>>'))
    | eval changepriorpct_<<MATCHSTR>> =round(100*changepriorpct_<<MATCHSTR>>,2)
    | eval changeoverall_<<MATCHSTR>>=('<<FIELD>>'-'earliest_<<MATCHSTR>>') 
    | eval changeoverallpct_<<MATCHSTR>> =('changeoverall_<<MATCHSTR>>' / if('earliest_<<MATCHSTR>>' = 0,1,'earliest_<<MATCHSTR>>'))
    | eval changeoverallpct_<<MATCHSTR>> =round(100*changeoverallpct_<<MATCHSTR>>,2)
    ]

Or, on the other hand, based on the same input, this calculates the trend by simple moving average. In this case it is coded as the sma2, since we only had three points in the data.

...
| sort 0 _time
| stats avg(*) as * by KPI _time
| outputcsv dmjtemp1.csv
| stats count by KPI
| table KPI
| eventstats count as KPIcount
| rename KPI as mapKPI
| map  search="|inputcsv dmjtemp1.csv 
    | search KPI=$mapKPI$ | sort 0 _time 
    | trendline sma2(detail_AppTime) as sma3_AppTime 
    | trendline sma2(detail_BEndTime) as sma2_BEndTime 
    | trendline sma2(detail_Infra_Avg) as sma2_Infra_Avg 
    | trendline sma2(detail_OverallCustomer) as sma2_OverallCustomer"

---

If you have more than 10 KPIs, then you'll need to add a maxsearches= parameter to the map command.

[DalJeanis]

Here's run-anywhere to load his test data

| makeresults 
| eval mydata="3/26/2017:1:10:00 KPI1 0 0.1 0 1.8!!!!3/26/2017:1:10:00 KPI2 0 0.1 0 1.95!!!!3/26/2017:10:10:00 KPI1 0 0.1 0 1.29!!!!3/26/2017:10:10:00 KPI2 1.66 1.7 0.48 3.44!!!!3/27/2017:1:10:00 KPI1 1.93 1.95 1.66 5.29!!!!3/27/2017:1:10:00 KPI2 1.95 1.95 0.57 3.58!!!!3/28/2017:2:55:00 KPI1 1.03 1.2 1.03 3.38!!!!3/28/2017:2:55:00 KPI2 1.31 1 0.21 2.06" | makemv delim="!!!!" mydata | mvexpand mydata 
| makemv mydata
| eval datetime=mvindex(mydata,0),KPI =mvindex(mydata,1),BEndTime =mvindex(mydata,2),AppTime=mvindex(mydata,3),Infra_Avg=mvindex(mydata,4),OverallCustomer=mvindex(mydata,5)
| eval _time=strptime(datetime,"%m/%d/%Y:%H:%M:%S")
| table _time KPI BEndTime AppTime Infra_Avg OverallCustomer

[somesoni2]

Trend of single column or multiple columns (from

[praja1983]

we need to trend multiple column App time, infra time, back end time and customer time. How can we do this?