topic Re: How to retrieve names from comments and assign values to those names? in Splunk Search

How to retrieve names from comments and assign values to those names?

ramesh12345 — Wed, 13 Mar 2019 12:27:42 GMT

Hi,

We have closed cases and escalated cases,in that single person can work on particular case as well as multiple persons worked on particular case.So my requirement is to display in bar chart how many cases resolved and escalated by person.

Ex: Case:1111 Worked persons: Raju,Ramu,rahul
      Case:1112 Worked persons:  Ramu,rahul
      Case:1113 Worked persons: Raju      
      Case:1115 Worked persons: Ramu

So my chart should be

Raju count is 2
Ramu count is 2
rahul count is 1 like this

My Basic search is:

index="os" sourcetype="Service"  CaseNumber=* status=*  assignment_group=* |dedup _time,CaseNumber,assignment |streamstats current=f last(assignment) as lg, last(active) as  Active by CaseNumber|lookup test.csv check as assigned_to OUTPUT TeamName| eval is_escalated= if(assignment!=lg AND assignment="Sustaining",1,NULL) |eval is_resolved=if(assignment="Sustaining" AND status="Complete" AND (isnull(Active) OR Active="true"),1,NULL)|stats count(is_escalated) AS "Escalated Cases" count(is_resolved) AS "Resolved Cases"  assigned_to,TeamName| fields - TeamName

I retrieved the names from comments using below search:

index="os" sourcetype="Service" CaseNumber=*|dedup _time,CaseNumber,status|rex field=Comments"(?\w*\W*\w*\s*\w*\s*\s\(\d+\))" |stats values(Names) as Names by CaseNumber|nomv Names|table CaseNumber,Names,status

So I want to display resolved and escalated cases by name.Please help how to do this.

Re: How to retrieve names from comments and assign values to those names?

vnravikumar — Wed, 13 Mar 2019 13:58:05 GMT

Hi @ramesh12345

Try this rex

| makeresults 
| eval comments="Case:1111 Worked persons: Raju,Ramu,rahul" 
| appendpipe 
    [| eval comments="Case:1112 Worked persons: Ramu,rahul"] 
| rex field=comments "Case:(?P<CaseNumber>.\d+).+\:\s(?P<Names>.+)$" 
| makemv delim="," Names 
| mvexpand Names 
| stats count by Names

Re: How to retrieve names from comments and assign values to those names?

ramesh12345 — Wed, 13 Mar 2019 14:11:38 GMT

Could you please send me the final query.

Re: How to retrieve names from comments and assign values to those names?

ramesh12345 — Wed, 13 Mar 2019 14:42:07 GMT

But my problem is whenever we write the condition eval is_resolved=if(assignment="Sustaining" AND status="Complete" AND (isnull(Active) OR Active="true"),1,NULL).it is showing only one person name.i want to assign that case to all who worked on that case.