About cross-tabulation in XQL

[T.Abe940625]

Hi

Is it possible to execute PIVOT on the results of XQL execution?

For example, if I execute an XQL query on the following table

1/1/2025 allow hostname
1/1/2025 deny hostname
1/2/2025 allow hostname
1/2/2025 allow hostname
1/3/2025 deny hostname
1/3/2025 deny hostname

I want to get the following output

                      allow　deny
1/1/2025　　　1　     1
1/2/2025　　　2　　 0
1/3/2025　　　0　　 2

[MDovirak]

Easy way to make it comp(count) per action. Like example here:

dataset = firewall
| filter act in ("allow", "deny")
| comp count() as total by act

You should return total -> act count (deny/allow).

If you need to make it per day, you should create a new field "day". To make it, you can use replex. Example: | alter day = replex(to_string(date_floor(_time, "d")), "\d+:\d+:\d+\+00","")
So, your query should looks like:

dataset = firewall
| alter day = replex(to_string(date_floor(_time, "d")), "\d+:\d+:\d+\+00","")
| comp count() as total by act, day

Results: Total | Act | Day