Splunk Search

how to get last field value by time range picker

xyz123
Explorer

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:

Spoiler
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

1A895802020-11-02 15:56:20  2020-11-02 20:47:20
2A237802020-11-03 21:18:37  2020-11-04 19:08:12
3A238262020-11-03 21:20:58  2020-11-06 21:21:35
4A239002020-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

1A895802020-11-02 15:56:20PPA89580
 
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
2A237802020-11-03 21:18:37PPA23780
 
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
3A238262020-11-03 21:20:58PPA23826
 
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
4A239002020-11-03 21:25:05PPA23900
 
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.

Labels (2)
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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.

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")

 

View solution in original post

0 Karma

xyz123
Explorer

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

xyz123
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

xyz123
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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")

xyz123
Explorer

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
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

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")

 

0 Karma

xyz123
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

ITWhisperer_0-1605534732959.png

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.

0 Karma

xyz123
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| addinfo
| where _time <= info_min_time+(7*24*60*60)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...