How to Transpose Correct Query

[strawberry28]

OLD Query:

source="http:Emerson_P1CDN" AND status_code=200 AND path=*/catalog* AND path!=*thumb* AND path!=*CartRefreshStatusJSON* AND path!=*PriceAjaxView* | bucket span=1m _time | stats count by client_isp,_time | where count >= 600 | convert timeformat="%m/%d/%Y %H:%M:%S" ctime(_time) | sort - count | transpose header_field=_time

When I set this one as an alert, it considers the client_isp and _time as part of the query so even there where no result it is sending a blank alert only the client_isp and time on the first column.

New Query:

source="http:Emerson_P1CDN" AND status_code=200 AND path=*/catalog* AND path!=*thumb* AND path!=*CartRefreshStatusJSON* AND path!=*PriceAjaxView* | bucket span=1m _time | stats count by client_isp,_time | transpose header_field=_time | sort - count | where count >= 600 | convert timeformat="%m/%d/%Y %H:%M:%S" ctime(_time)

While on this one, there were no result at all.

What maybe wrong on this query?

[ITWhisperer · Accepted solution]

The transpose command has an option to deal with this situation.

You could try something like this

source="http:Emerson_P1CDN" AND status_code=200 AND path=*/catalog* AND path!=*thumb* AND path!=*CartRefreshStatusJSON* AND path!=*PriceAjaxView* 
| bucket span=1m _time 
| stats count by client_isp,_time 
| where count >= 600 
| convert timeformat="%m/%d/%Y %H:%M:%S" ctime(_time) 
| sort - count 
| transpose header_field=_time include_empty=f