topic Re: About cross-tabulation in XQL in Cortex XSIAM Discussions

About cross-tabulation in XQL

T.Abe940625 — Wed, 22 Jan 2025 08:56:31 GMT

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

Re: About cross-tabulation in XQL

MDovirak — Tue, 11 Feb 2025 09:39:56 GMT

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