topic Re: SPLUNK query in Splunk Search

SPLUNK query

sushma6 — Tue, 11 Mar 2014 08:17:19 GMT

Hi,

I had input some logs into splunk and now I need someone's help to write a query such that I get the reults in the way I want.

I ran the following search query:

host=SCAWTR813 source="E:\\SPLUNK\\FTPlogs _HYDservers\\APP04\\ifs20140310.xml" AND "filesize" OR "transtime"

and the output i received is :

3/10/14 
5:09:11.000 PM  
    <transtime>686</transtime>
host = SCAWTR813 source = E:\SPLUNK\FTPlogs _HYDservers\APP04\ifs20140310.xml sourcetype = xml
3/10/14 
5:09:11.000 PM  
    <filesize>273431</filesize>
host = SCAWTR813 source = E:\SPLUNK\FTPlogs _HYDservers\APP04\ifs20140310.xml sourcetype = xml

Now at the same timestamp I have filesize and transtime.

I need a query such that I get the value of filesize/timestamp for a particular timestamp.

Please help!

Regards,
Sushma.

Re: SPLUNK query

martin_mueller — Tue, 11 Mar 2014 13:22:55 GMT

Is there a reason for breaking the XML file into several small events?

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 15:52:18 GMT

Because I need to get filesize and transtime values out of the xml file, and then perform filesize/transtime, need a query for this

Re: SPLUNK query

linu1988 — Tue, 11 Mar 2014 16:14:41 GMT

are these two events or single event containing both the details?

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 16:18:35 GMT

they are two separate events not the single event

Re: SPLUNK query

martin_mueller — Tue, 11 Mar 2014 16:35:55 GMT

You can do this:

host=SCAWTR813 source="E:\\SPLUNK\\FTPlogs _HYDservers\\APP04\\ifs20140310.xml" AND "filesize" OR "transtime" | spath | eventstats avg(filesize) as filesize avg(transtime) as transtime | eval avg=filesize/transtime

That assumes your search only yields those two events listed in your question, nothing else. It copies each field into the other event and adds the average to each event.

Note, I still recommend questioning why these events are split up instead of being part of one larger event for the XML file.

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 16:48:09 GMT

I ran the your query but the output I get is same as the one as posted above what i need is filesize/transtime

Re: SPLUNK query

martin_mueller — Tue, 11 Mar 2014 16:50:43 GMT

Check the field list on the left for avg.

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 16:52:56 GMT

Actually i have not split the events i just gave the xml file as input and from the xml file i was searching for keywords filesize and transtime,to do so i ran the above query and i could get list of filesize and transtime keywords out of it,but not sure how to retrive the value between the xml tags i.e. the output above shows 234 and 24 and both are displayed as two different events, now what i want is 234/24, calculate and place the output in a new varaiable. hope now I am clear. I even tried out the command xmlkv, xpath and spath but no use...

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 16:58:46 GMT

To the left on the field list I could find avg and a single value for it, I dont want avg(filesize)/avg(transtime).
At a partcular timestamp filesize and transtime are generated in my excel so at that period what is the value of filesize/transtime, at another timestamp these two values may be different so i need the value at different timestamps

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 17:02:05 GMT

Sample output of transtime is as follows:

1 Value, 100% of events

Reports
Average over timeMaximum value over timeMinimum value timeTop valuesTop values by timeRare valuesEvents with this fieldAvg: 4378.691457 Min: 4378.691457 Max: 4378.691457 Std Dev: 0 Values Count %

4378.691457 88,146 100%

Similary for filesize and avg..but it is just showing the avg of all the filesize and transtime

Re: SPLUNK query

martin_mueller — Tue, 11 Mar 2014 17:04:36 GMT

You can add a by _time to the eventstats if you're certain the timestamps for both events will always be identical and unique.

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 17:16:11 GMT

Sample output of the query:
3/10/14
5:09:11.000 PM 686

host = SCAWTR813 source = E:\SPLUNK\FTPlogs _HYDservers\APP04\ifs20140310.xml sourcetype = xml
3/10/14
5:09:11.000 PM 273431

filesize = 273431 host = SCAWTR813 source = E:\SPLUNK\FTPlogs _HYDservers\APP04\ifs20140310.xml sourcetype = xml

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 17:17:44 GMT

Now if you see the above query on 10th March at 10PM i have the values for filesize and transtime, i need the output of filesize/transtime, similarly i have values for filesize and transtime at 11 on the same day, i need the value for filesize/transtime

Re: SPLUNK query

sushma6 — Tue, 11 Mar 2014 18:01:18 GMT

Any idea on this, please help!

Re: SPLUNK query

martin_mueller — Tue, 11 Mar 2014 18:30:30 GMT

What's wrong with this?

host=SCAWTR813 source="E:\\SPLUNK\\FTPlogs _HYDservers\\APP04\\ifs20140310.xml" AND "filesize" OR "transtime" | spath | eventstats avg(filesize) as filesize avg(transtime) as transtime by _time | eval avg=filesize/transtime

Re: SPLUNK query

sushma6 — Wed, 12 Mar 2014 04:59:10 GMT

If you dont mind could you please explain me the query in detail..Actually under the avg field to the left, I could find some values but I could not find out which value of filesize/transtime has been listed there

Re: SPLUNK query

rakesh_498115 — Wed, 12 Mar 2014 07:06:21 GMT

Hi Sushma6,

Probably u need this ..

host=SCAWTR813 source="E:\SPLUNK\FTPlogs _HYDservers\APP04\ifs20140310.xml" AND "filesize" OR "transtime" | transaction keepevicted=true _time | rex "filesize>(?[^<])<" | rex"transtime>(?[^<])<" | eval MyField = filesize + "/" + transtime | table _time,MyField

Re: SPLUNK query

martin_mueller — Wed, 12 Mar 2014 08:16:00 GMT

My query works like this:

search for your events
apply spath field extraction from structured XML data
compute the average value for both fields for every timestamp
calculate filesize divided by transtime, store in field avg

If you're confused by the output of eventstats, adding fields to the existing events, you can change to stats instead:

host=SCAWTR813 source="E:\\SPLUNK\\FTPlogs _HYDservers\\APP04\\ifs20140310.xml" AND "filesize" OR "transtime" | spath | stats avg(filesize) as filesize avg(transtime) as transtime by _time | eval avg=filesize/transtime

Re: SPLUNK query

sushma6 — Wed, 12 Mar 2014 10:26:30 GMT

Appreciate your help! and am getting the required data