topic Re: How to fill auto-fill missing dates in a time range and fill null with previous value? in Splunk Search

How to fill auto-fill missing dates in a time range and fill null with previous value?

srive326 — Mon, 04 May 2020 20:30:22 GMT

Hello everyone,

I need help with a query.

I have a table with the following fields:

_time USERNUMBER WEIGHT
2020-04-02 07:17:12.397 545 245.2400
2020-04-02 07:17:12.400 547 100.0011
2020-04-03 07:15:37.956 545 260.2400
2020-04-06 07:16:41.763 545 245.2400
2020-04-09 07:15:38.864 545 612.5600
2020-04-10 07:15:59.845 545 245.2400
2020-04-10 07:17:12.401 547 100.0011
2020-04-13 07:16:32.169 545 245.0258
2020-04-15 07:15:37.027 545 245.2400
2020-04-15 07:15:37.027 547 120.2400
2020-04-16 07:15:44.469 545 156.2400
2020-04-17 07:15:46.726 545 245.0215
2020-04-20 07:16:45.495 545 142.2400
2020-04-22 07:15:54.629 545 245.0214
2020-04-22 07:15:54.629 547 106.0521
2020-04-23 07:15:43.608 545 965.2850
2020-04-25 07:15:50.403 545 245.2100
2020-04-27 07:16:48.186 545 335.2400
2020-04-28 07:15:52.713 545 245.5596
2020-04-29 07:15:54.472 545 520.2400
2020-05-01 07:15:52.179 545 245.0018

I want to show for each USERNUMBER the weight on each day of the month of April. The problem is, that
the weight is only updated when there has been a change. How can I autofill the missing dates in April, and also
populate the WEIGHT for those missing dates with the WEIGHT that was there the previous day for that USER.
So for example if I'm only looking at USERNUMBER 545 the rows from 04/02 to 04/09 will look like:

2020-04-02 07:17:12.397 545 245.2400
2020-04-03 07:15:37.956 545 260.2400
2020-04-04 00:00:00.000 545 260.2400
2020-04-05 00:00:00.000 545 260.2400
2020-04-06 07:16:41.763 545 245.2400
2020-04-07 00:00:00.000 545 245.2400
2020-04-08 00:00:00.000 545 245.2400
2020-04-09 07:15:38.864 545 612.5600

Sidenote: The Hour/Time component of the date is not important to me
I hope this makes sense, thank you in advance for your help.

Re: How to fill auto-fill missing dates in a time range and fill null with previous value?

richgalloway — Mon, 04 May 2020 20:48:25 GMT

Check out the filldown command.

Re: How to fill auto-fill missing dates in a time range and fill null with previous value?

to4kawa — Mon, 04 May 2020 22:48:54 GMT

| makeresults
| eval _raw="_time,USERNUMBER,WEIGHT
2020-04-02 07:17:12.397,545,245.2400
2020-04-02 07:17:12.400,547,100.0011
2020-04-03 07:15:37.956,545,260.2400
2020-04-06 07:16:41.763,545,245.2400
2020-04-09 07:15:38.864,545,612.5600
2020-04-10 07:15:59.845,545,245.2400
2020-04-10 07:17:12.401,547,100.0011
2020-04-13 07:16:32.169,545,245.0258
2020-04-15 07:15:37.027,545,245.2400
2020-04-15 07:15:37.027,547,120.2400
2020-04-16 07:15:44.469,545,156.2400
2020-04-17 07:15:46.726,545,245.0215
2020-04-20 07:16:45.495,545,142.2400
2020-04-22 07:15:54.629,545,245.0214
2020-04-22 07:15:54.629,547,106.0521
2020-04-23 07:15:43.608,545,965.2850
2020-04-25 07:15:50.403,545,245.2100
2020-04-27 07:16:48.186,545,335.2400
2020-04-28 07:15:52.713,545,245.5596
2020-04-29 07:15:54.472,545,520.2400
2020-05-01 07:15:52.179,545,245.0018"
| multikv forceheader=1
| eval _time=strptime(time,"%F %T.%3Q")
| table _time USERNUMBER WEIGHT
| rename COMMENT as "this is sample"
| timechart cont=f span=1d list(WEIGHT) by USERNUMBER
| makecontinuous _time span=1d
| rename COMMENT as "Timerange created. check at this"
| filldown

I don't know what you want to display. How about this?

Re: How to fill auto-fill missing dates in a time range and fill null with previous value?

srive326 — Tue, 05 May 2020 13:54:02 GMT

Good morning,

Thank you for your help with this query. The goal in getting the data in that format is that I then have to look for each USERNUMBER add a conditional statement if the weight on a given day is greater than 500.000 or not, then add how many USERS had a weight over 500 and how many didn't on each day in April. I used your code and it worked in filling in the missing dates and also filling in with previous weights on the missing date (thank you). However, I have over 80,000 unique usernumbers, this means that I should have that many number of columns (splunk truncates them to show just 10). I tried transposing the result so I can instead have a table like this:
USERNUMBER 04/02/2020 04/03/2020
545 245.2400 260.2400

but I still got only 10 USERNUMBERS/rows.
Given these details, what would be the best way to display the data so I can add the conditional statements? Thank you again for your help.

Re: How to fill auto-fill missing dates in a time range and fill null with previous value?

richgalloway — Tue, 05 May 2020 14:08:53 GMT

10 is the default limit of timechart. Try timechart limit=0 ..., but I suspect 80,000 values may be too many to render.

Re: How to fill auto-fill missing dates in a time range and fill null with previous value?

to4kawa — Tue, 05 May 2020 16:02:18 GMT

You're missing the goal and the question, and the method is completely different.

Re: How to fill auto-fill missing dates in a time range and fill null with previous value?

srive326 — Wed, 30 Sep 2020 05:18:34 GMT

Hello,

Thank you for your input. I changed the limit to 0 and this helped return more USERNUMBERS.

I modified what to4kawa shared so it now looks like this:

This returns the USERNUMBER counts I expected (about 80k) but it only shows the first 5 dates as columns. Is there something else I need to edit to show the other dates?
I did get this error after it finished running: Events might not be returned in sub-second order due to search memory limits. See search.log for more information. Increase the value of the following limits.conf setting:[search]:max_rawsize_perchunk.

Also if I don't transpose the program doesn't finish and I get a few different errors.

Re: How to fill auto-fill missing dates in a time range and fill null with previous value?

srive326 — Tue, 05 May 2020 20:26:58 GMT

Ok, I will write a new question with all those details. I left them out because I thought I would be able to handle the second part of the query on my own.