Hiding null value results

[Bo3432]

Hello,

I am trying to find a command that will allow me to create a table and only display values. when using the user agent field in my table, there are some values that are null. I only want values to display. 

[richgalloway]

Use the where command to filter out results with empty fields.

| where isnotnull(user_agent) AND user_agent!=""

[Bo3432]

Can this be used with a table?  This is my command but it is giving no results. 

index=azure sourcetype="azure:monitor:aad" action=*
| where isnotnull(properties.userAgent) AND properties.userAgent!=""
|table _time user properties.deviceDetail.displayName properties.userAgent action
|sort -_time

[bowesmana]

@Bo3432 The where clause takes an eval statement and in eval statements you need to wrap 'odd' field names with single quotes. In your case properties.userAgent contains a full-stop, so you need to use

| where isnotnull('properties.userAgent') AND 'properties.userAgent'!=""

[richgalloway · Accepted solution]

Yes, it can be used with a table and all other visualizations.

When you say "it is giving no results" does that mean the where command is not filtering as expected or you are getting nothing at all from the query?  If the former, then it's possible the userAgent field is all spaces so the filter should be modified to handle that.  For the latter, try renaming the fields to eliminate dots.

index=azure sourcetype="azure:monitor:aad" action=*
| rename properties.* as *
| where isnotnull(userAgent) AND userAgent!=""
|table _time user deviceDetail.displayName userAgent action
|sort -_time

[Bo3432]

That did it. Much appreciated.