topic Re: Time Conversion During Search Runtime in Splunk Search

How to do time conversion during search runtime?

anandhalagaras1 — Sat, 12 Mar 2022 06:02:50 GMT

Hi Team,

We got an requirement to create a report based on the accessed time present in the logs here in the logs the time is present with seconds, milliseconds, microseconds, nanoseconds value.

Example: 1s79ms874µs907ns So here in this case how to convert them into a unique value. So post which we need to check and create a report for the same. In most of the cases the time is getting started with milliseconds and in few cases the time information is getting started with seconds.

So how to convert the time (1s79ms874µs907ns) to an unique value either in seconds, milliseconds , microseconds or nanoseconds so then only we can able to create a report for the same.

Or do we have any other option to fix this issue while searching the logs during runtime.

So kindly help on my request.

Sample Logs for Reference:

DEBUG 2022-03-10 07:17:26,239 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: abcdefgh-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 145ms227µs975ns ago, IN_USE DEBUG 2022-03-10 07:07:26,239 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: ijklmnop-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 1s79ms874µs907ns ago, IN_USE DEBUG 2022-03-10 07:02:26,238 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: qrstuvwx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 7ms215µs946ns ago, IN_USE DEBUG 2022-03-10 06:57:26,237 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: qrstuvwx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 168ms259µs830ns ago, IN_USE DEBUG 2022-03-10 06:57:26,237 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: abcdefgh-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 6s993ms781µs523ns ago, IN_USE DEBUG 2022-03-10 06:47:26,238 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: ijklmnop-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 2ms593µs888ns ago, IN_USE DEBUG 2022-03-10 06:47:26,238 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: qrstuvwx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 55ms239µs616ns ago, IN_USE DEBUG 2022-03-10 06:47:26,238 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: abcdefgh-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 957ms778µs205ns ago, IN_USE DEBUG 2022-03-10 06:47:26,238 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: ijklmnop-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 45ms536µs884ns ago, IN_USE DEBUG 2022-03-10 06:47:26,238 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: qrstuvwx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 22ms906µs437ns ago, IN_USE DEBUG 2022-03-10 06:47:26,238 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: abcdefgh-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 46ms556µs466ns ago, IN_USE DEBUG 2022-03-10 06:42:26,236 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: ijklmnop-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 3s286ms410µs997ns ago, IN_USE DEBUG 2022-03-10 06:37:26,239 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: qrstuvwx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 842ms323µs432ns ago, IN_USE DEBUG 2022-03-10 06:27:26,236 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: abcdefgh-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 7ms698µs576ns ago, IN_USE DEBUG 2022-03-10 06:27:26,236 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: ijklmnop-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 18ms948µs359ns ago, IN_USE DEBUG 2022-03-10 06:17:26,236 [Timer-x] com.abc.valid.AppData - EntryData >>>>>>>>ConnectionID:xxxxx ClientConnectionId: qrstuvwx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, accessed 257ms32µs814ns ago, IN_USE

Re: Time Conversion During Search Runtime

ITWhisperer — Thu, 10 Mar 2022 13:59:37 GMT

Re: Time Conversion During Search Runtime

PickleRick — Thu, 10 Mar 2022 14:05:46 GMT

I'd say that single regex would be a nicer solution. In this case probably the efficiency won't matter that much but in general single strict regex is usually better performing than four separate ones.

Re: Time Conversion During Search Runtime

ITWhisperer — Thu, 10 Mar 2022 14:20:50 GMT

Yes, a single rex might be better if you can assume that (at least) one of the units is always present .e.g ns

| rex "((?<seconds>\d+)s)?((?<milli>\d+)ms)?((?<micro>\d+)µs)?(?<nano>\d+)ns"

Re: Time Conversion During Search Runtime

anandhalagaras1 — Thu, 10 Mar 2022 15:34:58 GMT

@ITWhisperer ,

Thank you. Now I can see the fields are getting extracted as desired. Our requirement is to create a report for the same i.e. If the total time taken is taking longer than expected then we need to get an alert and also if it comes under a visualization has a graph then it will be good as well.

So is there any possibility to convert the value of seconds, nanoseconds, milliseconds & microseconds in a event to a single value. If yes then we can try to get a visualization as expected.

Kindly help on the same.

Re: Time Conversion During Search Runtime

ITWhisperer — Thu, 10 Mar 2022 15:38:04 GMT

That's what the rest of my previous answer does i.e. converts the extracted values to seconds

Re: Time Conversion During Search Runtime

anandhalagaras1 — Wed, 06 Apr 2022 07:42:31 GMT

@ITWhisperer ,Thank you. The below mentioned solution worked as expected.

But we want to get the data display in a graph format. So I have amended the query as below:

and then I have chosen Visualization-->Pivot-->Selected Fields.

Then i have chosen the Line Chart. And now I can see X Axis with Date and Y Axis with Seconds information.

So there are events with the same timing but with different seconds.

2022-03-23 00:51:53,113 Seconds 14.827107589
2022-03-23 00:51:53,113 Seconds 0.000293807
2022-03-23 00:51:53,113 Seconds 0.000333807
2022-03-23 00:51:53,113 Seconds 0.000436807
2022-03-23 00:51:53,113 Seconds 0.000781807

But in this graph, I can see the timing in X Axis whereas in Y axis for this timing 2022-03-23 00:51:53,113 i can see the count of Seconds ( i.e. Addition of all seconds and producing a count) for that time but we need the data in the graph for each and every events. So how to update the query so that for each and every event i need the graph output.

So kindly help

Re: Time Conversion During Search Runtime

ITWhisperer — Wed, 06 Apr 2022 07:57:50 GMT

If you are trying to plot every event against the time it occurred then a scatter plot might be more appropriate. A line graph would be used for plotting one or more series of values (over time usually).

Re: Time Conversion During Search Runtime

anandhalagaras1 — Wed, 06 Apr 2022 09:44:18 GMT

@ITWhisperer ,

Thanks for your response. But is there any possibility to bring the data for each event in Line graph or Bar chart or Column Chart? And also for scatter chart how to mention the x axis and y axis information. Kindly help.

Re: Time Conversion During Search Runtime

ITWhisperer — Wed, 06 Apr 2022 09:48:23 GMT

What is it that you are trying to visualise with a line or bar chart?

For a scatter chart, looking at your data, I would imagine that the x-axis would be the time and the y-axis would be seconds

Re: Time Conversion During Search Runtime

anandhalagaras1 — Wed, 06 Apr 2022 10:16:46 GMT

@ITWhisperer When I choose the Scatter chart there is a field as Mark so I have chosen the _time and Periods has been set to Auto and then for X axis what should I need to choose and for Y Axis should i need to choose the seconds? Since the data seems to be sluggish when i check now.

Re: Time Conversion During Search Runtime

ITWhisperer — Wed, 06 Apr 2022 10:58:27 GMT

It looks like _time might not work well with scatter charts 🤐 If the times are all from the same day, you could use mod 84600

| eval time=_time%84600 | fields - _time