Splunk Search

Create a table with _time and a custom fields

dsmithson8812
Engager

I'm lost. I'm trying to capture the _time and UserName (custom field) from a search and use the _time to find events within 1 second in another search. I would then want to report the result in a simple table: _time; UserName; real_ip_address.

Any help would be appreciated! Thank you.

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

host=app* "Login attempt failed" "https://www.website.org"
| table _time UserName | eval et=_time-1 | eval lt=_time+2 | map search="search index=_internal host=lb* earliest=$et$ latest=$lt$ | table _time real_ip_address  | eval UserName=\"$UserName$\" "

View solution in original post

somesoni2
Revered Legend

Give this a try

host=app* "Login attempt failed" "https://www.website.org"
| table _time UserName | eval et=_time-1 | eval lt=_time+2 | map search="search index=_internal host=lb* earliest=$et$ latest=$lt$ | table _time real_ip_address  | eval UserName=\"$UserName$\" "

dsmithson8812
Engager

I found that the second search was pulling from the wrong index. I changed the index to main and now real_ip_address is pulled. There are some additional problems. The time window designated by earliest and latest is ignored. Perhaps if you see the results it will be clear. Here are the redacted results:

_time real_ip_address UserName
2017-09-22T14:24:49.000-0400 -" user1
2017-09-22T14:24:49.000-0400 10.100.100.100 user1
2017-09-22T13:53:03.000-0400 10.100.100.101 user2
2017-09-22T13:52:56.000-0400 10.100.100.101 user2
2017-09-22T13:52:55.000-0400 -" user2
2017-09-22T09:51:04.000-0400 user3
2017-09-22T09:51:05.000-0400 10.100.100.102 user3
2017-09-22T09:51:05.000-0400 10.100.100.102 user3

0 Karma

dsmithson8812
Engager

This worked for me after some troubleshooting and tweaking to narrow the scope of the map search.

Would appreciate if a moderator would name the title of this thread appropriately. It was renamed by a moderator to something that doesn't fit.

Thank you somesoni2 et al.

0 Karma

dsmithson8812
Engager

It returns a result, but it has some problems:

One problem is that it doesn't pull real_ip_address from the second search. The name of the field is correct, so I'm not sure why it's not there.

Another problem is that it returns 22 rows in the table. It should only return 1 row.

Oddly (to me anyhow) if I use _time+1 instead of +2, there are no results. I'm sure there's a good reason for this, but confused by it.

0 Karma

somesoni2
Revered Legend

When you specify the timerange, latest time-range is exclusive, hence I added 2 to lt. But in that 0.99 sec there might be other events that get caught. Is there any other field that can related/filter results from Search 2? With time as only filter/relation, there is high chance you get other non-relevant results.
For real_ip_address, check if you get that field when you run just search2 separately.

0 Karma

dsmithson8812
Engager

Search 1:

host=app* "Login attempt failed" "https://www.website.org"

Result:

Sep 22 09:51:05 app1 drupal-prod: https://www.website.org|1506088265|user|123.123.123.123|https://www.website.org/login_redirect?desti... attempt failed for UserName.

Search 2: This search is run with a _time accelerator of +/- 1 second.

host=lb*

Result:

123.123.123.123 - - [22/Sep/2017:09:51:05 -0400] "POST /login_redirect?destination=login_redirect HTTP/1.1" 403 8707 "https://website.org/" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:55.0) Gecko/20100101 Firefox/55.0" "124.124.124.124"

Goal:

When Search 1 finds a match, get the timestamp and the UserName. Use the timestamp from Search 1 with +/- 1 second to run Search 2. Report timestamp, UserName, IP Address.

In the example above 124.124.124.124 is the real_ip_address extracted.

0 Karma

sbbadri
Motivator

@dsmithson8812

try this,
i assume that you have extracted UserName and real_ip_address

index=x sourcetype=x source=x earliest=-1s latest=now | regex for UserName and real_ip_address | table _time UserName real_ip_address

0 Karma

dsmithson8812
Engager

In other words, I need to correlate two logs based on timestamp. For some reason the title of my post was changed. It doesn't really capture the gist of what I need here.

0 Karma

jeremiahc4
Builder

do you have any redacted sample logs you could post here as well as the 2 searches you would use manually to find the events of interest in the 2 separate locations? would make it slightly easier to help out

0 Karma

dsmithson8812
Engager

I have posted samples and such as comment to my original post. Thank you for looking at this.

0 Karma

somesoni2
Revered Legend

Can you also post searches to query both the data? How many records do you get from search 1 (which has _time and UserName)?

0 Karma

dsmithson8812
Engager

Ultimately it's for a real-time search and immediate notification by email. So it's one record set at a time.

I didn't understand the first part of your question. You asked to post searches to query both the data. I thought I had done that in Search 1 and Search 2.

0 Karma

somesoni2
Revered Legend

Sorry about the first question.. didn't see your comment under the question. Give my answer a try..

0 Karma

dsmithson8812
Engager

I think I need to give you more information. There are logs for two different hosts, so I have to search one host first to get the event I'm looking for, then I have to take the timestamp and search another host for events within 1 second of the first search.

Does that make sense?

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...