Splunk Search

Count filled rows in multiple columns

kmattern
Builder

I have a table that has three columns. Normally the columns will have different numbers of entries, for example Col1 may have 25 entries while Col2 may have 8 and Col3 may have 75. I want to display the counts of the number of filled rows for each column. The best I can get is the total count of rows in the table itself. The way the data is aggregated I can't put counts into the search itself. I played with Post Processing but maybe I don't know enough about it to make it work.

I don't know if this is even possible but if anyone has ideas I'd be happy to hear them.

This is the obfuscated search


sourcetype="iis" earliest=-45d c_ip="192.168.0.1"

/PubType1/ .pdf cs_uri_stem!=".pdf_"

| makemv delim="/" cs_uri_stem

| eval Publication=mvindex(cs_uri_stem,3)

| eval PubType1=Publication

| stats count(PubType1) by PubType1

| appendcols [search earliest=-45d sourcetype="iis" c_ip="192.168.0.1"

/PubType2/ .pdf cs_uri_stem!=".pdf_"

| makemv delim="/" cs_uri_stem

| eval Publication=mvindex(cs_uri_stem,3)

| eval PubType2=Publication]

| appendcols [search earliest=-45d sourcetype="iis" c_ip="192.168.0.1"

/PubType2b/ .pdf cs_uri_stem!=".pdf_"

| makemv delim="/" cs_uri_stem

| eval Publication=mvindex(cs_uri_stem,3)

| eval PubType2B=Publication]

| stats first(PubType1) as PubType1, first(PubType2) as PubType2, first(PubType2B) as PubType2B, count(Publication) by Publication

| table PubType1, PubType2, PubType2B

0 Karma

chris
Motivator

Hi
you could append the following stats command to your base search that results in the table you describe:

<base search> | stats count(eval(isnotnull(Col1))) as Col1Entries count(eval(isnotnull(Col2))) as Col2Entries

If this does not help some sample data would be great to better understand your problem.

Chris

0 Karma

kmattern
Builder

Post processing may be the answer but I don't know enough about it yet to know for sure. In the end I have convinced my boss that too much time and money has been wasted on this exercise when the customer asked for these changes only if it didn't take too much effort (money!).

I'll play with this as time passes and I have the time.

0 Karma

chris
Motivator

Hi, just a quick thought before I leave the office. Could you have 2 visualisations for your client? One with the table of the "Pulbications" and another one with the counts. You could achieve that by using a PostProcess in your dashboard. But maybe I'm missing the point.

0 Karma

kmattern
Builder

This gives me the counts but in the process I lose the actual list of entries in each column. The table should list the results of the search. I really want to pass the counts to something like a SimpleResultsHeader. The three columns are lists of publications by type. The customer wants to see the list of pubs, which is drillable, but also the total count of each type of pub. I'm sorry but I cannot release any data because it belongs to the DoD.

I added the search to my original question.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

By "The best Ican get is the total count of rows in the table itself", you mean you tried "|stats count(column1)" and its giving you total no of rows?

0 Karma
Get Updates on the Splunk Community!

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...