This is result of a query that reflects license consumption by day
Index | 3/2/2021 | 3/3/2021 | 3/4/2021 | 3/5/2021 | 3/6/2021 | 3/7/2021 | 3/8/2021 |
index01 | 0.018006 | 0.018128 | 0.018065 | 0.018035 | 0.017944 | 0.017985 | 0.018042 |
index02 | 0.014985 | 0.009444 | 0.054803 | 0.010401 | 0.006807 | 0.005035 | 0.008998 |
index03 | 3.468919 | 3.674277 | 3.786565 | 3.133193 | 2.151094 | 2.548173 | 4.531934 |
index04 | 0.084911 | 0.082637 | 0.090785 | 0.062404 | 0.012795 | 0.031198 | 0.084129 |
I'm try to compute a daily difference so we can easily spot variance/trend with the result looking something like this:
Index | 3/2/2021 | 3/3/2021 | dif day 1 | 3/4/2021 | dif day 2 | 3/5/2021 | dif day 3 | 3/6/2021 | dif day 4 | 3/7/2021 | dif day 5 | 3/8/2021 | dif day 6 |
index01 | 0.018006 | 0.018128 | -0.00012 | 0.018065 | 0.00006 | 0.018035 | 0.00003 | 0.017944 | 0.00009 | 0.017985 | -0.00004 | 0.018042 | -0.00006 |
index02 | 0.014985 | 0.009444 | 0.005541 | 0.054803 | -0.04536 | 0.010401 | 0.04440 | 0.006807 | 0.00359 | 0.005035 | 0.00177 | 0.008998 | -0.00396 |
index03 | 3.468919 | 3.674277 | -0.20536 | 3.786565 | -0.11229 | 3.133193 | 0.65337 | 2.151094 | 0.98210 | 2.548173 | -0.39708 | 4.531934 | -1.98376 |
index04 | 0.084911 | 0.082637 | 0.002274 | 0.090785 | -0.00815 | 0.062404 | 0.02838 | 0.012795 | 0.04961 | 0.031198 | -0.01840 | 0.084129 | -0.05293 |
The query I started from is below & I've tried 20 ways to Sunday get a difference column, but no joy
index=_internal source=*license_usage.log* type=Usage earliest=-7d@d latest=@d host=licenseserver
| eval GB=round(b/1024/1024/1024,6)
| bucket span=1d _time
| eval Time=strftime(_time,"%m/%d/%y")
| chart sum(GB) AS volume_GB over Time by idx limit=0
| transpose 0 column_name=Index header_field=Time
I'm not married to chart or transpose, its just where it all started.
Any suggestions?
Try something like this
index=_internal source=*license_usage.log* type=Usage earliest=-7d@d latest=@d host=licenseserver
| eval GB=round(b/1024/1024/1024,6)
| bucket span=1d _time
| stats sum(GB) as volume_GB by idx _time
| streamstats window=2 list(volume_GB) as diff by idx
| eval diff=tonumber(mvindex(diff,0)) - tonumber(mvindex(diff,1))
| eval volume_GB=mvappend(volume_GB,diff)
| xyseries idx _time volume_GB
| foreach * [ eval <<FIELD>>_diff=if(mvcount('<<FIELD>>') > 1, mvindex('<<FIELD>>', 1), null) | eval <<FIELD>>=mvindex('<<FIELD>>',0) ]
| transpose 0 header_field=idx
| eval column=if(match(column,"_"),strftime(mvindex(split(column,"_"),0),"%m/%d/%Y diff"),strftime(column,"%m/%d/%Y"))
| transpose 0 header_field=column
that did the trick... thanks a bunch.
Try something like this
index=_internal source=*license_usage.log* type=Usage earliest=-7d@d latest=@d host=licenseserver
| eval GB=round(b/1024/1024/1024,6)
| bucket span=1d _time
| stats sum(GB) as volume_GB by idx _time
| streamstats window=2 list(volume_GB) as diff by idx
| eval diff=tonumber(mvindex(diff,0)) - tonumber(mvindex(diff,1))
| eval volume_GB=mvappend(volume_GB,diff)
| xyseries idx _time volume_GB
| foreach * [ eval <<FIELD>>_diff=if(mvcount('<<FIELD>>') > 1, mvindex('<<FIELD>>', 1), null) | eval <<FIELD>>=mvindex('<<FIELD>>',0) ]
| transpose 0 header_field=idx
| eval column=if(match(column,"_"),strftime(mvindex(split(column,"_"),0),"%m/%d/%Y diff"),strftime(column,"%m/%d/%Y"))
| transpose 0 header_field=column