Splunk Search

Searching a lookup

Siddharthnegi
Contributor

I have this lookup

Siddharthnegi_0-1705395632145.png

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Contributor

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

0 Karma

Siddharthnegi
Contributor

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

What is your search then?

0 Karma

Siddharthnegi
Contributor

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

OK. We're getting somewhere :slightly_smiling_face:

| 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 :slightly_smiling_face:

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...