topic Re: how to get last field value by time range picker in Splunk Search

how to get last field value by time range picker

xyz123 — Sun, 15 Nov 2020 04:00:07 GMT

Hello,
What I want to get a table with all fields populated with their last values by time range.

For each form I have a field called Created also the field Product that can be filled later or can be left empty. I want to calculate with form has a product value or are empty to get a total percentage.

For a time range let's say Created date goes from Nov 1 to Nov 7 the field Product shows empty, but if I add more day at time range like going ahead it gets populated properly, how can I get this last value when taking in count that I just want results within time range Nov1-7 (time range picker), here what I have until now:

index=main

| eval _time=strptime(Created,"%Y-%m-%d %H:%M:%S")

| addinfo
| where ((_time >= info_min_time) AND (_time=="+Infinity" OR _time<=info_max_time))

| stats latest(Created) as Created latest(Product) as Product values(Delivered) as Delivered last(Updated) as Updated by Code

Code Created Product Delivered Updated

1	A89580	2020-11-02 15:56:20	2020-11-02 20:47:20
2	A23780	2020-11-03 21:18:37	2020-11-04 19:08:12
3	A23826	2020-11-03 21:20:58	2020-11-06 21:21:35
4	A23900	2020-11-03 21:25:05	2020-11-06 21:25:19

If I extend the time range: shows all values for Product and Delivered date
I modified last(Update) for values(Update) I can see all the time range for each Code.
Just to clarify Product and Delivered fields are independent one of another; after is Delivered, Product can be filled or left empty.

Code Created Product Delivered Updated

1	A89580	2020-11-02 15:56:20	PPA89580	2020-11-13 19:39:01	2020-11-02 15:56:24 2020-11-02 19:21:34 2020-11-02 20:47:20 2020-11-10 13:13:06 2020-11-13 19:39:01 2020-11-14 20:01:49
2	A23780	2020-11-03 21:18:37	PPA23780	2020-11-10 02:22:47	2020-11-03 21:18:51 2020-11-04 19:08:12 2020-11-07 19:08:18 2020-11-10 02:19:48 2020-11-10 02:22:47 2020-11-11 03:00:36
3	A23826	2020-11-03 21:20:58	PPA23826	2020-11-12 20:34:07	2020-11-03 21:20:58 2020-11-03 21:21:28 2020-11-06 21:21:35 2020-11-09 21:21:37 2020-11-12 17:56:48 2020-11-12 17:58:36 2020-11-12 20:34:07 2020-11-13 21:01:04
4	A23900	2020-11-03 21:25:05	PPA23900	2020-11-09 21:43:31	2020-11-03 21:25:15 2020-11-06 21:25:19 2020-11-09 13:07:25 2020-11-09 13:09:33 2020-11-09 21:43:31 2020-11-10 22:03:09

Assistance with this will greatly be appreciated. Thank you.

Re: how to get last field value by time range picker

ITWhisperer — Sun, 15 Nov 2020 11:46:51 GMT

Just to clarify, you have two time ranges? One for all the events since a point in time, and one for the create time being within a smaller time period? Is the time period for the create time always the same e.g. start time + 7days?

Re: how to get last field value by time range picker

xyz123 — Sun, 15 Nov 2020 12:27:53 GMT

Hello,

Thanks for your question, I have a date field called Create I make Splunk time range picker _time to be the same as this date so it will bring all the events within that range.

In this example I took from Nov1-7

The issue is that others fields, like Delivered and Product get updated -after- that time range example in Nov 9, that's why I can see them empty for that time range.

I would like to show the final results with all populated.

Regards,

Xyz123

Re: how to get last field value by time range picker

ITWhisperer — Sun, 15 Nov 2020 12:50:12 GMT

It sounds like the way you are using timepicker is limiting the events that are retrieved, e.g. you are only getting events between Nov 1 and Nov 7, this is why you aren't getting the updates after Nov 7. You need to set the time range of the search (with time picker?) to be Nov 1 until now i.e. since Nov 1. You then need a way to limit the created dates. Or you need to use timepicker to just set the earliest time for the search and set latest to now. and then use the timepicker to limit your created time.

Are you setting up a dashboard for this? If so, can you share the SimpleXML you have for it?

Re: how to get last field value by time range picker

xyz123 — Sun, 15 Nov 2020 13:26:08 GMT

Hello,

Thanks for your reply, no dashboard for now, I'm tying to get the proper data first.

The client's asking for the products list when Created date is within a time range.

But I'm getting this products empty since time range is truncated by this time range.

Is there another way to show all this data using Created date?

I was thinking about transactions but again the last event is not captured.

Regards,

Xyz123

Re: how to get last field value by time range picker

ITWhisperer — Sun, 15 Nov 2020 13:53:12 GMT

Set the time picker for the query to be since Nov 1

index=main | eval Created=strptime(Created,"%Y-%m-%d %H:%M:%S") | stats latest(Created) as Created latest(Product) as Product values(Delivered) as Delivered last(Updated) as Updated by Code | where Created < strptime("2020-11-08","%Y-%m-%d")

Re: how to get last field value by time range picker

xyz123 — Mon, 16 Nov 2020 11:11:11 GMT

Hello, unfortunately it didn't work it still gets truncated, again if I put the time range from Nov1-7 to Nov1-14 I get all the values. Here the code again.

index=main | eval _time=strptime(Created,"%Y-%m-%d %H:%M:%S") | addinfo | where ((_time >= info_min_time) AND (_time=="+Infinity" OR _time<=info_max_time)) | stats latest(Created) as Created latest(Product) as Product values(Delivered) as Delivered last(Updated) as Updated by Code

Re: how to get last field value by time range picker

ITWhisperer — Mon, 16 Nov 2020 12:16:34 GMT

Setting the time range to Nov 1-7 only processes events in that time range, that is why the later updates are missing. It doesn't matter that you change the value of _time to be the time in the Created field, it is already too late to change which events are processed. By changing the time range to Nov 1-14 you include the other events but you also pick up events created in the time period to. This is why in my version I limited the rows on whether the created date was before Nov 8.

Fundamentally, the issue is that you need 3 points in time (Earliest event, Latest event, and latest created) but timepicker only allows you to select 2 of these. To get the data you want, timepicker should frame all the events you want to take into consideration i.e. anything after Nov 1 or Nov 1-14. You then need a where clause to select only the events with Created dates in the period you are interested in.

Re: how to get last field value by time range picker

xyz123 — Mon, 16 Nov 2020 13:14:14 GMT

Hello,

thanks for your reply, If I understand what you mean it's to set time range picker for 'All Time' then set the date (Created) range manually like using token for example?
It sounds like there's not another way to do this automatically?

regards,

Xyz123

Re: how to get last field value by time range picker

ITWhisperer — Mon, 16 Nov 2020 13:55:24 GMT

You don't need to use "All time", use date range - since

Then use the where clause to limit the results to everything before Nov 8.

If you want to automate it to some degree, you could use earliest from addinfo and add 7 days for example.

Re: how to get last field value by time range picker

xyz123 — Wed, 18 Nov 2020 11:53:12 GMT

Hi, could you kindly add the code for the earliest option? Thanks

Re: how to get last field value by time range picker

ITWhisperer — Wed, 18 Nov 2020 18:45:46 GMT

| addinfo | where _time <= info_min_time+(7*24*60*60)

Re: how to get last field value by time range picker

xyz123 — Wed, 18 Nov 2020 18:52:53 GMT

Thanks so much I've used this code to limit the Close date by making a wide range in time range picker. Appreciate your time.