Splunk Search

How to do time conversion during search runtime?

anandhalagaras1
Path Finder

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
Path Finder

@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
Path Finder

@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
Path Finder
  • @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
Path Finder

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

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...