timechart stats creates extra row

[marksheinbaum]

I am running the following query for a single 24 hour period. I was expecting a single summary row result. Not sure why the result is split across 2 rows. Here's the query:

index=federated:license_master_internal source=*license_usage.log type=Usage pool=engineering_pool
| eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h)
| eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s)
| eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx)
| where like(h, "%metfarm%" ) OR like(h, "%scale%")
|eval h=rtrim(h,".eng.ssnsgs.net")
|eval env=split(h,"-")
|eval env=mvindex(env,1)
|eval env=if (like(env,"metfarm%"),"metfarm",env)
|eval env=if (like(env,"sysperf%"),"95x",env)
|eval env=if(like(env,"gs02"),"tscale",env)
| timechart span=1d sum(b) as b by env
| foreach * [eval <<FIELD>>=round('<<FIELD>>'/1024/1024/1024, 3)]
|addtotals

[marksheinbaum]

Thanks for the solution. However, there is an additional case to be handled. Your solution handles the 1 day timeframe. However, we also want to see totals for timeframes > 1 day by day, i.e., license usage by environment by day. Is there a way to make the display of the stats dashboard conditional depending on the selected time frame? 

[bowesmana · Accepted solution]

There are many possible ways of choosing time, so how do you want it to work if

a) user selects from 00:00 yesterday to now - (bit more than one day)
b) last 24 hours - with end time as "now" not beginning of minute, so tiny bit more than one day
c) yesterday + day before to now, 2.xx days

and other permutations of part days.

Using timechart would be the general way to go, which will handle the majority of cases, but then if you can define the conditions for the display of a single row, then all you need to do is set a token which contains this as the token value

<set token="use_single_row">| addcoltotals | tail 1 | fields - _time</set>

and then in your search, do

...
| timechart span=1d sum(b) as b by env
$use_single_row$

and to determine your conditions of the time range selected, make a search at the start of your XML like this

<search>
  <query>
| makeresults
| addinfo
| eval gap=if(info_max_time - info_min_time < (86400+60), 1, 0)
  </query>
  <earliest>$time_picker.earliest$</earliest>
  <latest>$time_picker.latest$</latest>
  <done>
    <eval token="use_single_row">if($result.gap$=1, "| addcoltotals | tail 1 | fields - _time", "")</eval>
  </done>
</search>

which assumes your time picker token is called time_picker. This will run when the user selects the time and calculates the gap between search start and end and if it's less then 24 hours will set the new token to sum the fields.

You could also do this as two different panels and show one or the other depending on the gap using the <panel depends="$token$"> construct, but that depends on how you end up displaying these results.

[bowesmana]

If you run timechart across a 24 hour window and you specify @d as the time bucket, it will count by the day,  so say you run the search at 10:00 am, it will give you the 24 hours window of yesterday from 10:00am to midnight and today from midnight to 10am.

Currently your query will give you 

yesterday_time env1_count env2_count ... total
today_time env1_count env2_count ... total

what is your intention to show this information - is time relevant? If you are just looking for a sum of (b) for each env, then just use stats, e.g.

| stats sum(b) as sum by env
| transpose 0 header_field=env