Splunk Search

SPLUNK query

sushma6
New Member

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.

Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

sushma6
New Member

Appreciate your help! and am getting the required data

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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
0 Karma

rakesh_498115
Motivator

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

0 Karma

sushma6
New Member

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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
0 Karma

sushma6
New Member

Any idea on this, please help!

0 Karma

sushma6
New Member

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

0 Karma

sushma6
New Member

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

sushma6
New Member

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

0 Karma

sushma6
New Member

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

0 Karma

sushma6
New Member

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...

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Check the field list on the left for avg.

0 Karma

sushma6
New Member

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

0 Karma

sushma6
New Member

they are two separate events not the single event

0 Karma

linu1988
Champion

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

0 Karma

sushma6
New Member

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...