topic Re: Create a table with _time and a custom fields in Splunk Search

Create a table with _time and a custom fields

dsmithson8812 — Tue, 29 Sep 2020 15:54:50 GMT

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.

Re: Create a table with _time and a custom fields

sbbadri — Tue, 29 Sep 2020 15:51:08 GMT

@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

Re: Create a table with _time and a custom fields

dsmithson8812 — Fri, 22 Sep 2017 17:47:19 GMT

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?

Re: Create a table with _time and a custom fields

dsmithson8812 — Fri, 22 Sep 2017 17:52:48 GMT

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.

Re: Create a table with _time and a custom fields

jeremiahc4 — Fri, 22 Sep 2017 18:30:56 GMT

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

Re: Create a table with _time and a custom fields

dsmithson8812 — Tue, 29 Sep 2020 15:54:57 GMT

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?destination=login_redirect|https://website.org/|0||Login 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.

Re: Create a table with _time and a custom fields

dsmithson8812 — Fri, 22 Sep 2017 18:58:01 GMT

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

Re: Create a table with _time and a custom fields

somesoni2 — Fri, 22 Sep 2017 19:03:48 GMT

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

Re: Create a table with _time and a custom fields

dsmithson8812 — Fri, 22 Sep 2017 19:06:52 GMT

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.

Re: Create a table with _time and a custom fields

somesoni2 — Fri, 22 Sep 2017 19:53:45 GMT

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$\" "

Re: Create a table with _time and a custom fields

somesoni2 — Fri, 22 Sep 2017 19:54:24 GMT

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

Re: Create a table with _time and a custom fields

dsmithson8812 — Tue, 29 Sep 2020 15:55:03 GMT

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.

Re: Create a table with _time and a custom fields

dsmithson8812 — Tue, 29 Sep 2020 15:55:06 GMT

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

Re: Create a table with _time and a custom fields

somesoni2 — Tue, 29 Sep 2020 15:55:09 GMT

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.

Re: Create a table with _time and a custom fields

dsmithson8812 — Thu, 28 Sep 2017 05:12:45 GMT

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.