Archive
Highlighted

How can I create categories and sub categories from a data field and use keywords from the field to compare it to another excel file?

Communicator

Hello fellow Splunkers,,

I have a two fold question. I have a field called CallDESCRIPTIONText, which contains issues reported. Using that, I have created the following query, which shows the data like this

host="BI_Data" Call_DESCRIPTION_Text="*" MEDIUM="*" CALL_NUMBER="*" MEDIUM="*"
 | eval Call_DESCRIPTION_Text=upper(Call_DESCRIPTION_Text)
 | eval Test=replace("*GHOST CALL*" WITH "GHOST CALL ISSUES" "WINDOWS PASSWORD*" WITH "WINDOWS PASSWORD ISSUES" "SEDXHO*" WITH "SODEXO RELATED ISSUES" "*SODEXO*" WITH "SODEXO RELATED ISSUES" "*SODEXHO*" WITH "SODEXO RELATED ISSUES" "*NETWORK PASSWORD*" WITH "NETWORK PASSWORD RESET ISSUES" "*ACCOUNT LOCK*" WITH "ACCOUNT LOCKED ISSUES" "*AD*" WITH "AD PASSWORD RESET ISSUES" "*REMOTE ACCESS*" WITH "REMOTE ACCESS RELATED ISSUES" "*VEEVA*" WITH "VEEVA PASSWORD AND LOGIN RELATED QUERIES" "*MCAFEE*LOGIN*" WITH "MCAFEE LOGIN RELATED ISSUES" "*USER*LOGIN*" WITH "USER LOGIN ISSUE" "*SAP*" WITH "SAP PASSWORD AND LOGIN RELATED ISSUES" "*VPN*" WITH "CONNECTING TO VPN RELATED ISSUES" "*OUTLOOK*" WITH "MS OUTLOOK RELATED ISSUES" "*WINDOWS ACCOUNT*" WITH "WINDOWS ACCOUNT LOCK/UNLOCK ISSUES" "TEST*" WITH "TEST CALLS" "*ACCESS TO 4SIGHT, PRECALL, TSA, MARKET ACCESS, TBM DASHBOARD OR REPORT*" WITH "ACCESS RELATED ISSUES FOR 4SIGHT/PRECALL/TSA/MARKET ACCESS/TBM DASHBOARD/REPORT" "*PASSWORD RESET*" WITH "MISCELLANEOUS PASSWORD RESET QUERIES" "*X2*" WITH "PSN/CSN PASSWORD RELATED ISSUES" "*ORACLE*" WITH "ORACLE RELATED ISSUES" "*INFORMATION*" WITH "USER REQUESTING INFORMATION" IN Call_DESCRIPTION_Text)
 | stats count(CALL_NUMBER) AS "Ticket Count" BY Call_DESCRIPTION_Text MEDIUM
 | chart limit=0 usenull=f sum("Ticket Count") OVER Call_DESCRIPTION_Text BY MEDIUM
 | rename Call_DESCRIPTION_Text AS "Top Issues Reported"
 | addtotals
 | sort - Total

alt text
Question 1: What I am supposed to do is, take these values and show them as "Categories", and the search the original data and find issues related to this, and show them as "Sub Categories". Like this. What can do or change in my query for this.

Category                                         Sub category

Account Related Issues               Account lock, Unlock, 

Remote Access Issues                 Unable to find printer, Unable to connect to remote access, Remote access possible

Ghost Call Issues                       drop after greet,  line dead, busy tone, background noise only

Sodexo                              Sedxho card call

Outlook related Issues               Migrate to outlook, unable to access, 

Password related issues             Password reset   

Question 2: How can I use these values to compare them with the values in another excel sheet and the bring a new field called "Knowledge article available" like this

alt text

Thanks in advance

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.