Dashboards & Visualizations

table from xml

SplunkCSIT
Communicator

Hi, i want to provide two different tables as below
Table1:
id Genre
bk1011 Computer
bk1121 Computer
bk1031 Fantasy

Table2:
Genre id
Computer bk1011
bk1121
Fantasy bk1031

<?xml version="1.0"?>

<catalog>

   <book id="bk1011">

      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-02</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
   <book id="bk1121">

      <author>Galos, Mike</author>
      <title>Visual Studio 7: A Comprehensive Guide</title>
      <genre>Computer</genre>
      <price>49.95</price>
      <publish_date>2001-04-16</publish_date>
      <description>Microsoft Visual Studio 7 is explored in depth,
      looking at how Visual Basic, Visual C++, C#, and ASP+ are 
      integrated into a comprehensive development 
      environment.</description>
   </book>

<book id="bk1031">

      <author>Corets, Eva</author>
      <title>Maeve Ascendant</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-11-17</publish_date>
      <description>After the collapse of a nanotechnology 
      society in England, the young survivors lay the 
      foundation for a new society.</description>
   </book>
Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

If that entire XML is one event you can do this:

base search | spath | rename catalog.book{@id} as id catalog.book.genre as genre | eval id_genre = mvzip(id, genre, "###") | table id_genre | mvexpand id_genre | makemv id_genre delim="###" | eval id = mvindex(id_genre, 0) | eval genre = mvindex(id_genre, 1) | table id genre

That'll generate your first table. To get the second table from that you can append | stats values(id) by genre to the above search.

View solution in original post

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

If that entire XML is one event you can do this:

base search | spath | rename catalog.book{@id} as id catalog.book.genre as genre | eval id_genre = mvzip(id, genre, "###") | table id_genre | mvexpand id_genre | makemv id_genre delim="###" | eval id = mvindex(id_genre, 0) | eval genre = mvindex(id_genre, 1) | table id genre

That'll generate your first table. To get the second table from that you can append | stats values(id) by genre to the above search.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Sounds to me as if you'll want to append this instead of the stats:

... | chart count over id by genre

...or this, I keep forgetting the order:

... | chart count over genre by id
0 Karma

SplunkCSIT
Communicator

thks, it works, now, i want to generate the bar graph such that the x-axis is id, and y-axis is numeric running numbers and the bar is stacked of Genre, how to go abt doing it?

0 Karma

SplunkCSIT
Communicator

thks, it works, now, i want to generate the bar graph such that the x-axis is id, and y-axis is numeric running numbers and the bar is stacked of Genre

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi SplunkCSIT,

either setup on your Search Head in props.conf the option KV_MODE = xml to have the key=value mapping done or your use spath to do it on the fly.

With spath it would be like this:

Table 1: YourBaseSearchHere | spath | stats values(gerne) AS gerne by id
Table 2: YourBaseSearchHere | spath | stats values(id) AS id by gerne

Or if you did set KV_MODE, like this:

Table 1: YourBaseSearchHere | stats values(gerne) AS gerne by id
Table 2: YourBaseSearchHere | stats values(id) AS id by gerne

hope this helps ...

cheers, MuS

0 Karma

MuS
SplunkTrust
SplunkTrust

I can only show you a path to your possible solution, I cannot do your work, since you did not tell what exactly your needs are. Test your data and maybe add some dedup gerne before the stats to get what you want....

0 Karma

SplunkCSIT
Communicator

Thks for the ans but it does not work. Based on your stats values(gerne) AS gerne by id it returns:

ID Genre
bk1011 Computer
Fantasy
bk1121 Computer
Fantasy
bk1031 Computer
Fantasy

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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 ...