Reporting

Eliminate the record from displaying int the report

Jananee_iNautix
Path Finder

Having a set of logs,we generate a report in table format containing 18 fields .There is a requirement to eliminate the records which has only one field containing value and the remaining 17 fields empty.For example

sl.no Filename user ..................... service error

1 abc

2 reyg ........................

3 frfe fwef ........................frefg reytr

The record no 1 & 2 contains only one field containg value and the remaining fields empty.

slno 1.Only the field filename has value and other fields empty
slno 2.Only the field user has value and other fields empty

I want to eliminate these two records and similar records containing one non-empty field from displaying in the report.

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this workaround

your base search giving your report with 18 fields | streamstats count as sno| eventstats count(*) as c_* by sno |fields - sno | addtotals fieldname=NonNULLFields c_* | where NOT NonNULLFields=1 | fields - c_*,NonNULLFields

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try this workaround

your base search giving your report with 18 fields | streamstats count as sno| eventstats count(*) as c_* by sno |fields - sno | addtotals fieldname=NonNULLFields c_* | where NOT NonNULLFields=1 | fields - c_*,NonNULLFields

gauldridge
Path Finder

You could eval a new field that concatenates your 18 fields into one delimited string, use makemv on the new field, and then mvcount to create a flag for rows/records that only have a single value:

your_current_search_here ... | eval concatField=Filename."|".user."|".service."|".error | makemv delim="|" concatField | eval count=mvcount(concatField) | where count>1 | fields - concatField,count

I haven't tested this exact example but I've done similar things before with no problem.

0 Karma

Jananee_iNautix
Path Finder

The understanding that has been got from the above query is

First calculate the count of events using "streamstats" for each event and store it in sno.

Secondly count the occurence of all fields for each sno(per event basis) and store it in c_.Then find the total number of numeric fields matching the field c_ and store it in the NOnNullFields.Exclude the records that contain NonNullFields=1.Finally remove the fields c_* and NonNullFields

Can you confirm whether our understanding is correct .Also please correct if its wrong

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...