Splunk Search

How to do time conversion during search runtime?

anandhalagaras1
Communicator

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

 

Labels (2)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| rex "(?<seconds>\d+)s"
| rex "(?<milli>\d+)ms"
| rex "(?<micro>\d+)µs"
| rex "(?<nano>\d+)ns"
| fillnull value=0 seconds milli micro nano
| eval seconds=round(seconds+(((((nano/1000)+micro)/1000)+milli)/1000),9)

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| rex "(?<seconds>\d+)s"
| rex "(?<milli>\d+)ms"
| rex "(?<micro>\d+)µs"
| rex "(?<nano>\d+)ns"
| fillnull value=0 seconds milli micro nano
| eval seconds=round(seconds+(((((nano/1000)+micro)/1000)+milli)/1000),9)
0 Karma

anandhalagaras1
Communicator

@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:

index=abc host=xyz | rex "(?<seconds>\d+)s"
| rex "(?<milli>\d+)ms"
| rex "(?<micro>\d+)µs"
| rex "(?<nano>\d+)ns"
| fillnull value=0 seconds milli micro nano
| eval seconds=round(seconds+(((((nano/1000)+micro)/1000)+milli)/1000),9)

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

anandhalagaras1
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

anandhalagaras1
Communicator
  • @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.
  •  
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

PickleRick
Ultra Champion

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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"

anandhalagaras1
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...