Eliminate the record from displaying int the report

[Jananee_iNautix]

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.

[somesoni2 · Accepted solution]

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]

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.

[Jananee_iNautix]

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