topic Re: table from xml in Dashboards & Visualizations

table from xml

SplunkCSIT — Mon, 07 Apr 2014 10:08:11 GMT

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>

Re: table from xml

MuS — Mon, 07 Apr 2014 10:41:45 GMT

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

Re: table from xml

SplunkCSIT — Mon, 07 Apr 2014 12:24:16 GMT

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

Re: table from xml

MuS — Mon, 07 Apr 2014 12:33:00 GMT

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

Re: table from xml

martin_mueller — Mon, 07 Apr 2014 12:48:58 GMT

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.

Re: table from xml

SplunkCSIT — Mon, 07 Apr 2014 13:08:39 GMT

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

Re: table from xml

SplunkCSIT — Tue, 08 Apr 2014 00:47:05 GMT

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?

Re: table from xml

martin_mueller — Tue, 08 Apr 2014 07:18:23 GMT

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