Splunk Search
Highlighted

How to edit my search to perform eval math on fields extracted from XML data?

I have a set of XML logs that were all consumed by Splunk at the same time. I believe I have the timestamps from the logs properly read in my search, and I am getting reasonable results for just reporting on some fields, but I want to be able to perform some math using 2 existing fields and I am running into some trouble.

Here is a sample log in XML format:

<interface>
     <rpc>
          <request id="">
               <command>get-database-statistics</command>
          </request>
                <response>
                    <statistics>
                        <database-requests>
                            <preface>
                                <now>2015-04-05T07:30:01-04:00</now>
                                <cleared>2015-04-05T07:00:01-04:00</cleared>
                                <start-time>2015-04-03T18:38:19-04:00</start-time>
                                <sequence>1</sequence>
                                <process-id>13119</process-id>
                            </preface>
                            <counts></counts>
                            <totals>
                                <search-requests>440776</search-requests>
                                <records>1027353</records>
                                <sets>931302</sets>
                                <exceptions>45</exceptions>
                                <success>437823</success>
                                <err-toomany>418</err-toomany>
                                <err-syntax>2535</err-syntax>
                                <search-time>1299.498347</search-time>
                                <update-time>0</update-time>
                                <queue-time>15.141938</queue-time>
                            </totals>
                            <highwater>
                                <threads-total>6</threads-total>
                                <records>5788</records>
                            </highwater>
                        </database-requests>
                    </statistics>
                </response>
                <status>
                    <code>0</code>
                    <more-info></more-info>
                </status>
            </rpc>
        </interface>

And here is a sample search I am trying:

index=* sourcetype=xml-too_small | xmlkv | spath output=total_records path=interface.rpc.response.statistics.database-requests.totals.records | eval time=tostring(cleared) | eval recspereq=total_records/search-requests | stats max(search-requests), max(total_records), max(success), max(exceptions), max(recsperreq) by time

The "spath" part is there because there are two tags and xmlkv only finds the second one. The timestamp is the one I want to use for "time" and while the formatting isn't perfect I couldn't seem to get that cleaned up either. I know it's not elegant, but it seems to work. My current issue is that I get no results at all for "recsperreq". In the stats all the fields under that column are blank. I would like that field to be the value of "totalrecords" (which is the XML field in the section) divided by the value of search-requests.

I'm sure I am missing something simple here but I can't for the life of me figure out what it is.

Tags (3)
0 Karma
Highlighted

Re: How to edit my search to perform eval math on fields extracted from XML data?

SplunkTrust
SplunkTrust

The trouble is, in English your calculation reads like this: "Take total_records, divide by search, subtract requests"... operators such as minus signs or dots in fields names are trouble if not treated properly.

This works:

| stats count as _raw | eval _raw = "<interface>
  <rpc>
       <request id=\"\">
            <command>get-database-statistics</command>
       </request>
             <response>
                 <statistics>
                     <database-requests>
                         <preface>
                             <now>2015-04-05T07:30:01-04:00</now>
                             <cleared>2015-04-05T07:00:01-04:00</cleared>
                             <start-time>2015-04-03T18:38:19-04:00</start-time>
                             <sequence>1</sequence>
                             <process-id>13119</process-id>
                         </preface>
                         <counts></counts>
                         <totals>
                             <search-requests>440776</search-requests>
                             <records>1027353</records>
                             <sets>931302</sets>
                             <exceptions>45</exceptions>
                             <success>437823</success>
                             <err-toomany>418</err-toomany>
                             <err-syntax>2535</err-syntax>
                             <search-time>1299.498347</search-time>
                             <update-time>0</update-time>
                             <queue-time>15.141938</queue-time>
                         </totals>
                         <highwater>
                             <threads-total>6</threads-total>
                             <records>5788</records>
                         </highwater>
                     </database-requests>
                 </statistics>
             </response>
             <status>
                 <code>0</code>
                 <more-info></more-info>
             </status>
         </rpc>
     </interface>"
| spath
| table interface.rpc.response.statistics.database-requests.totals.records interface.rpc.response.statistics.database-requests.totals.search-requests
| eval recsperreq = 'interface.rpc.response.statistics.database-requests.totals.records' / 'interface.rpc.response.statistics.database-requests.totals.search-requests'

Note the single quotes around the field names... alternatively you could rename them to something purely \w before that, or tell spath to extract them into specific fields directly.

View solution in original post

Highlighted

Re: How to edit my search to perform eval math on fields extracted from XML data?

It looks like you are correct, I felt it would be something simple that I was missing and I didn't even think about how a dash in a field name would be interpreted as a minus. Thanks!

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.