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

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

phularah — Tue, 02 Aug 2022 14:59:23 GMT

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.

Re: Need first non-null values in the table based upon other field values

yuanliu — Tue, 02 Aug 2022 08:39:54 GMT

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?

Re: Need first non-null values in the table based upon other field values

phularah — Tue, 02 Aug 2022 10:20:16 GMT

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.

Re: Need first non-null values in the table based upon other field values

somesoni2 — Tue, 02 Aug 2022 13:05:10 GMT

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)

Re: Need first non-null values in the table based upon other field values

phularah — Tue, 02 Aug 2022 13:48:15 GMT

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.

Re: Need first non-null values in the table based upon other field values

somesoni2 — Tue, 02 Aug 2022 13:54:15 GMT

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")

Re: Need first non-null values in the table based upon other field values

phularah — Tue, 02 Aug 2022 14:10:31 GMT

Thank you. It worked.