How do you create a crosstab with sparse data?

[jfriedman_ofigl]

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.

[maciep]

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

[jfriedman_ofigl]

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

[maciep]

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

[eykrevooh · Accepted solution]

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