Reporting
Highlighted

Eliminate the record from displaying int the report

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
Highlighted

Re: Eliminate the record from displaying int the report

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
Highlighted

Re: Eliminate the record from displaying int the report

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
Highlighted

Re: Eliminate the record from displaying int the report

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