How to reverse my current search to display names as opposed to numbers( lookup)

[splunkman341]

Hi guys,

So I have a search that currently grabs the most active category, with also the most active subcategory under each category. Example the most active category 10000002, has the move active subcategory 7000006 under it. I have this part done, but what I need help no with is that opposed to displaying the category number, I want it to display the category and subcategory name respectively. I inserted two csvs files, but now I am not sure if I need to create a new sure, or I have to modify the one I currently have.

This is currently the search that I have

index=doccloud_main sourcetype=doccloud_catalina "Document workspace" NOT "draft" | rex "Category:\s*(?<Category>[^,]*),\s*subCategory:\s*(?<subCategory>.*)" | stats count by Category, subCategory | sort 30 - count

[woodcock · Accepted solution]

Assuming that your CSV file calls the category number "ID" and the category name "category" (do note that casing is important so "ID" is not the same as "id" or "Id"), this will definitely work:

index=doccloud_main sourcetype=doccloud_catalina "Document workspace" NOT "draft" | rex "Category:\s*(?<Category>[^,]*),\s*subCategory:\s*(?<subCategory>.*)" | stats count by Category, subCategory | sort 30 - count | lookup categoryInfo_lookup.csv ID AS Category OUTPUT category AS CategoryName

Also be aware that you must create a definition for the lookup to attach a Knowledge Object name to the file. You do this by going to Settings -> Lookups -> Lookup Definitions -> New. I am mentioning this because it looks to me like you are referencing the filename in your lookup command instead of a Lookup Definition (because of the .csv ending on categoryInfo_lookup.csv instead of something more conventional like categoryInfo_lookup).

[somesoni2]

Assuming your current search is giving Category and SubCategory as code (numeric) and you've two lookup table (CSV) files in Splunk named category_lookup.csv (have fields Category and CategoryName) and subcategory_lookup.csv (have fields subCategory and subCategoryName), then try this

index=doccloud_main sourcetype=doccloud_catalina "Document workspace" NOT "draft" | rex "Category:\s*(?<Category>[^,]*),\s*subCategory:\s*(?<subCategory>.*)" | stats count by Category, subCategory | sort 30 - count | lookup category_lookup.csv Category OUTPUT CategoryName | lookup subcategory_lookup.csv subCategory OUTPUT subCategoryName | rename CategoryName as Category subCategoryName as subCategory |table Category, subCategory count

[splunkman341]

The category number in my csv file is labeled as "ID" and the actual name of the category is labeled as "category". I am attempting only category right now, and it is not working. This is what I have tried:

index=doccloud_main sourcetype=doccloud_catalina "Document workspace" NOT "draft" | rex "Category:\s*(?<Category>[^,]*),\s*subCategory:\s*(?<subCategory>.*)" | stats count by Category, subCategory | sort 30 - count | lookup categoryInfo_lookup.csv id OUTPUT category|  rename id as category