Splunk Search

Could not construct lookup error for Top 10 users in Past 30 days

anikeshp7
Path Finder

Hi,

Im trying to display display top 10 users Name in the past 30 days using query

index="123" AND organizationId="00980876655334" earliest=-30d logRecordType=axapx ((*DataTableController*) AND (*fetchData*)) |lookup Test.csv UserID AS userId OUTPUT Name AS NAME| stats count(userId) as usage by userId |sort -usage limit=10

In Test.csv I have columns UserId and Name containing userIds and respective Names of users

The above query succesfully returns the top 10 UserId. However my requirement is to return the Names instead of the userIds.

tweaking the last line of query as [ stats count(NAME) as usage by NAME |sort -usage limit=10 ]

doesn't seem to work and gives error "Could not construct lookup 'Test.csv, UserID, AS, userId, OUTPUT, Name, AS, NAME'. "

Can anyone please help me on this.

 

 

Labels (5)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Try putting the lookup after stats.  It's a good practice to perform lookups late in query so fewer events have to be looked up.

index="123" AND organizationId="00980876655334" earliest=-30d logRecordType=axapx ((*DataTableController*) AND (*fetchData*)) 
| stats count(userId) as usage by userId 
| sort - usage limit=10
| lookup Test.csv UserID AS userId OUTPUT Name AS NAME
| table NAME, usage
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try putting the lookup after stats.  It's a good practice to perform lookups late in query so fewer events have to be looked up.

index="123" AND organizationId="00980876655334" earliest=-30d logRecordType=axapx ((*DataTableController*) AND (*fetchData*)) 
| stats count(userId) as usage by userId 
| sort - usage limit=10
| lookup Test.csv UserID AS userId OUTPUT Name AS NAME
| table NAME, usage
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Observability for AI

Don’t miss out on an exciting Tech Talk on Splunk Observability for AI!Discover how Splunk’s agentic AI ...

🔐 Trust at Every Hop: How mTLS in Splunk Enterprise 10.0 Makes Security Simpler

From Idea to Implementation: Why Splunk Built mTLS into Splunk Enterprise 10.0  mTLS wasn’t just a checkbox ...

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...