topic Re: How to exclude blank rows with time being displayed in Splunk Search

How to exclude blank rows with time being displayed

iamsplunker — Mon, 30 Aug 2021 22:35:14 GMT

Hello Splunk Community,

I've a query which lists accountNumber , targetAccountNumber, eventType, eventTime

The query is working just fine..but it is displaying empty rows with eventTime being displayed

accountNumber targetAccountNumber eventType eventTime

123456 789123 apple 09/02/2020:12:00

banana 09/02/2020:13:00

111111 763333 mango 09/03/2020:15:00

watermelon 09/03/2020:16:00

212121 999999 Texas 09/04/2020 :18:00

09/04/2020:19:00

09/04/2020:20:00

09/04/2020:21:00

I do not want the empty eventTime being displayed. It should display only there is an eventType for particular time. How should I exclude the empty eventTime

My query is as follows

index=newyork data

| stats values(eventType) as eventTypes,values(eventTime) as "Event Time" by accountNumber, targetAccountNumber

Please help

Re: How to exclude blank rows with time being displayed

gcusello — Tue, 31 Aug 2021 07:25:20 GMT

Hi @iamsplunker,

you can add at the end of the search a filter

index=newyork data | stats values(eventType) as eventTypes,values(eventTime) as "Event Time" by accountNumber, targetAccountNumber | search eventType=*

Ciao.

Giuseppe

Re: How to exclude blank rows with time being displayed

iamsplunker — Tue, 31 Aug 2021 13:40:10 GMT

@gcusello : I tried adding |search eventTypes=*

|search EventTime=*

However it's not working for me

Re: How to exclude blank rows with time being displayed

PickleRick — Tue, 31 Aug 2021 13:49:51 GMT

Judging from your search, these are not empty rows.

You group by accountNumber and targetAccountNumber so even if you could have empty fields to split results by, you definitely cannot have two different "empty", "empty" results.

So your results probably (even though you pasted them destroying the formatting a bit) contain just three rows.

The fields eventType and eventTime have multivalue contents. For example, for a row with an accountNumber 123456 and targetAccountNumber 789123 you have a _single_ result with multivalue entry at eventType containing entries "apple" and "banana" and two different entries as multivalue entry at eventTime.

What's important - since you used values() on both those fields - there is no relation between those values that you can devise from them. In particular, "apple" "banana" and both timestamps could come from four different events.

You could do mvexpand but the results can make not much sense so I'd urge you to reconsider your initial search.

Again - if you have search results like that:

212121 999999 Texas 09/04/2020 :18:00

09/04/2020:19:00

09/04/2020:20:00

09/04/2020:21:00

It doesn't mean that there is "no eventType" for the 19:00, 20:00 and 21:00 timestamp.

The search that you provided should return different combinations of accountNumber and targetAccountNumber field values but for each of those combinations it only lists possible values for the two remaining fields regardless of whether they appear in the events with any connection to one another.

In other words - if you have table in form of:

A,B
1,2
3,4
5,6
1,7
3,6,
1,4

and you do | stats values(A) values(B)
you'll get just a single row of results with two fields - one will be a multivalued field of 1,3 and 5, and the other one will be another multivalued field with 2,4,6 and 7.

Re: How to exclude blank rows with time being displayed

gcusello — Tue, 31 Aug 2021 14:02:59 GMT

Hi @iamsplunker,

at first, you don't need to add two rows, but only one:

|search eventTypes=* EventTime=*

But, why isn't working for you, what's the problem?

Ciao.

Giuseppe