Inputlookup

[chuck_life09]

Hi, 

I have the below lookup file 

sbl.csv

It has 3 rows 

1. A=1, B = " Added" , C= 31/3/2021 04:16pm

2. .A=1, B = " Added" , C= 31/3/2021 04:45pm

3. . A=1, B = " Removed" , C= 31/3/2021 04:57pm.

Now if I give a search

|inputlookup sbl.csv | stats latest(B) as status by A 

I should get 1 , Removed

but I am getting 1, Added 

Why is that, can anyone help? 

[scelikok]

Hi @chuck_life09,

When I test with your sample data it works. Maybe your time format is different than the sample?

latest/earliest function needs _time field in epoch time.  Since your lookup has no _time field, latest/earliest function have no effect.

[chuck_life09]

Thanks this worked... 

[scelikok]

Hi @chuck_life09,

Easier way to do without a subsearch;

| inputlookup sbl.csv
| eval _time=strptime(C,"%d/%m/%Y %I:%M%p") 
| stats latest(B) as status by A

[chuck_life09]

Hi @scelikok 

Still I am not getting A= 1 and B = removed

It is still taking only the first row item. 

Why is it that stats latest won't work within inputlookup? 

[gcusello]

Hi @chuck_life09,

you can use the latest option on a date and in epochtime not on another field.

So you have to convert C in epochtime and use that timestamp to extract the fields you need, something like this:

| inputlookup sbl.csv 
| search [ | inputlookup sbl.csv | eval C_epoch=strptime(C,"%d/%m/%Y %I:%M%p") | stats latest(C_epoch) AS C_epoch BY A | eval C=strftime(C_epoch,"%d/%m/%Y %I:%M%p") | fields C ]
| table A B C

Ciao.

Giuseppe