Dashboards & Visualizations

Transform Hierarchical XML into Table Rows

pixeldude
Explorer

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)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

ITWhisperer
SplunkTrust
SplunkTrust

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

pixeldude
Explorer

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

ITWhisperer
SplunkTrust
SplunkTrust

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

 

pixeldude
Explorer

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

ITWhisperer
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...