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!

Automatic Discovery Part 1: What is Automatic Discovery in Splunk Observability Cloud ...

If you’ve ever deployed a new database cluster, spun up a caching layer, or added a load balancer, you know it ...

Real-Time Fraud Detection: How Splunk Dashboards Protect Financial Institutions

Financial fraud isn't slowing down. If anything, it's getting more sophisticated. Account takeovers, credit ...

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

 Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...