topic Re: How to table/chart over a period of time in Splunk Search

How to table/chart over a period of time

clintla — Tue, 26 Nov 2019 20:32:00 GMT

trying to calculate groupings of VMs capacity growth over time but a chart or table looks to be the best answer if you need to report on 100 VMs.

In a simplified data set per below
Date ,Name,Capacit Used
5/1/2019, VM1,100
5/1/2019, VM2,100
5/1/2019, VM4,450
6/1/2019, VM1,100
6/1/2019, VM2,140
6/1/2019, VM4,450
7/1/2019, VM1,105
7/1/2019, VM2,200
8/1/2019, VM1,110
8/1/2019, VM2,200
9/1/2019, VM1,110
9/1/2019, VM2,200
10/1/2019,VM1,110
10/1/2019,VM2,200
10/1/2019,VM3,100
11/1/2019,VM1,110
11/1/2019,VM2,200
11/1/2019,VM3,200

How can you search it so that if you search for 7/1/2019 through 11/1/2019 that the result would be tabled as

VM1 5GB
VM2 0GB
VM3 200GB

So this almost needs to be like a delta except that its clobbered by VMs that are not in the beginning or ending of the time range.

If a VM is created in the time range then its starting capacity should be 0

Re: How to table/chart over a period of time

yannK — Tue, 26 Nov 2019 20:40:24 GMT

If you extracted the fields, try to sum the capacity over the period, using a stats

mysearch | stats sum(capacity_used) by Name

If you want to see the usage evolution (not the sum of usage), timechart would be the best too but when using timechart and chart, the top 10 series are displayed only, you need to add a higher limit if you have 100 Vms

But the UI and graphs may not be able to show them.

Example

    mysearch | timechart span=1d max(capacity_used) AS totalUsage by Name limit=100

So I would recommend to use a stats instead to build a table.

  mysearch | bucket _time span=1d | stats max(capacity_used) AS totalUsageby _time Name

Re: How to table/chart over a period of time

clintla — Wed, 30 Sep 2020 03:08:43 GMT

I've tried a lot of variations but I need to figure out how to take the beginning time of capacity_used and subtract the ending time of capacity_used

Stuff like stats.. just adds up some capacity.

I was doing earliest(capacity_used) minus latest(capacity_used) but that gets clobbered by VM's that were not present either at the beginning or end of the time range.. like VM3 in my example

Re: How to table/chart over a period of time

yannK — Tue, 26 Nov 2019 22:01:13 GMT

if you have a period and want to measure the delta. try the streamstats.

    | bucket _time span=1d | stats max(capacity_used) AS daily_usage by _time Name| streamstats first(daily_usage) AS first_value last(daily_usage) As last_value by Name  window=2| eval delta=last_value - first_value

Re: How to table/chart over a period of time

woodcock — Tue, 26 Nov 2019 22:44:45 GMT

Like this (not sure about the units, though):

| makeresults | eval _raw="Date,Name,Capacity Used
5/1/2019,VM1,100
5/1/2019,VM2,100
5/1/2019,VM4,450
6/1/2019,VM1,100
6/1/2019,VM2,140
6/1/2019,VM4,450
7/1/2019,VM1,105
7/1/2019,VM2,200
8/1/2019,VM1,110
8/1/2019,VM2,200
9/1/2019,VM1,110
9/1/2019,VM2,200
10/1/2019,VM1,110
10/1/2019,VM2,200
10/1/2019,VM3,100
11/1/2019,VM1,110
11/1/2019,VM2,200
11/1/2019,VM3,200"
| multikv forceheader=1

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval _time = strptime(Date, "%m/%d/%Y")
| where _time >= strptime("7/1/2019", "%m/%d/%Y") AND _time <= strptime("11/1/2019", "%m/%d/%Y")
| stats sum(Capacity_Used) AS Capacity_Used BY Name
| eval Capacity_Used = Capacity_Used . "GB"

Re: How to table/chart over a period of time

to4kawa — Wed, 27 Nov 2019 12:27:59 GMT

| makeresults 
| eval _raw="Date ,Name,Capacit_Used
5/1/2019, VM1,100
5/1/2019, VM2,100
5/1/2019, VM4,450
6/1/2019, VM1,100
6/1/2019, VM2,140
6/1/2019, VM4,450
7/1/2019, VM1,105
7/1/2019, VM2,200
8/1/2019, VM1,110
8/1/2019, VM2,200
9/1/2019, VM1,110
9/1/2019, VM2,200
10/1/2019,VM1,110
10/1/2019,VM2,200
10/1/2019,VM3,100
11/1/2019,VM1,110
11/1/2019,VM2,200
11/1/2019,VM3,200"
| rex mode=sed "s/ //g"
| multikv
| table Date,Name,Capacit_Used
`comment("this is sample data")`
| eval _time=strptime(Date,"%m/%d/%Y") 
`comment("Adjust the period here if necessary")`
| stats earliest(_time) as first_time earliest(Capacit_Used) as first_value latest(_time) as last_time latest(Capacit_Used) as last_value
 range(Capacit_Used) as result by Name
| foreach *_time 
    [eval <<FIELD>> = strftime(<<FIELD>>,"%m/%d/%Y") ]
| eval result = result . "GB"

Hi, @clintla
Please evaluate the previous answer.

Re: How to table/chart over a period of time

clintla — Wed, 27 Nov 2019 14:17:33 GMT

Output from above
VM1 545GB
VM2 1000GB
VM3 300GB

This looks like its just adding up all the daily capacities reported.

Trying to look over any search & find a start/end time & track the growth. My example below was just from july to nov. havent found a way to make it work yet.

VM1 5GB
VM2 0GB
VM3 200GB

Think there is an easy way to just be able to pick any time frame & then track the growth of groups of VMs?

Re: How to table/chart over a period of time

clintla — Wed, 27 Nov 2019 14:20:26 GMT

I like the idea but still is clobbered by if a VM is not in the start or end of a selected time frame.

I was using earliest & latest which works great if a VM is present at the start and end of a selected time frame.

If a VM is added in the middle of a selected time frame & its 500GB then its earliest is 500GB. (should be 0) which makes the growth calculation inaccurate.

Re: How to table/chart over a period of time

clintla — Wed, 30 Sep 2020 03:09:08 GMT

This is running into the same problem I was having
Name first_time first_value last_time last_value result
VM1 05/01/2019 100 11/01/2019 110 10GB
VM2 05/01/2019 100 11/01/2019 200 100GB
VM3 10/01/2019 100 11/01/2019 200 100GB
VM4 05/01/2019 450 06/01/2019 450 0GB

Per the stats command-
| stats earliest(_time) as first_time earliest(Capacit_Used) as first_value latest(_time) as last_time latest(Capacit_Used) as last_value
range(Capacit_Used) as result by Name

earliest & latest clobber those VMs that were deleted or added in a time range.
for VM4- it should not show up in July to Nov but if it was in a time selection- it should read -450 (due to it was deleted)

This does give me some ideas to try though so thanks!

Re: How to table/chart over a period of time

woodcock — Wed, 27 Nov 2019 16:56:59 GMT

Like this:

| makeresults
| eval _raw="Date,Name,Capacity Used
5/1/2019,VM1,100
5/1/2019,VM2,100
5/1/2019,VM4,450
6/1/2019,VM1,100
6/1/2019,VM2,140
6/1/2019,VM4,450
7/1/2019,VM1,105
7/1/2019,VM2,200
8/1/2019,VM1,110
8/1/2019,VM2,200
9/1/2019,VM1,110
9/1/2019,VM2,200
10/1/2019,VM1,110
10/1/2019,VM2,200
10/1/2019,VM3,100
11/1/2019,VM1,110
11/1/2019,VM2,200
11/1/2019,VM3,200"
| multikv forceheader=1
| eval _time = strptime(Date, "%m/%d/%Y")
| sort 0 - _time

 | rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

 | eval _time = strptime(Date, "%m/%d/%Y")
 | where _time >= strptime("7/1/2019", "%m/%d/%Y") AND _time <= strptime("11/1/2019", "%m/%d/%Y")
 | stats range(Capacity_Used) AS option1 min(Capacity_Used) AS min max(Capacity_Used) AS max first(Capacity_Used) AS newest last(Capacity_Used) AS oldest BY Name
 | eval option2 = max - min, option3 = newest - oldest
 | foreach option* [ eval <<FIELD>> = <<FIELD>> . "GB" ]
 | table Name option* *

Re: How to table/chart over a period of time

woodcock — Wed, 27 Nov 2019 16:57:21 GMT

Got it. See my new answer.

Re: How to table/chart over a period of time

clintla — Wed, 27 Nov 2019 17:14:27 GMT

Again for 7/1/2019 through 11/1/2019 that the result would be tabled as

VM1 5GB
VM2 0GB
VM3 200GB

(this is the actual growth)

If the VM doesnt exist at the beginning of a selected time frame.. it should be 0.

your results seem to be tied to min/max or earliest/latest but if the VM is not in the beginning or end of the range.. should start or end at 0 respectively.

Name option1 option2 eval option3 max min newest oldest
VM1 5GB 5GB 5 110 105 110 105
VM2 0GB 0GB 0 200 200 200 200
VM3 100GB 100GB 100 200 100 200 100

Re: How to table/chart over a period of time

clintla — Wed, 27 Nov 2019 17:16:19 GMT

should there be a fillnul & then add in a token for TimeRange.earliest=-30d ?

Re: How to table/chart over a period of time

clintla — Wed, 27 Nov 2019 17:30:06 GMT

Maybe a better example is just this.

if you had a VM that was created last month at 100GB and you did a search
for year to date.

your growth would be 100GB.

if you do any variation of min/max, earliest/latest, range.. you'll show 0GB growth
because earliest/latest/max/min/range is all going to show the same value

but the true growth is 100GB

Re: How to table/chart over a period of time

woodcock — Wed, 27 Nov 2019 18:04:40 GMT

OK, one more time. Stand by for new answer...

Re: How to table/chart over a period of time

woodcock — Wed, 27 Nov 2019 18:09:00 GMT

The problem is that your example doesn't have the use-case that you are complaining about. In any case, I have modified my answer to accommodate it as such: if there is only 1 event in the results set for any VM Name, then the value is listed as the growth:

| makeresults 
| eval _raw="Date,Name,Capacity Used
5/1/2019,VM1,100
5/1/2019,VM2,100
5/1/2019,VM4,450
6/1/2019,VM1,100
6/1/2019,VM2,140
6/1/2019,VM4,450
7/1/2019,VM1,105
7/1/2019,VM2,200
8/1/2019,VM1,110
8/1/2019,VM2,200
9/1/2019,VM1,110
9/1/2019,VM2,200
10/1/2019,VM1,110
10/1/2019,VM2,200
10/1/2019,VM3,100
11/1/2019,VM1,110
11/1/2019,VM2,200
11/1/2019,VM3,200" 
| multikv forceheader=1 
| eval _time = strptime(Date, "%m/%d/%Y") 
| sort 0 - _time 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution" 

| eval _time = strptime(Date, "%m/%d/%Y") 
| where _time >= strptime("7/1/2019", "%m/%d/%Y") AND _time <= strptime("11/1/2019", "%m/%d/%Y") 
| stats count range(Capacity_Used) AS option1 min(Capacity_Used) AS min max(Capacity_Used) AS max first(Capacity_Used) AS newest last(Capacity_Used) AS oldest BY Name 
| eval option2 = max - min, option3 = newest - oldest 
| foreach option* [ eval <<FIELD>> = if(count==1, min, <<FIELD>>) . "GB" ] 
| table Name option* * 1

Re: How to table/chart over a period of time

clintla — Wed, 27 Nov 2019 18:14:17 GMT

VM3 is one that is added in the time range & is what the main problem that I've been trying to fix.

Lemme check this out though & see if I can use it.

Thanks for the ongoing answers.. it does help in me learning more & being able to try new things

Re: How to table/chart over a period of time

clintla — Wed, 27 Nov 2019 18:25:37 GMT

Still, I guess I am not explaining it well enough or there is just not a straight-forward solution for this.

VM3 is the use case I was describing. It simply comes in during the time range

The more I look at it, the more I think it needs to be solved w/ a _time solution.

If a VM is removed from the selected time range.. it also wont work. (like VM4 if you do all-time)

if you count events, it doesnt work (doesnt tell you to start or end with a 0)

Anyway.. thanks for the efforts.. I will continue to plug away at it.

Re: How to table/chart over a period of time

woodcock — Wed, 27 Nov 2019 18:32:16 GMT

OK, I finally get it. Stand by for one more answer.

Re: How to table/chart over a period of time

woodcock — Wed, 27 Nov 2019 18:39:03 GMT

This definitely does what you need but I am unsure if the all_min_time calculation needs to go before or after the where:

| makeresults 
| eval _raw="Date,Name,Capacity Used
5/1/2019,VM1,100
5/1/2019,VM2,100
5/1/2019,VM4,450
6/1/2019,VM1,100
6/1/2019,VM2,140
6/1/2019,VM4,450
7/1/2019,VM1,105
7/1/2019,VM2,200
8/1/2019,VM1,110
8/1/2019,VM2,200
9/1/2019,VM1,110
9/1/2019,VM2,200
10/1/2019,VM1,110
10/1/2019,VM2,200
10/1/2019,VM3,100
11/1/2019,VM1,110
11/1/2019,VM2,200
11/1/2019,VM3,200" 
| multikv forceheader=1 
| eval _time = strptime(Date, "%m/%d/%Y") 
| sort 0 - _time 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution" 

| eval _time = strptime(Date, "%m/%d/%Y") 
| where _time >= strptime("7/1/2019", "%m/%d/%Y") AND _time <= strptime("11/1/2019", "%m/%d/%Y") 
| eventstats min(_time) AS all_min_time
| stats count first(all_min_time) AS all_min_time min(_time) AS min_time range(Capacity_Used) AS option1 min(Capacity_Used) AS min max(Capacity_Used) AS max first(Capacity_Used) AS newest last(Capacity_Used) AS oldest BY Name 
| eval first = if(my_min_time==all_min_time, first, 0)
| eval option2 = max - min, option3 = newest - oldest 
| foreach option* [ eval <<FIELD>> = <<FIELD>> . "GB" ]
| table Name option* * 1