How do you combine results based on substring?

[sarahw3]

I have results such as "No image", "No Images", "No images: Blank", etc. I want to combine all results that say no images into one result that I can count. How can I do this in the search bar?

[somesoni2 · Accepted solution]

Try like this

your base search
| rex "(?<Status>(No Image|Never Checked|Not Working)" 
| stats count by Status

[alancalvitti]

Why am I getting no results using substr (vs rex)? eg group by first 10 characters of my_field values:

index=my_index  | eval prefix=substr(my_field,1,10) | stats count by prefix

Ive checked that the following does return results:

index=my_index  | stats count by my_field

[somesoni2]

Glad it worked out for you. Don't forget to close the question by accepting the answer that worked.

[sarahw3]

Now it is not working for me. Is there a way to say if the string starts with No Images, keep the first 9 characters of that string and forget the rest? Ex. "No Images- Computer failed" would be cut to just "No images"?

[somesoni2]

Sure, try like this

your base search
 | eval Status=case(searchmatch("No Image"),"No Images", searchmatch("Never Checked"),"Never Checked"), searchmatch("Not Working"),"Not Working") 
 | stats count by Status

[sarahw3]

Haha sorry for all the questions! I am very new to splunk haha! So we record the name of the cameras and a few years ago we changed the format of how we enter the names. Ex. PSA turned into 07789PSA. Is there a way I can combine those two formats into one for all 100+ cameras we have? I have a dropdown menu and I want each camera have just one option so that all the data for that one camera is together.

[somesoni2]

I'm pretty sure you can but I need to know more before I can suggest something. Does camera name comes as part of a field or raw? What is the current dropdown query?

[sarahw3]

It is raw data. We manually put the status of the camera next to the camera name in a google sheet which I have put into Splunk. The dropdown lets me choose the name of the facility and then I have another dropdown that will then let you select the name of the camera in that location that you want. The problem is that PSA and 07789PSA are both shown on the dropdown menu. I want to be able to combine them so all the data from the camera is being used by the panels on the dashboard.

[somesoni2]

Ok.. Just need your current Camera Name dropdown query.

[sarahw3]

It is Camera_Location

[somesoni2]

Add following eval to your dropdown query

| eval Camera_Location=replace(Camera_Location,"^\d+","")

[sarahw3]

Brilliant!!! Thank you!!!!!!

[sarahw3]

Yay that worked perfectly!!! Thank you so so so so so much!!!!!!

[mipeters_splunk]

if that is the case you should have not just accepted the answer but also upvoted it 😉

[rbreton]

Have you tried using an 'if' function.
| eval new-field = if(your-field = "No image*" , "No Image" , your-field)

[rbreton]

I have a similar search. See if this can help...
| eval status = if(like(status, "No Image%"), "No image", status)
| stats count by status

[gcusello]

HI sarahw3,
if you want the number of "No images" try something like this

your_search "No image*"| stats count

Bye.
Giuseppe

[sarahw3]

I also have other results, like "Not Checked" and "Working". Is there a way I can display those counts as well as the combo of "No images"?

[gcusello]

insert all these values in a lookup (e.g. called No_Images.csv with one column called query) and run a search like this

your_search [ | inputlookup No_Images.csv | fields query ] | stats count

In this way you search for all strings in your lookup.

Bye.
Giuseppe

[sarahw3]

It is still not working for me. I have the following events and their frequency when I do stats count by Status:
No Image: 30
No Images: 15
No image-Blank: 40
No image-Rebooted: 21
Never Checked: 132
Not Working: 21

I would like it to display like the following:
No Images: 106
Never Checked: 132
Not Working: 21

I have very little experience with Splunk so I apologize for not understanding. I really appreciate your help!!