Retrieving latest timestamp from CSV file such that it is smaller than event time

[andra_pietraru]

Hello,

I am using lookups to get some metadata from a CSV file that also has timestamps.

How could I retrieve the latest (maximum) timestamp from the CSV such that it is smaller than the event time? Is there any command that can do that? I tried stats and max, but that only finds the maximum without taking into consideration the second condition.
Thanks in advance!

[richgalloway]

No one command can do that. Filter first for smaller times then look for max. Something like ... | where csvTime < _time | stats max(csvTime) | ....

[andra_pietraru]

Just to make it more clear: based on IP addresses I retrieve MAC addresses and timestamps from the CSV file. I will retrieve several timestamps for each IP address. What I want to achieve is to make a timechart where I can see the MAC addresses in time. So, for example, if I have 2 MAC address with timestamp 1st of April and then I have only one MAC address with timestamp 1st of May for IP "::1", I want to see that in the timechart. Hence, from 1st April to 1st May IP "::1" should have two MAC addresses and starting 1st of May only one MAC address.
Do you have any suggestions? Thanks

[richgalloway]

Get the data from the CSV, but don't worry about smaller times. Let the bucketing feature of the timechart command handle that for you. Something like ... | timechart minspan=1mon count by MAC.

[andra_pietraru]

If I choose a smaller time range, example: 29 April - 2 May, then I always see two MAC addresses. So, setting up minspan is not enough or am I missing something?

[richgalloway]

You may indeed be missing something, but I don't know what that would be. 😉