How to exclude blank rows with time being displayed

[iamsplunker]

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

[PickleRick]

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.

[gcusello]

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

[iamsplunker]

@gcusello : I tried adding |search eventTypes=* 

                         |search EventTime=* 

However it's not working for me

[gcusello]

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