Splunk Search

How do you create a crosstab with sparse data?

jfriedman_ofigl
Explorer

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.

Tags (2)
0 Karma
1 Solution

eykrevooh
Explorer

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

View solution in original post

maciep
Champion

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
Explorer

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

maciep
Champion

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

0 Karma

eykrevooh
Explorer

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

Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...