Splunk Search

How to fix column name and evaluate times?

HelloItsMe76
Explorer

I have a dbquery ouput that looks like the below, unfortunately i cant update the actual database query to make it more readable... 

 

2022-12-16 21:30:17.689, TO_CHAR(schema.function(MAX(columnA)),'MM-DD-YYHH24:MI')="12-16-22 16:29"

 

I am trying to whether time the 2 times  at the begining and end of the results are within 15 mins of each other. I have tried renaming the column from the long stupid string but i cant get that working using the rename function.  does anyone have any ideas how to rename (or if i even need to) and then evaluate whether the times are within 15 mins of each other?

the query i ran to get the above is just <index="abc">

 

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

richgalloway
SplunkTrust
SplunkTrust

Use rex to extract the two timestamps then use strptime to convert them into epoch (integer) form.  Then you can compare then to see how far apart they are.

| makeresults | eval _raw="2022-12-16 21:30:17.689, TO_CHAR(schema.function(MAX(columnA)),'MM-DD-YYHH24:MI')=\"12-16-22 16:29\""
```Above creates demo data.  Delete IRL```
| rex "(?<time1>^\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d)"
| rex "(?<time2>\d\d-\d\d-\d\d \d\d:\d\d\\\")"
| eval diffsecs = strptime(time1, "%Y-%m-%d %H:%M:%S.%3N") - strptime(time2, "%m-%d-%y %H:%M")
| eval old=if(abs(diffsecs) > (15*60),1 ,0)
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Use rex to extract the two timestamps then use strptime to convert them into epoch (integer) form.  Then you can compare then to see how far apart they are.

| makeresults | eval _raw="2022-12-16 21:30:17.689, TO_CHAR(schema.function(MAX(columnA)),'MM-DD-YYHH24:MI')=\"12-16-22 16:29\""
```Above creates demo data.  Delete IRL```
| rex "(?<time1>^\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d)"
| rex "(?<time2>\d\d-\d\d-\d\d \d\d:\d\d\\\")"
| eval diffsecs = strptime(time1, "%Y-%m-%d %H:%M:%S.%3N") - strptime(time2, "%m-%d-%y %H:%M")
| eval old=if(abs(diffsecs) > (15*60),1 ,0)
---
If this reply helps you, Karma would be appreciated.
0 Karma

HelloItsMe76
Explorer

this is beautiful. thanks so much. it works perfectly.

Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...