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

[anikeshp7]

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.

[richgalloway · Accepted solution]

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