index query results against inputlookup return stats/multiple stats

[DanWilkinson]

Hello and thank you for your time.

I would like to run a search in splunk, using the results against inputlookup lists to return the stats or multiple stats.

Example:

My search is:

index="MyIndex" AND host="MyHost" AND (*string1* OR "*string2*" OR "*string3*") | dedup user | table user

user_results
user.name1
user.name2
user.name3

using those results:

| inputlookup ACBounceList_a-c.csv
| inputlookup append=t ACBounceList_d-g.csv
| inputlookup append=t ACBounceList_h-l.csv
| inputlookup append=t ACBounceList_m-q.csv
| inputlookup append=t ACBounceList_r-s.csv
| inputlookup append=t ACBounceList_t-v.csv
| inputlookup append=t ACBounceList_w-z.csv
| stats count by field_stats_wanted

| where inputlookup_user = user_results

resulting in:

field_stats_wanted                     count

value1                                                     30

value2                                                     35

etc                                                            etc

Any assistance with this would be greatly appreciated.

[yuanliu]

It is unclear what is being asked.  What is the relationship between the field you tabled ("user") and all the lookup tables?  And the relationship with "field_stats_wanted"?  Most importantly, why is inputlookup even considered?  If you wonder, appending multiple inputlookups is rarely the correct approach.  It usually means that the problem is not clearly understood.

So, explain the use case without SPL first.  Is "user" is the only field of interest from raw events?  What is the desired results?  What are in those lookup tables?  Why are there so many different tables? Are there inherent relationships between those tables?  What is the logic between "user", these tables, and desired results?  Try not make volunteers read your mind.

[DanWilkinson · Accepted solution]

Thank you for the response. I did manage to figure out my issue. First was the use of the multiple lookups, when I created the first lookup, I used sort, that limited my results to > 5000, and I needed < 30k. I fixed that, creating the Inputlookup ACResults.csv without the sort value that was limiting my results. (inputlookup was from Active Directory).

Then used the following search:

  index=Myindex host=xx.xx.xx.xx "AAA user accounting Successful"

  | dedup user

Then used lookup for where the user field values matched the field cn from my lookup:

  | lookup ACResults.csv cn as user

Final result of my new search:

index=Myindex host=xx.xx.xx.xx "AAA user accounting Successful"

| dedup user

| lookup ACResults.csv cn as user

| eval Sector=extensionAttribute14

| stats count by Sector

| sort -count

Answering your questions:

-What is the relationship between the field you tabled ("user") and all the lookup tables? -user = cn from active directory

-And the relationship with "field_stats_wanted"? -extensionAttribute14 for that user (cn) from Active Directory

- Most importantly, why is inputlookup even considered? -all my inputlookups had the same fields, so appending would make it easier to search, (I thought), I was wrong.

-It usually means that the problem is not clearly understood. - that was true, but I learned.

I hope this helps for future users. Thank you all the same.