How to combine tstats with inputlookup?

[nmohammed]

I am trying to produce report to get total usage based on time and clientid from a lookup.

Here is the regular tstats search:

| tstats count FROM datamodel=Enc groupby  _time span=1d, Enc.clientid

The above search gives results for all the clientids in the particular index, but I need results for only a list of clientids which is in the DC-Clients.csv

I tried the following, but doesn't produce any results:

| tstats count FROM datamodel=Enc groupby  _time span=1d, Enc.clientid | search [| inputlookup DC-Clients.csv | fields + clientid]

Example contents of DC-Clients.csv

Actual Clientid,clientid
018587,018587
033839,033839

I need to filter results to produce a report for only the clientids in the CSV file. Any suggestions appreciated.

Thanks..!!!

[my2ndhead]

A faster variant would be to add the filter directly into the tstats command:

| tstats count FROM datamodel=Enc where [ | inputlookup DC-Clients.csv | fields + Enc.clientid ]  groupby  _time span=1d, Enc.clientid  by host

[nmohammed · Accepted solution]

Was able to get the desired results.

First I changed the field name in the DC-Clients.csv lookup file from clientid to Enc.clientid and saved it. So the new DC-Clients.csv file contents look like this:

contents of DC-Clients.csv

Actual Clientid,Enc.clientid
018587,018587
033839,033839

Then the in the search, I used the field Enc.clientid that matches the field in the data model as follows ...

New search:

 | tstats count FROM datamodel=Enc groupby  _time span=1d, Enc.clientid | search [| inputlookup DC-Clients.csv | fields + Enc.clientid]

tstats produces results much faster than the original search I was using.

Thanks..!!!