Create a table with _time and a custom fields

[dsmithson8812]

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.

[somesoni2 · Accepted solution]

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]

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

[dsmithson8812]

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.

[dsmithson8812]

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.

[somesoni2]

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.

[dsmithson8812]

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.

[sbbadri]

@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

[dsmithson8812]

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.

[jeremiahc4]

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

[dsmithson8812]

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

[somesoni2]

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

[dsmithson8812]

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.

[somesoni2]

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

[dsmithson8812]

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?