topic Re: Searching a lookup in Splunk Search

Searching a lookup

Siddharthnegi — Tue, 16 Jan 2024 09:57:35 GMT

I have this lookup

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

Re: Searching a lookup

PickleRick — Tue, 16 Jan 2024 09:59:19 GMT

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

| 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.

Re: Searching a lookup

Siddharthnegi — Tue, 16 Jan 2024 10:04:40 GMT

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

Re: Searching a lookup

PickleRick — Tue, 16 Jan 2024 10:11:01 GMT

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

Re: Searching a lookup

Siddharthnegi — Tue, 16 Jan 2024 10:52:18 GMT

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

Re: Searching a lookup

PickleRick — Tue, 16 Jan 2024 13:37:29 GMT

What is your search then?

Re: Searching a lookup

Siddharthnegi — Tue, 16 Jan 2024 14:15:16 GMT

Re: Searching a lookup

PickleRick — Tue, 16 Jan 2024 14:51:01 GMT

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 🙂