Splunk Search

Query to get the values out of XML data

sushma7
Path Finder

Hi,

Please find the below XML file:


20140401-05:39:58
<![CDATA[Connection established]]>
FTP
26875412
0 0
153.71.45.1:21
153.71.45.109:53828
start
F34F6ECC-6A58-4577-FE93-1F7F7C4D4C1D


20140401-05:40:11
<![CDATA[logon success (110001001)]]>
FTP
26875412
1 1
110001001
sep03vvm401.mdccommonldap.com
153.71.45.1:21
153.71.45.109:53828
PASS
<![CDATA[*****]]>
220
F34F6ECC-6A58-4577-FE93-1F7F7C4D4C1D


20140401-05:40:12
<![CDATA[]]>
FTP
26875412
1 0
110001001
sep03vvm401.mdccommonldap.com
153.71.45.1:21
153.71.45.109:53828
STOR
<![CDATA[FDD_110001001_110001000_20140401_055542_20140401_3_201404010555421515_P.XCF]]>
<![CDATA[D:\Program Files\NCR\ECPIX\Data\SystemIn\users\110001001\FDD_110001001_110001000_20140401_055542_20140401_3_201404010555421515_P.XCF]]>
86692
187
F34F6ECC-6A58-4577-FE93-1F7F7C4D4C1D


20140401-05:40:12
<![CDATA[Connection closed]]>
FTP
26875412
1 0
110001001
sep03vvm401.mdccommonldap.com
153.71.45.1:21
153.71.45.109:53828
QUIT
F34F6ECC-6A58-4577-FE93-1F7F7C4D4C1D

For this file I need a query to retrieve the values for
1. 153.71.45.1:21

  1. 153.71.45.109:53828

  2. 110001001

  3. sep03vvm401.mdccommonldap.com

  4. Start time for the request i.e. where the cmd start is mentioned.

  5. quit time for the request i.e. where the cmd quit is mentioned.

  6. difference between the start and quit time for request.

Let me know if you need any more information.

Regards,
Sushma.

Tags (1)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

As a first step, make sure your XML is indexed as one entry tag per event with the log_time value used as Splunk timestamp.
Once you've done that, using the spath command will create fields like entry.lstnconnaddr, entry.cliconnaddr, and so on - you can use those as you normally would use fields.

martin_mueller
SplunkTrust
SplunkTrust

You could for example append this:

... | timechart avg(diff)

That'll produce a chart with the average diff value over time... no idea whether that's what you are looking for or not, there are endless numbers of different statistics you could want.

0 Karma

sushma7
Path Finder

Any type of graph like bar, line or pie etc.

0 Karma

sushma7
Path Finder

I mean to say I want to generate a graph out of it, i think modifying the query a bit may help out. Is it not?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

What kind of statistics are you looking for?

0 Karma

sushma7
Path Finder

Yeah ok that worked out for me, but this query does not generate any statistical graph for me, is there a way to modify the query a bit so that i could be able to generate the query?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Not quite, the "duration" keyword is important for the tostring() function. See http://docs.splunk.com/Documentation/Splunk/6.0.2/SearchReference/CommonEvalFunctions for reference.

0 Karma

sushma7
Path Finder

so just replace eval move_time=strftime(diff, "%H:%M:%S.%3Q") with eval move_time = tostring(diff, "%H:%M:%S.%3Q"). Is that right?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

For displaying the time I recommend not using strftime(). That expects an epoch timestamp, ie seconds since January 1st, 1970 UTC. I assume you're on UTC-4 (EDT?), which shows an epoch value of 6 as four hours before midnight (plus 6 seconds) and drops the December 31st, 1969 due to the strftime() format.

Instead, use Splunk's duration converter:

... | eval end_time = strptime(...) | eval start_time = strptime(...) | eval diff = ... | eval move_time = tostring(diff, "duration")
0 Karma

sushma7
Path Finder

eval end_time=strptime(move_end,"%Y-%m-%d %H:%M:%S.%3Q") | eval start_time=strptime(move_file_start,"%Y-%m-%d %H:%M:%S.%3Q") | eval diff=end_time-start_time | eval move_time=strftime(diff, "%H:%M:%S.%3Q").. Please see this query and correct me

0 Karma

sushma7
Path Finder

Ok, i wrote a query to calculate the difference between start and end time. In my XML file the start time is 19:42:48:305 and end time is 19:42:42:080, so the actual difference between them should be 00:00:06:225, but it is showing me as 20:00:06:225, why is it happening so?

0 Karma

sushma7
Path Finder

I knew that we have to use spath so as to retrieve the xml values, but I am not clear as how to use in case of my above requirement.

0 Karma

Ayn
Legend

Look into using the spath command.

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