How to rename and combine results in search?

[PaulaCom]

Hi All 

I have a table showing number of Helpdesk calls and count for the year 

eg 

| search "problemtype.detailDisplayName"!=*AGRESSO*
| eval problem_detail='problemtype.detailDisplayName'
| eval problem_detail=replace(problem_detail, "&#8226","")
| eval problem_detail=replace(problem_detail, ";","|")
| eval techGroupLevel = 'techGroupLevel.levelName'
| eval techGroupLevel = replace(techGroupLevel, " "," ")
| eval techGroupLevel = replace(techGroupLevel, " ","")
| eval techGroupLevel = replace(techGroupLevel, "Level"," Level")
| eval location_Name = 'location.locationName'
| eval status = 'statustype.statusTypeName'
| eval priority = 'prioritytype.priorityTypeName'
| eval techGroupId = 'techGroupLevel.id'
| eval tech_Name = 'clientTech.displayName'
| table _time id displayClient location_Name problem_detail detail type bookmarkableLink status priority techGroupId techGroupLevel tech_Name reportDateUtc lastUpdated closeDate
| search techGroupLevel = "*"
| stats count as tech_group_requests by techGroupLevel
| sort -tech_group_requests

techGroupLevel                                   tech_Group_Requests 

Hardware Level 1                                  10000

Applications Level 1                                  800

Printer                                                           758

MIS                                                                      7

NULL                                                                    8

i would like to combine results of Hardware Level 1 and Printer and form new definition of "Device Management "

Also i would like to combine MIS and NULL as 'Other'

both to show count combined 

ie 

techGroupLevel                                   tech_Group_Requests 

Device Management                                  10758

Applications Level 1                                  800

Other MIS                                                            15

I have used | eval techGroupLevel=case(match(techGroupLevel, "HARDWARE"), "Device Management"

but i'm stuck on how to include printer to this code

thank you 

[ITWhisperer]

| eval techGroupLevel=if(match(techGroupLevel,"HARDWARE") OR match(techGroupLevel, "Printer"), "Device Management", if(match(techGroupLevel, "MIS") OR match(techGroupLevel, "NULL"), "Other MIS", techGroupLevel))
| stats count as tech_group_requests by techGroupLevel
| sort -tech_group_requests

[PaulaCom]

thank you 

this worked once i removed a previous eval command on techGroupLevel

P

[gcusello]

Hi @PaulaCom,

at the end of your search you have to:

• transpose your results,
• use eval for calculations,
• transpose agin your results,

Something like this:

<your_search>
| transpose 0 header_field=techGroupLevel column_name=tech_group_requests
| eval "Device Management"="Hardware Level 1"+Printer, Other=MIS+NULL
| transpose 0 header_field=tech_group_requests column_name=techGroupLevel

Ciao.

Giuseppe

[PaulaCom]

thank you 

I'm not familiar with transpose command and i couldn't get this to work but ended up using another command. 

thank you for your input 

P 

[GaetanVP]

Hello !

@gcusello very smart to think about the transpose method ! I just had a problem when it come to to sum the Hardware Level 1 + Printer, it did a concatenation of string and int... 

There is probably a clean and quick way to counter this issue... But I ended up with this : 

| makeresults format=csv data="techGroupLevel, tech_Group_Requests
Hardware Level 1, 10000
Applications Level 1, 800
Printer, 758
MIS, 7
NULL, 8"
| replace "Hardware Level 1" WITH hardware_level_1
| transpose 0 header_field="techGroupLevel" column_name=tech_Group_Requests
| eval "Device Management"=hardware_level_1+Printer
| eval Other=MIS+NULL
| fields tech_Group_Requests,"Device Management","Applications Level 1","Other"
| transpose 0 header_field="tech_Group_Requests" column_name=techGroupLevel

@PaulaCom I hope it helps !

Regards,

GaetanVP

[gcusello]

Hi @GaetanVP,

perfect!

I had the doubt that there could be an issue, solved with your workaround!

Ciao.

Giuseppe