Searching a lookup

[Siddharthnegi]

I have this lookup

I want the total count when the timeval is latest. (in this case 2023) any solution

[PickleRick]

If you want to just get some statistical report on data read from your lookup, use the inputlookup command.

Like

| inputlookup mylookup | stats count

will give you number of rows in your lookup. You can do any operation on fields read from the lookup that you would normally do in a "normal" event search.

[Siddharthnegi]

thanks for the reply but  I want the total count when the timeval is latest. (in this case 2023), so according to my lookup result should be 2. with BIE count is 0 and  RAD count is 2 so 0+2=2. Hope this helps in understanding

[PickleRick]

And what have you tried so far? And how the results weren't meeting your expectations?

[Siddharthnegi]

if i try to find count i am only getting count of either BIE or RAD . But I want count of both combined .

[PickleRick]

What is your search then?

[Siddharthnegi]

| inputlookup abc.csv
| eval CompanyCode="DSPL"
| eventstats values(CompanyCode) as CompanyCode
| eval 3Let=case(CompanyCode == "DSDE", "BIE", CompanyCode == "DSDE-AS", "PUT", CompanyCode == "DSDE-FS", "STL", CompanyCode == "CSDE", "DAR", CompanyCode == "DSPL", "RAD", CompanyCode == "DSMX", "QUE", CompanyCode == "DSUS", "SSC")
| where '3Let'='place'
| sort - timeval
| table count timeval
| head 1
|appendpipe [stats count | where count==0
| eval timeval=strftime(now(),"%Y") | where count==0]

[PickleRick]

OK. We're getting somewhere 🙂

| inputlookup abc.csv
| eval CompanyCode="DSPL"
| eventstats values(CompanyCode) as CompanyCode
| eval 3Let=case(CompanyCode == "DSDE", "BIE", CompanyCode == "DSDE-AS", "PUT", CompanyCode == "DSDE-FS", "STL", CompanyCode == "CSDE", "DAR", CompanyCode == "DSPL", "RAD", CompanyCode == "DSMX", "QUE", CompanyCode == "DSUS", "SSC")
| where '3Let'='place'

OK. I assume this produces your data set and it works pretty OK.

But now if you want to have _all_ events for which a particular field has a value which is max of all possible, you have several options available (for example using subsearches) but the easiest one will be to add an additional field which tells you which value is the max year value. For this we use eventstats.

| eventstats max(timeval) as maxyear

Now you have an additional field telling you which year is the max year. So now just filter your values to only leave those where your timeval is equal to that maxyear

| where timeval=maxyear

And you should be all set 🙂