topic Re: How to count filled and blank cells in excel spreadsheet by writing splunk query? in Splunk Search

How to count filled and blank cells in excel spreadsheet by writing splunk query?

ngwodo — Wed, 02 Dec 2020 04:15:21 GMT

I need help on splunk query that will count both filled and empty cells in excel spreadsheet differently and give the values in percentages. I am trying to track down the percentage of the cells left to be filled out in the excel spreadsheet using |inputlookup David_metrics_temp.csv

Re: How to count filled and blank cells in excel spreadsheet by writing splunk query?

richgalloway — Wed, 02 Dec 2020 15:11:11 GMT

Here is a way to count the number of filled and empty cells. I'll leave it to you to compute the percentage.

| inputlookup David_metrics_temp.csv | eval filled=0, empty=0 | foreach * [eval filled=if('<<FIELD>>'=filled OR '<<FIELD>>'=empty,filled,if(isnotnull('<<FIELD>>'),filled+1,filled)), empty=if('<<FIELD>>'=filled OR '<<FIELD>>'=empty,empty,if(isnull('<<FIELD>>'),empty+1, empty))] | fields empty filled | stats sum(*) as * | table filled empty

Re: How to count filled and blank cells in excel spreadsheet by writing splunk query?

ngwodo — Wed, 02 Dec 2020 20:34:53 GMT

It worked very well. Thanks.

Re: How to count filled and blank cells in excel spreadsheet by writing splunk query?

ngwodo — Wed, 02 Dec 2020 20:36:47 GMT

Please what about if I add up the totals of filled and empty will this query below do it?

| eventstats sum(filled, empty) AS total | table filled empty

Re: How to count filled and blank cells in excel spreadsheet by writing splunk query?

richgalloway — Wed, 02 Dec 2020 21:17:14 GMT

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

Re: How to count filled and blank cells in excel spreadsheet by writing splunk query?

richgalloway — Wed, 02 Dec 2020 21:18:46 GMT

Use eval to add filled and empty.

| eval total = filled + empty

Re: How to count filled and blank cells in excel spreadsheet by writing splunk query?

ngwodo — Wed, 02 Dec 2020 22:14:50 GMT

Thanks. I had already figure it out. Thanks again for your help.