topic Re: How do you combine results based on substring? in Splunk Search

How do you combine results based on substring?

sarahw3 — Thu, 27 Jul 2017 14:07:37 GMT

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?

Re: How do you combine results based on substring?

gcusello — Thu, 27 Jul 2017 15:34:58 GMT

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

your_search "No image*"| stats count

Bye.
Giuseppe

Re: How do you combine results based on substring?

sarahw3 — Thu, 27 Jul 2017 15:43:07 GMT

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"?

Re: How do you combine results based on substring?

somesoni2 — Thu, 27 Jul 2017 15:48:59 GMT

We may be more helpful if you can add more (full) sample events with all different values. A sample output would be even more helpful.

Re: How do you combine results based on substring?

gcusello — Thu, 27 Jul 2017 15:53:08 GMT

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

Re: How do you combine results based on substring?

sarahw3 — Thu, 27 Jul 2017 18:25:05 GMT

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!!

Re: How do you combine results based on substring?

sarahw3 — Thu, 27 Jul 2017 18:27:16 GMT

And this data is coming from multiple csv files if that makes a difference.

Re: How do you combine results based on substring?

somesoni2 — Thu, 27 Jul 2017 18:33:17 GMT

Are "No Image", "Never Checked" and "Not Working" are the only possible values here? The text you posted in your previous comment, is it _raw field (raw data) or part of a field?

Re: How do you combine results based on substring?

sarahw3 — Thu, 27 Jul 2017 18:42:02 GMT

It is raw data in the form of a string.

Re: How do you combine results based on substring?

sarahw3 — Thu, 27 Jul 2017 18:45:50 GMT

And yes these are the only possible values I am interested in.

Re: How do you combine results based on substring?

rbreton — Thu, 27 Jul 2017 18:47:31 GMT

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

Re: How do you combine results based on substring?

somesoni2 — Thu, 27 Jul 2017 18:51:23 GMT

Try like this

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

Re: How do you combine results based on substring?

sarahw3 — Thu, 27 Jul 2017 19:10:11 GMT

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

Re: How do you combine results based on substring?

somesoni2 — Thu, 27 Jul 2017 19:19:21 GMT

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

Re: How do you combine results based on substring?

sarahw3 — Fri, 28 Jul 2017 16:59:50 GMT

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"?

Re: How do you combine results based on substring?

somesoni2 — Fri, 28 Jul 2017 17:04:27 GMT

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

Re: How do you combine results based on substring?

sarahw3 — Fri, 28 Jul 2017 17:08:53 GMT

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

Re: How do you combine results based on substring?

sarahw3 — Fri, 28 Jul 2017 18:47:11 GMT

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.

Re: How do you combine results based on substring?

somesoni2 — Fri, 28 Jul 2017 18:59:48 GMT

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?

Re: How do you combine results based on substring?

rbreton — Fri, 28 Jul 2017 19:22:39 GMT

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