topic Re: Transaction command in Oracle XML Logging in Splunk Search

Transaction command in Oracle XML Logging

imarks004 — Tue, 29 Mar 2011 00:45:07 GMT

I am trying to do a search that matches on the term of commit, then use the transaction statement to tie it back to everything that has the same OS_Process field but since every event does not contain the term commit, it does not show all the events. Is this possible? This is search "sourcetype=oracle_xml_* commit | transaction OS_Process | table Sql_Text | reverse"

Re: Transaction command in Oracle XML Logging

David — Tue, 29 Mar 2011 01:14:38 GMT

I'm not sure how to get you 100% there, without playing with the data, but I'd try to avoid using Transaction, as it slows down the search considerably. I might try one of these options and see how close they get you:

[search sourcetype=oracle_xml_* commit | dedup OS_Process | fields OS_Process] 
       | table _time OS_Process Sql_Text 
       | sort OS_Process, _time

[search sourcetype=oracle_xml_* commit | dedup OS_Process | fields OS_Process] 
       | stats values(Sql_Text) by OS_Process

These will do a subsearch of the logs for anything containing "commit" and then pull out just the OS_Process. That way, you can do a search for just logs that actually have a commit in them, speeding up the search. You can then try to do a stats values(), or just a table with the texts. I think the latter is probably closest to what you want, but I'm not sure whether values() will sort the data oddly.

Re: Transaction command in Oracle XML Logging

imarks004 — Tue, 29 Mar 2011 19:24:59 GMT

Thanks David, this worked great for me.

Re: Transaction command in Oracle XML Logging

David — Tue, 29 Mar 2011 23:50:58 GMT

Excellent! I'm glad to hear that solved it for you.