Dashboards & Visualizations
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

Transform Hierarchical XML into Table Rows

pixeldude
Explorer
‎04-15-2021 09:57 AM

Hi everyone. I have some hierarchical XML that can get rather large. If you're familiar with SQL - I'm visualizing Execution Plans. I have data that looks like this:

 

 

<PlanXml>
<CursorInformation>
<SQL_ID>00j24vf2v5uay</SQL_ID>
<ADDRESS>00000000B9AF2A00</ADDRESS>
<HASH_VALUE>2243094878</HASH_VALUE>
<PLAN_HASH_VALUE>3599761032</PLAN_HASH_VALUE>
<CHILD_ADDRESS>00000000B9AF12A8</CHILD_ADDRESS>
<CHILD_NUMBER>0</CHILD_NUMBER>
<TIMESTAMP>2021-04-15 07:19:52</TIMESTAMP>
</CursorInformation><CursorDetails>
<ID>0</ID>
<PARENT_ID></PARENT_ID>
<DEPTH>0</DEPTH>
<POSITION>1548</POSITION>
<OPERATION>SELECT STATEMENT</OPERATION>
<OPTIONS></OPTIONS>
<OBJECT_NODE></OBJECT_NODE>
<OBJECT_NUMBER></OBJECT_NUMBER>
<OBJECT_OWNER></OBJECT_OWNER>
<OBJECT_NAME></OBJECT_NAME>
<OBJECT_ALIAS></OBJECT_ALIAS>
<OBJECT_TYPE></OBJECT_TYPE>
<OPTIMIZER>ALL_ROWS</OPTIMIZER>
<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
<COST>1548</COST>
<CARDINALITY></CARDINALITY>
<BYTES></BYTES>
<OTHER_TAG></OTHER_TAG>
<DISTRIBUTION></DISTRIBUTION>
<CPU_COST></CPU_COST>
<IO_COST></IO_COST>
<TEMP_SPACE></TEMP_SPACE>
<ACCESS_PREDICATES></ACCESS_PREDICATES>
<FILTER_PREDICATES></FILTER_PREDICATES>
<PROJECTION></PROJECTION>
<TIME></TIME>
<QBLOCK_NAME></QBLOCK_NAME>
</CursorDetails><CursorDetails>
<ID>1</ID>
<PARENT_ID>0</PARENT_ID>
<DEPTH>1</DEPTH>
<POSITION>1</POSITION>
<OPERATION>SORT</OPERATION>
<OPTIONS>AGGREGATE</OPTIONS>
<OBJECT_NODE></OBJECT_NODE>
<OBJECT_NUMBER></OBJECT_NUMBER>
<OBJECT_OWNER></OBJECT_OWNER>
<OBJECT_NAME></OBJECT_NAME>
<OBJECT_ALIAS></OBJECT_ALIAS>
<OBJECT_TYPE></OBJECT_TYPE>
<OPTIMIZER></OPTIMIZER>
<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
<COST></COST>
<CARDINALITY>1</CARDINALITY>
<BYTES>13</BYTES>
<OTHER_TAG></OTHER_TAG>
<DISTRIBUTION></DISTRIBUTION>
<CPU_COST></CPU_COST>
<IO_COST></IO_COST>
<TEMP_SPACE></TEMP_SPACE>
<ACCESS_PREDICATES></ACCESS_PREDICATES>
<FILTER_PREDICATES></FILTER_PREDICATES>
<PROJECTION><![CDATA[(#keys=0) SUM("UI_COUNT")~22~]]></PROJECTION>
<TIME></TIME>
<QBLOCK_NAME>SEL$1</QBLOCK_NAME>
</CursorDetails><CursorDetails>
<ID>2</ID>
<PARENT_ID>1</PARENT_ID>
<DEPTH>2</DEPTH>
<POSITION>1</POSITION>
<OPERATION>VIEW</OPERATION>
<OPTIONS></OPTIONS>
<OBJECT_NODE></OBJECT_NODE>
<OBJECT_NUMBER></OBJECT_NUMBER>
<OBJECT_OWNER></OBJECT_OWNER>
<OBJECT_NAME></OBJECT_NAME>
<OBJECT_ALIAS>&quot;from$_subquery$_001&quot;@&quot;SEL$1&quot;</OBJECT_ALIAS>
<OBJECT_TYPE></OBJECT_TYPE>
<OPTIMIZER></OPTIMIZER>
<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
<COST>1548</COST>
<CARDINALITY>1</CARDINALITY>
<BYTES>13</BYTES>
<OTHER_TAG></OTHER_TAG>
<DISTRIBUTION></DISTRIBUTION>
<CPU_COST>327951516</CPU_COST>
<IO_COST>1541</IO_COST>
<TEMP_SPACE></TEMP_SPACE>
<ACCESS_PREDICATES></ACCESS_PREDICATES>
<FILTER_PREDICATES></FILTER_PREDICATES>
<PROJECTION><![CDATA["STUFF"~NUMBER,22~]]></PROJECTION>
<TIME>1</TIME>
<QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME>
</CursorDetails><CursorDetails>
<ID>3</ID>
<PARENT_ID>2</PARENT_ID>
<DEPTH>3</DEPTH>
<POSITION>1</POSITION>
<OPERATION>SORT</OPERATION>
<OPTIONS>AGGREGATE</OPTIONS>
<OBJECT_NODE></OBJECT_NODE>
<OBJECT_NUMBER></OBJECT_NUMBER>
<OBJECT_OWNER></OBJECT_OWNER>
<OBJECT_NAME></OBJECT_NAME>
<OBJECT_ALIAS></OBJECT_ALIAS>
<OBJECT_TYPE></OBJECT_TYPE>
<OPTIMIZER></OPTIMIZER>
<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
<COST></COST>
<CARDINALITY>1</CARDINALITY>
<BYTES>37</BYTES>
<OTHER_TAG></OTHER_TAG>
<DISTRIBUTION></DISTRIBUTION>
<CPU_COST></CPU_COST>
<IO_COST></IO_COST>
<TEMP_SPACE></TEMP_SPACE>
<ACCESS_PREDICATES></ACCESS_PREDICATES>
<FILTER_PREDICATES></FILTER_PREDICATES>
<PROJECTION><![CDATA[(#keys=0) COUNT(*)~22~]]></PROJECTION>
<TIME></TIME>
<QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME>
</CursorDetails><CursorDetails>
<ID>4</ID>
<PARENT_ID>3</PARENT_ID>
<DEPTH>4</DEPTH>
<POSITION>1</POSITION>
<OPERATION>FILTER</OPERATION>
<OPTIONS></OPTIONS>
<OBJECT_NODE></OBJECT_NODE>
<OBJECT_NUMBER></OBJECT_NUMBER>
<OBJECT_OWNER></OBJECT_OWNER>
<OBJECT_NAME></OBJECT_NAME>
<OBJECT_ALIAS></OBJECT_ALIAS>
<OBJECT_TYPE></OBJECT_TYPE>
<OPTIMIZER></OPTIMIZER>
<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
<COST></COST>
<CARDINALITY></CARDINALITY>
<BYTES></BYTES>
<OTHER_TAG></OTHER_TAG>
<DISTRIBUTION></DISTRIBUTION>
<CPU_COST></CPU_COST>
<IO_COST></IO_COST>
<TEMP_SPACE></T
EMP_SPACE>
<ACCESS_PREDICATES></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[( IS NOT NULL OR  IS NOT NULL OR ("MASTER"."DATA" IS NULL AND  IS NOT NULL))]]></FILTER_PREDICATES>
<PROJECTION></PROJECTION>
<TIME></TIME>
<QBLOCK_NAME></QBLOCK_NAME>
</CursorDetails><CursorDetails>
<ID>5</ID>
<PARENT_ID>4</PARENT_ID>
<DEPTH>5</DEPTH>
<POSITION>1</POSITION>
<OPERATION>HASH JOIN</OPERATION>
<OPTIONS></OPTIONS>
<OBJECT_NODE></OBJECT_NODE>
<OBJECT_NUMBER></OBJECT_NUMBER>
<OBJECT_OWNER></OBJECT_OWNER>
<OBJECT_NAME></OBJECT_NAME>
<OBJECT_ALIAS></OBJECT_ALIAS>
<OBJECT_TYPE></OBJECT_TYPE>
<OPTIMIZER></OPTIMIZER>
<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
<COST>1019</COST>
<CARDINALITY>1312</CARDINALITY>
<BYTES>48544</BYTES>
<OTHER_TAG></OTHER_TAG>
<DISTRIBUTION></DISTRIBUTION>
<CPU_COST>326015926</CPU_COST>
<IO_COST>1012</IO_COST>
<TEMP_SPACE></TEMP_SPACE>
<ACCESS_PREDICATES><![CDATA["MASTER"."DATA"="TYPE_REF"."DATA"]]></ACCESS_PREDICATES>
<FILTER_PREDICATES></FILTER_PREDICATES>
<PROJECTION><![CDATA[(#keys=1) "MASTER"."STUFF"~VARCHAR2,10~, "MASTER"."CT_PRIMARY_KEY2"~VARCHAR2,10~, "MASTER"."CT_PRIMARY_KEY"~VARCHAR2,15~]]></PROJECTION>
<TIME>1</TIME>
<QBLOCK_NAME></QBLOCK_NAME>
</CursorDetails><CursorDetails>
<ID>6</ID>
<PARENT_ID>5</PARENT_ID>
<DEPTH>6</DEPTH>
<POSITION>1</POSITION>
<OPERATION>TABLE ACCESS</OPERATION>
<OPTIONS>BY INDEX ROWID BATCHED</OPTIONS>
<OBJECT_NODE></OBJECT_NODE>
<OBJECT_NUMBER>81015</OBJECT_NUMBER>
<OBJECT_OWNER>OWNER</OBJECT_OWNER>
<OBJECT_NAME>Table</OBJECT_NAME>
<OBJECT_ALIAS>&quot;TYPE_REF&quot;@&quot;SEL$2&quot;</OBJECT_ALIAS>
<OBJECT_TYPE>TABLE</OBJECT_TYPE>
<OPTIMIZER></OPTIMIZER>
<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
<COST>2</COST>
<CARDINALITY>15</CARDINALITY>
<BYTES>150</BYTES>
<OTHER_TAG></OTHER_TAG>
<DISTRIBUTION></DISTRIBUTION>
<CPU_COST>20393</CPU_COST>
<IO_COST>2</IO_COST>
<TEMP_SPACE></TEMP_SPACE>
<ACCESS_PREDICATES></ACCESS_PREDICATES>
<FILTER_PREDICATES></FILTER_PREDICATES>
<PROJECTION><![CDATA["TYPE_REF"."TABLE"~VARCHAR2,5~]]></PROJECTION>
<TIME>1</TIME>
<QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME>
</CursorDetails>
</PlanXml>

 

 

 I want the data to come out looking tabular, like this:

SQL_IDCHILD_NUMBERTIMESTAMPOPERATIONOPTIONID
00j24vf2v5uay02021-04-15 07:19:52SELECT STATEMENT 

0

00j24vf2v5uay02021-04-15 07:19:52SORTAGGREGATE

1

etc...etc...etc..etc..etc..etc..

 

I can get the data to come out into a table, but each row contains a subset of the parent, and the detail values don't line up from a readable perspective: I don't know what value in one column matches to a value in an adjacent column.

This is the command I was working with:

 

 

source=*PLANXMLTEST.xml 
| table PlanXml.CursorInformation.SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP,PlanXml.CursorDetails.ID,PlanXml.CursorDetails.OPERATION,PlanXml.CursorDetails.OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER,PlanXml.CursorDetails.COST,PlanXml.CursorDetails.CARDINALITY,PlanXml.CursorDetails.BYTES,PlanXml.CursorDetails.DISTRIBUTION,PlanXml.CursorDetails.CPU_COST,PlanXml.CursorDetails.IO_COST,PlanXml.CursorDetails.TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION
| rename PlanXml.CursorInformation.SQL_ID AS SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE AS PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER AS CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP AS TIMESTAMP,PlanXml.CursorDetails.ID AS ID,PlanXml.CursorDetails.OPERATION as OPERATION,PlanXml.CursorDetails.OPTIONS AS OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER AS OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME AS OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE AS OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER AS OPTIMIZER,PlanXml.CursorDetails.COST AS COST,PlanXml.CursorDetails.CARDINALITY AS CARDINALITY,PlanXml.CursorDetails.BYTES AS BYTES,PlanXml.CursorDetails.DISTRIBUTION AS DISTRIBUTION,PlanXml.CursorDetails.CPU_COST AS CPU_COST,PlanXml.CursorDetails.IO_COST AS IO_COST,PlanXml.CursorDetails.TEMP_SPACE AS TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES AS ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES AS FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION AS PROJECTION

 

 

 

Screen Shot 2021-04-15 at 11.52.38 AM.png

Let me know if I can provide any additional info - thanks for the help! 😊

Labels (1)
Labels
Tags (2)
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎04-15-2021 12:34 PM

It looks like you still have all the fields that we (un)helpfully extracted for you when the file was indexed - try removing everything apart from the _raw field (you may want to go back to the config that ingested the data in the first place to make it more usable), also extract fields from CursorInformation

| fields _raw
| spath path=PlanXml.CursorInformation output=CursorInformation 
| spath input=CursorInformation
| fields - CursorInformation
| spath path=PlanXml.CursorDetails output=CursorDetails
| fields - _*
| mvexpand CursorDetails
| spath input=CursorDetails
| fields - CursorDetails

 

View solution in original post

Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎04-15-2021 12:04 PM

If you break out the CursorDetails collection into separate events, it is easier to keep track of what goes with what

| makeresults 
| eval _raw="<PlanXml>
	<CursorInformation>
		<SQL_ID>00j24vf2v5uay</SQL_ID>
		<ADDRESS>00000000B9AF2A00</ADDRESS>
		<HASH_VALUE>2243094878</HASH_VALUE>
		<PLAN_HASH_VALUE>3599761032</PLAN_HASH_VALUE>
		<CHILD_ADDRESS>00000000B9AF12A8</CHILD_ADDRESS>
		<CHILD_NUMBER>0</CHILD_NUMBER>
		<TIMESTAMP>2021-04-15 07:19:52</TIMESTAMP>
	</CursorInformation>
	<CursorDetails>
		<ID>0</ID>
		<PARENT_ID/>
		<DEPTH>0</DEPTH>
		<POSITION>1548</POSITION>
		<OPERATION>SELECT STATEMENT</OPERATION>
		<OPTIONS/>
		<OBJECT_NODE/>
		<OBJECT_NUMBER/>
		<OBJECT_OWNER/>
		<OBJECT_NAME/>
		<OBJECT_ALIAS/>
		<OBJECT_TYPE/>
		<OPTIMIZER>ALL_ROWS</OPTIMIZER>
		<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
		<COST>1548</COST>
		<CARDINALITY/>
		<BYTES/>
		<OTHER_TAG/>
		<DISTRIBUTION/>
		<CPU_COST/>
		<IO_COST/>
		<TEMP_SPACE/>
		<ACCESS_PREDICATES/>
		<FILTER_PREDICATES/>
		<PROJECTION/>
		<TIME/>
		<QBLOCK_NAME/>
	</CursorDetails>
	<CursorDetails>
		<ID>1</ID>
		<PARENT_ID>0</PARENT_ID>
		<DEPTH>1</DEPTH>
		<POSITION>1</POSITION>
		<OPERATION>SORT</OPERATION>
		<OPTIONS>AGGREGATE</OPTIONS>
		<OBJECT_NODE/>
		<OBJECT_NUMBER/>
		<OBJECT_OWNER/>
		<OBJECT_NAME/>
		<OBJECT_ALIAS/>
		<OBJECT_TYPE/>
		<OPTIMIZER/>
		<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
		<COST/>
		<CARDINALITY>1</CARDINALITY>
		<BYTES>13</BYTES>
		<OTHER_TAG/>
		<DISTRIBUTION/>
		<CPU_COST/>
		<IO_COST/>
		<TEMP_SPACE/>
		<ACCESS_PREDICATES/>
		<FILTER_PREDICATES/>
		<PROJECTION>
			<![CDATA[(#keys=0) SUM(\"UI_COUNT\")~22~]]>
		</PROJECTION>
		<TIME/>
		<QBLOCK_NAME>SEL$1</QBLOCK_NAME>
	</CursorDetails>
	<CursorDetails>
		<ID>2</ID>
		<PARENT_ID>1</PARENT_ID>
		<DEPTH>2</DEPTH>
		<POSITION>1</POSITION>
		<OPERATION>VIEW</OPERATION>
		<OPTIONS/>
		<OBJECT_NODE/>
		<OBJECT_NUMBER/>
		<OBJECT_OWNER/>
		<OBJECT_NAME/>
		<OBJECT_ALIAS>&quot;from$_subquery$_001&quot;@&quot;SEL$1&quot;</OBJECT_ALIAS>
		<OBJECT_TYPE/>
		<OPTIMIZER/>
		<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
		<COST>1548</COST>
		<CARDINALITY>1</CARDINALITY>
		<BYTES>13</BYTES>
		<OTHER_TAG/>
		<DISTRIBUTION/>
		<CPU_COST>327951516</CPU_COST>
		<IO_COST>1541</IO_COST>
		<TEMP_SPACE/>
		<ACCESS_PREDICATES/>
		<FILTER_PREDICATES/>
		<PROJECTION>
			<![CDATA[\"STUFF\"~NUMBER,22~]]>
		</PROJECTION>
		<TIME>1</TIME>
		<QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME>
	</CursorDetails>
	<CursorDetails>
		<ID>3</ID>
		<PARENT_ID>2</PARENT_ID>
		<DEPTH>3</DEPTH>
		<POSITION>1</POSITION>
		<OPERATION>SORT</OPERATION>
		<OPTIONS>AGGREGATE</OPTIONS>
		<OBJECT_NODE/>
		<OBJECT_NUMBER/>
		<OBJECT_OWNER/>
		<OBJECT_NAME/>
		<OBJECT_ALIAS/>
		<OBJECT_TYPE/>
		<OPTIMIZER/>
		<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
		<COST/>
		<CARDINALITY>1</CARDINALITY>
		<BYTES>37</BYTES>
		<OTHER_TAG/>
		<DISTRIBUTION/>
		<CPU_COST/>
		<IO_COST/>
		<TEMP_SPACE/>
		<ACCESS_PREDICATES/>
		<FILTER_PREDICATES/>
		<PROJECTION>
			<![CDATA[(#keys=0) COUNT(*)~22~]]>
		</PROJECTION>
		<TIME/>
		<QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME>
	</CursorDetails>
	<CursorDetails>
		<ID>4</ID>
		<PARENT_ID>3</PARENT_ID>
		<DEPTH>4</DEPTH>
		<POSITION>1</POSITION>
		<OPERATION>FILTER</OPERATION>
		<OPTIONS/>
		<OBJECT_NODE/>
		<OBJECT_NUMBER/>
		<OBJECT_OWNER/>
		<OBJECT_NAME/>
		<OBJECT_ALIAS/>
		<OBJECT_TYPE/>
		<OPTIMIZER/>
		<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
		<COST/>
		<CARDINALITY/>
		<BYTES/>
		<OTHER_TAG/>
		<DISTRIBUTION/>
		<CPU_COST/>
		<IO_COST/>
		<TEMP_SPACE>
		</TEMP_SPACE>
		<ACCESS_PREDICATES/>
		<FILTER_PREDICATES>
			<![CDATA[( IS NOT NULL OR  IS NOT NULL OR (\"MASTER\".\"DATA\" IS NULL AND  IS NOT NULL))]]>
		</FILTER_PREDICATES>
		<PROJECTION/>
		<TIME/>
		<QBLOCK_NAME/>
	</CursorDetails>
	<CursorDetails>
		<ID>5</ID>
		<PARENT_ID>4</PARENT_ID>
		<DEPTH>5</DEPTH>
		<POSITION>1</POSITION>
		<OPERATION>HASH JOIN</OPERATION>
		<OPTIONS/>
		<OBJECT_NODE/>
		<OBJECT_NUMBER/>
		<OBJECT_OWNER/>
		<OBJECT_NAME/>
		<OBJECT_ALIAS/>
		<OBJECT_TYPE/>
		<OPTIMIZER/>
		<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
		<COST>1019</COST>
		<CARDINALITY>1312</CARDINALITY>
		<BYTES>48544</BYTES>
		<OTHER_TAG/>
		<DISTRIBUTION/>
		<CPU_COST>326015926</CPU_COST>
		<IO_COST>1012</IO_COST>
		<TEMP_SPACE/>
		<ACCESS_PREDICATES>
			<![CDATA[\"MASTER\".\"DATA\"=\"TYPE_REF\".\"DATA\"]]>
		</ACCESS_PREDICATES>
		<FILTER_PREDICATES/>
		<PROJECTION>
			<![CDATA[(#keys=1) \"MASTER\".\"STUFF\"~VARCHAR2,10~, \"MASTER\".\"CT_PRIMARY_KEY2\"~VARCHAR2,10~, \"MASTER\".\"CT_PRIMARY_KEY\"~VARCHAR2,15~]]>
		</PROJECTION>
		<TIME>1</TIME>
		<QBLOCK_NAME/>
	</CursorDetails>
	<CursorDetails>
		<ID>6</ID>
		<PARENT_ID>5</PARENT_ID>
		<DEPTH>6</DEPTH>
		<POSITION>1</POSITION>
		<OPERATION>TABLE ACCESS</OPERATION>
		<OPTIONS>BY INDEX ROWID BATCHED</OPTIONS>
		<OBJECT_NODE/>
		<OBJECT_NUMBER>81015</OBJECT_NUMBER>
		<OBJECT_OWNER>OWNER</OBJECT_OWNER>
		<OBJECT_NAME>Table</OBJECT_NAME>
		<OBJECT_ALIAS>&quot;TYPE_REF&quot;@&quot;SEL$2&quot;</OBJECT_ALIAS>
		<OBJECT_TYPE>TABLE</OBJECT_TYPE>
		<OPTIMIZER/>
		<SEARCH_COLUMNS>0</SEARCH_COLUMNS>
		<COST>2</COST>
		<CARDINALITY>15</CARDINALITY>
		<BYTES>150</BYTES>
		<OTHER_TAG/>
		<DISTRIBUTION/>
		<CPU_COST>20393</CPU_COST>
		<IO_COST>2</IO_COST>
		<TEMP_SPACE/>
		<ACCESS_PREDICATES/>
		<FILTER_PREDICATES/>
		<PROJECTION>
			<![CDATA[\"TYPE_REF\".\"TABLE\"~VARCHAR2,5~]]>
		</PROJECTION>
		<TIME>1</TIME>
		<QBLOCK_NAME>SEL$54D64B3C</QBLOCK_NAME>
	</CursorDetails>
</PlanXml>"


| spath path=PlanXml.CursorDetails output=CursorDetails
| fields - _*
| mvexpand CursorDetails
| spath input=CursorDetails
| fields - CursorDetails
Reply
Solved! Jump to solution

pixeldude
Explorer
‎04-15-2021 12:22 PM

Thank you for your reply! That makes sense to me, but trying to get it formatted correctly in a table is really proving difficult. Am I missing something?

source=*PLANXMLTEST.xml PlanXml.CursorInformation.SQL_ID=00j24vf2v5uay| spath path=PlanXml.CursorDetails output=CursorDetails
| fields - _*
| mvexpand CursorDetails
| spath input=CursorDetails
| fields - CursorDetails 
| table PlanXml.CursorInformation.SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP,PlanXml.CursorDetails.ID,PlanXml.CursorDetails.OPERATION,PlanXml.CursorDetails.OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER,PlanXml.CursorDetails.COST,PlanXml.CursorDetails.CARDINALITY,PlanXml.CursorDetails.BYTES,PlanXml.CursorDetails.DISTRIBUTION,PlanXml.CursorDetails.CPU_COST,PlanXml.CursorDetails.IO_COST,PlanXml.CursorDetails.TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION
| rename PlanXml.CursorInformation.SQL_ID AS SQL_ID,PlanXml.CursorInformation.PLAN_HASH_VALUE AS PLAN_HASH_VALUE,PlanXml.CursorInformation.CHILD_NUMBER AS CHILD_NUMBER,PlanXml.CursorInformation.TIMESTAMP AS TIMESTAMP,PlanXml.CursorDetails.ID AS ID,PlanXml.CursorDetails.OPERATION as OPERATION,PlanXml.CursorDetails.OPTIONS AS OPTIONS,PlanXml.CursorDetails.OBJECT_OWNER AS OBJECT_OWNER,PlanXml.CursorDetails.OBJECT_NAME AS OBJECT_NAME,PlanXml.CursorDetails.OBJECT_TYPE AS OBJECT_TYPE,PlanXml.CursorDetails.OPTIMIZER AS OPTIMIZER,PlanXml.CursorDetails.COST AS COST,PlanXml.CursorDetails.CARDINALITY AS CARDINALITY,PlanXml.CursorDetails.BYTES AS BYTES,PlanXml.CursorDetails.DISTRIBUTION AS DISTRIBUTION,PlanXml.CursorDetails.CPU_COST AS CPU_COST,PlanXml.CursorDetails.IO_COST AS IO_COST,PlanXml.CursorDetails.TEMP_SPACE AS TEMP_SPACE,PlanXml.CursorDetails.ACCESS_PREDICATES AS ACCESS_PREDICATES,PlanXml.CursorDetails.FILTER_PREDICATES AS FILTER_PREDICATES,PlanXml.CursorDetails.PROJECTION AS PROJECTION
| sort TIMESTAMP,SQL_ID,CHILD_NUMBER,ID

Now I just get duplicate rows with all the data still out of whack:Screen Shot 2021-04-15 at 2.21.41 PM.png

0 Karma
Reply
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎04-15-2021 12:34 PM

It looks like you still have all the fields that we (un)helpfully extracted for you when the file was indexed - try removing everything apart from the _raw field (you may want to go back to the config that ingested the data in the first place to make it more usable), also extract fields from CursorInformation

| fields _raw
| spath path=PlanXml.CursorInformation output=CursorInformation 
| spath input=CursorInformation
| fields - CursorInformation
| spath path=PlanXml.CursorDetails output=CursorDetails
| fields - _*
| mvexpand CursorDetails
| spath input=CursorDetails
| fields - CursorDetails

 

Reply
Solved! Jump to solution

pixeldude
Explorer
‎04-15-2021 12:42 PM

YES - that seems to have me headed in the right direction now! Thank you SO much!

Screen Shot 2021-04-15 at 2.40.57 PM.png

I'm curious when you say go back to the config and make it more usable... Can you provide any direction there? It took me a good amount of research to get the events to parse correctly to begin with - any direction would be greatly appreciated 😊

0 Karma
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎04-15-2021 01:04 PM

To be honest, I usually leave that part to others, having said that, it appears that the extraction process has done a blanket extract of the XML and has lost the hierarchy in the process. Although, it might be worth checking to see if the extract has the hierarchy, e.g. do you have a field that contains all the CursorInformation fields and similarly as field that contains all the CursorDetails either as a multivalue field or raw XML? If so, this would save you extracting it again from the _raw.

Reply
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...