topic Re: Query to get the values out of XML data in Splunk Search

Query to get the values out of XML data

sushma7 — Mon, 28 Sep 2020 16:20:11 GMT

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

153.71.45.109:53828
110001001
sep03vvm401.mdccommonldap.com
Start time for the request i.e. where the cmd start is mentioned.
quit time for the request i.e. where the cmd quit is mentioned.
difference between the start and quit time for request.

Let me know if you need any more information.

Regards,
Sushma.

Re: Query to get the values out of XML data

Ayn — Tue, 08 Apr 2014 08:03:35 GMT

Look into using the spath command.

Re: Query to get the values out of XML data

sushma7 — Tue, 08 Apr 2014 08:32:47 GMT

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.

Re: Query to get the values out of XML data

martin_mueller — Tue, 08 Apr 2014 13:46:58 GMT

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.

Re: Query to get the values out of XML data

sushma7 — Wed, 09 Apr 2014 11:07:37 GMT

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?

Re: Query to get the values out of XML data

sushma7 — Mon, 28 Sep 2020 16:20:56 GMT

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

Re: Query to get the values out of XML data

martin_mueller — Wed, 09 Apr 2014 11:32:52 GMT

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")

Re: Query to get the values out of XML data

sushma7 — Mon, 28 Sep 2020 16:20:59 GMT

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?

Re: Query to get the values out of XML data

martin_mueller — Wed, 09 Apr 2014 12:17:38 GMT

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.

Re: Query to get the values out of XML data

sushma7 — Wed, 09 Apr 2014 12:24:26 GMT

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?

Re: Query to get the values out of XML data

martin_mueller — Wed, 09 Apr 2014 12:27:04 GMT

What kind of statistics are you looking for?

Re: Query to get the values out of XML data

sushma7 — Wed, 09 Apr 2014 13:28:35 GMT

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?

Re: Query to get the values out of XML data

sushma7 — Wed, 09 Apr 2014 13:29:13 GMT

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

Re: Query to get the values out of XML data

martin_mueller — Wed, 09 Apr 2014 16:17:36 GMT

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.