How to get first non-null values in the table based upon other field values?

[phularah]

I have a search that is generating the results like below. I need a search where if TAC, CellName and Date are same in 2 rows, it would remove those rows where SiteName and Address is "NULL", and if the TAC, CellName and Date are different in 2 rows, rows with "NULL" value for field SiteName and Address remains.

[somesoni2]

Give this a try

Your current search with fields TAC Date SiteID CellName SiteName Address
| stats values(SiteName) as SiteName values(Address) as Address by TAC DATE SiteID CellName
| eval SiteName=if(mvcount(SiteName)>1, mvfilter(NOT match(SiteName,"NULL")), SiteName)
| eval Address =if(mvcount(Address)>1, mvfilter(NOT match(Address,"NULL")), Address)

[phularah]

No it is not working.
SIteName field generally has only 2 values, either NULL or "some other value" and same goes for Address field.
So, mvcount() will always be greater than 1 and mvfilter() won't work.

What I need is a condition that if a CellName for a Date is not unique and its SiteName and Address field has 2 values NULL (by fillnull value=NULL) or "some other value", the row with NULL value is removed.

But, if CellName is unique on a Date and value for SiteName and Address fields is NULL, the row remains.

Anyhow,  using this line (| stats values(SiteName) as SiteName values(Address) as Address by TAC DATE SiteID CellName) after my search is showing no results as expected --> 

My Search right now is -->

index=abc SiteID=xyz TAC=12345
| eval Date=strftime(_time,"%d-%b-%y")
| table _time TAC Date SiteID CellName SiteName Address
| fillnull value=NULL SiteName Address
| dedup Date CellName SiteName Address
| sort - _time
| fields - _time

I am sorting with _time here, because if month changes and I am looking at last 7 days, it would show data for 31st of previous month first, then 30th and at last 1st of current month.

[somesoni2 · Accepted solution]

How about this?

Your current search with fields TAC Date SiteID CellName SiteName Address
| eventstats count by TAC Date SiteID CellName
|  where NOT (count>1 AND SiteName="NULL")

[phularah]

Thank you. It worked.

[yuanliu]

This appears to be an incomplete problem.  I assume that "in 2 rows" means in 2 consecutive rows.  The two conditions, "same in 2 rows" and "different in 2 rows" are not mutually exclusive when looking at any given row.  What is the criteria to "pair" two rows?  For example, given the following CellName in consecutive rows where SiteName and Address are all NULL and the rest of fields are identical:

	CellName
1	A
2	A
3	A
4	B
5	B
6	C

The only intuitive decision I can make is to remove rows 1 and 2.  What about rows 2 and 3?  What about 3  and 4? 4  and 5? 5 and 6?

[phularah]

I have sorted the results based on Date and CellName. As can be seen CellName and Date values are same for rows 5 and 6 and same in rows 3 and 4 here, but SiteName and Address are NULL.
So, I don't want the rows 3 and 5. 

But, there are some cases where Date and CellName combination are different, but SiteName and Address are NULL. I want those rows in my table.