how to filter based on hour and minute after using transpose?

[abhi04]

Hi All,

I am using a mstats for a mteric and I am evaluating my hour and minute field something like below:

| mstats rate_avg(abc*) prestats=false WHERE "index"="def" span=3m
| rename rate_avg(* as *, *) as *
| eval Date=strftime(_time,"%m/%d/%Y")
| eval hour=strftime(_time,"%H")
| eval minute=strftime(_time,"%M")
| transpose column_name=instance
| rename "row 1" as MessagesRead
| eval MessagesRead=ROUND(MessagesRead,0)
| where MessagesRead < 1

Now I am unable to to use the below filter condition

| search NOT (instance="*xyz*" AND hour=09 AND (minute>=00 AND minute<=15))

as I dont want to alert for a particular instance only from 9 to 9:15, but it should alert for other instance for this time period.

Now before the transpose the instance does not exist and I cant use the filter and after transpose I am unable to filter on hour and minute.

Can u please help in filtering after transpose?

[tscroggins]

Hi @abhi04,

To filter with the where or search commands at the end of the pipeline, try the untable command instead of the transpose command:

| mstats rate_avg(abc*) as abc* where index=def span=3m
| untable _time instance MessagesRead
| eval MessagesRead=round(MessagesRead, 0)
| where ...