topic Re: How do you create a crosstab with sparse data? in Splunk Search

How do you create a crosstab with sparse data?

jfriedman_ofigl — Thu, 07 Feb 2019 22:10:24 GMT

My vulnerability data looks like this:

Machine  MachineType  VulnCode  Impact
-------  -----------  --------  ------
A        X            100       5
A        X            101       4
A        X            102       3
A        X            103       5
B        X            200       5
B        X            201       3
C        Y            101       4
D        Y            200       5
D        Y            201       3
E        Z            103       5
F        Z            201       3

I want a result like this:

MachineType  Impact=5  Impact=4  Impact=3
-----------  --------  --------  --------
X            3         1         2
Y            1         1         1
Z            1         0         1

I tried appendcols with a savedsearch but got Found circular dependency when expanding savedsearch.

Thank you.

Re: How do you create a crosstab with sparse data?

eykrevooh — Thu, 07 Feb 2019 23:22:44 GMT

This would be case to use the xyseries command.

First you want to get a count by the number of Machine Types and the Impacts.
| stats count by MachineType, Impact
Once you have the count you can use xyseries command to set the x axis as Machine Types, the y axis as the Impact, and their value as count.
| xyseries MachineType, Impact, count
This will produce a table like you have above, the only difference is that any place where there would be a 0 it would be null. If you want zeros in there place you can use fillnull.
| fillnull value=0

Example Full Command:
| stats count by MachineType, Impact | xyseries MachineType, Impact, count | fillnull value=0

Re: How do you create a crosstab with sparse data?

maciep — Thu, 07 Feb 2019 23:28:15 GMT

I think you just need to make impact that way you want and then use chart to format.

<your search>
| eval Impact = "Impact=" . Impact
| chart count over MachineType by Impact

Re: How do you create a crosstab with sparse data?

jfriedman_ofigl — Fri, 08 Feb 2019 14:25:31 GMT

This answer works as well, and perhaps is slightly cleaner.
@eykrevooh's answer was posted first and thus I accepted it.

Re: How do you create a crosstab with sparse data?

maciep — Fri, 08 Feb 2019 14:33:00 GMT

understood...important thing is you got what you needed. And although xyseries may not be needed for this particular case, keep it in your toolbox, because it can definitely come in handy