topic Re: How do I remove rows from a table based on empty column values? in Splunk Search

How do I remove rows from a table based on empty column values?

mrg_linus — Fri, 18 Sep 2015 14:10:55 GMT

What I'm trying to do
Using the export API /servicesNS/admin/search/search/jobs/export?output_mode=json&search=search I want to fetch (into a new cool system) comments made from CustomerServiceAgents in another system that then have been imported in Splunk. There are a lot of comments and I see that I can reduce the amount of retrieved rows by eliminating the ones where all the Comment fields are empty.

My question
How do I go about removing the rows where all Comments are empty? I've fiddled about with where and setting the field =*, but Splunk just sees this as invalid. See attached image to view my query and the result.

Re: How do I remove rows from a table based on empty column values?

maciep — Fri, 18 Sep 2015 15:20:25 GMT

Yeah, where doesn't like wildcards, but you can use it with match function.

.... | where match(comment_field,".")

Re: How do I remove rows from a table based on empty column values?

mrg_linus — Fri, 18 Sep 2015 15:49:30 GMT

Writing | where match(Comment1,".") nets me 0 results. I would assume it would return me the row in the picture where Comment1 is populated.

My goal is of course having all the rows where any of the Comments are populated.

Re: How do I remove rows from a table based on empty column values?

somesoni2 — Fri, 18 Sep 2015 16:01:35 GMT

Based on your data from the screenshot, try this

your base search  Value=* | eval {Property}=Value | stats lastest(*) as * by AccountID

Re: How do I remove rows from a table based on empty column values?

maciep — Fri, 18 Sep 2015 16:16:39 GMT

it depends on where you have the where. If it's after the stats, then you don't have a field called Comment1, you have a field called "latest(Comment1)".

So maybe try putting it before the stats. Or try what somesoni2 mentioned to keep the original field names.

Re: How do I remove rows from a table based on empty column values?

mrg_linus — Mon, 21 Sep 2015 12:42:16 GMT

Right perfect :)!

You are correct, I needed | where match("latest(Comment1)",".") if I didn't rename the columns. Thanks to @somesoni2!

Ended up with

index="*_db_customer" sourcetype="*:db:account:property" | eval {Property} = Value | stats latest(Comment*) by AccountID | table AccountID, latest(Comment*) | rename latest(*) to * | where match(Comment1,".") OR match(Comment2,".") OR match(Comment3,".") OR match(Comment4,".") OR match(Comment5,".") OR match(Comment6,".") OR match(Comment7,".") OR match(Comment8,".") OR match(Comment9,".") OR match(Comment10,".")