Splunk event time for event with starttime and endtime

[Cbr1sg]

Hello all,
I setup DB connect input to query data from SQL server and store it in the index of Splunk. During the setup of DB connect input, i was prompted to select one column in the data to represent the time of the event or choosing the indexing time as event time.
Problem is my event has start time and end time columns, doesn't matter what column I select, in the end I will miss out some events during Splunk search.

For example if I select start time as event time. When Splunk searchs for events between 1PM and 3PM, it only returns those events that have StartTime between 1PM and 3PM, and miss out those events that start earlier than 1PM, but end after 1PM/3PM

In the picture below, only event A and B will show up at the result of the search, but i will miss out event C and D.

Do you have any solution for this?

Thanks

[yeguchi]

Hi,

It is depends on how much data in your DB, but if you can configure timestamp as "Current index time" in Splunk DB connect and input time as "Batch", you can get all data returned by SQL with current timestamp.

In this case, timestamp is not related to start time and end time column in your data. When you search recent time, you can get all data regardless start / end time.

Hope it helps...

[Cbr1sg]

Our data is really huge, that's the reason we chose rising column instead of batch input in the first place.
IMO, for any event that has start time and end time, no matter what "time" you assign to it, whether it's current timestamp, start time, end time, indexing time, etc.. it will end up with missing data during the search as I mentioned in the original post.

Really hope Splunk can assign time duration instead of just time instance to the event.

[FrankVl]

There is no way around having to choose either start or end to get assigned to _time.

What you can of course do, is also extract the actual start and end times into their own fields and search by those fields, rather than by _time. That's a bit more cumbersome, and doesn't perform as fast, but it should allow you to search for all the events that match your search criteria. You'd still need to select something in the time picker of course, but just choose that window large enough to prevent missing out events.

Alternatively, you'll just need to decide for this particular use case what is the key moment. The start, or the end of an event and then assign that to _time and work with that.

[HiroshiSatoh]

How is Gantt Chart?

https://splunkbase.splunk.com/app/1741/#/details

[Cbr1sg]

Thanks but i think this is not the solution to my problem.
It's not an issue with displaying data, it's an issue of missing data, due to the way Splunk assign time to the event