topic Re: How to pull data from a lookup within a date range? in Knowledge Management

How to pull data from a lookup within a date range?

batsonpm — Tue, 29 Sep 2020 20:38:47 GMT

I created a lookup definition, account_admin, for a csv file that I have.
ark_admin - file - Time,User,Source IP,Service Account,Action,Service Account Path,Server Connection, Message

Here is a sample of the lookup data:
Action, Message, Server Connection, Service Path, Service Acct, Source IP, Time, User
Retrieve password,126.259.193.138,(Action: Connect)(Connection to address: server1234),ServerAdmins.com-PROD_5,PROD_5,126.259.125.36,07/10/2018 15:45:41,Some.Guy@nowhere.com

Here is a sample of the table1 data:
table1_time, email, host, _time
07/24/2018 09:26:01, Some.Guy@nowhere.com, server1234, 2018-07-24 10:30:00.987
07/23/2018 06:16:01, Some.Guy@nowhere.com, server3234, 2018-07-23 11:30:00.987
07/22/2018 08:16:01, Some.Guy@nowhere.com, server2234, 2018-07-22 09:30:00.987

When I run my lookup now, it pulls all the servers for the specific email address I'm searching for (User as email) and adds it to the same record. I would like to limit the list to a specific date range. When I run the following, I get no results.

sourcetype=table1
| eval endepochDate=relative_time(strptime(table1_time, "%m/%d/%Y %H:%M:%S"), "+72H@H")
| eval endate = strftime(endepochDate, "%m/%d/%Y %H:%M:%S")
| lookup account_admin User as email OUTPUTNEW "Server Connection" AS servcon "Service Account" AS servacct Time AS ark_time
| where ark_time > table1_time and ark_time <= endate

All the dates are the same format, "%m/%d/%Y %H:%M:%S", so I'm not sure why it isn't working. If I need to compare by converting to Unix time, how would I do that with the lookup?

Thanks!

Re: How to pull data from a lookup within a date range?

skoelpin — Wed, 25 Jul 2018 17:06:37 GMT

Have you tried using OUTPUT instead of OUTPUTNEW? Do you have an existing field you want your lookup to overwrite?

Re: How to pull data from a lookup within a date range?

somesoni2 — Wed, 25 Jul 2018 17:45:43 GMT

Give this a try

sourcetype=table1
| lookup account_admin User as email OUTPUTNEW "Server Connection" AS servcon "Service Account" AS servacct Time AS ark_time 
| where strptime(ark_time, "%m/%d/%Y %H:%M:%S") > strptime(table1_time, "%m/%d/%Y %H:%M:%S") and strptime(ark_time, "%m/%d/%Y %H:%M:%S") <= relative_time(strptime(table1_time, "%m/%d/%Y %H:%M:%S"),"+72h")

Re: How to pull data from a lookup within a date range?

akocak — Wed, 25 Jul 2018 17:45:56 GMT

I think you do some unnecessary conversion on time fields. you could simply connect all in epoch with strptime() and make your comparisons

| eval endepochDate=relative_time(strptime(table1_time, "%m/%d/%Y %H:%M:%S"), "+72H@H") 
| lookup account_admin User as email OUTPUTNEW "Server Connection" AS servcon "Service Account" AS servacct Time AS ark_time 
| where strptime(ark_time,"%m/%d/%Y %H:%M:%S") > strptime(table1_time,"%m/%d/%Y %H:%M:%S") and strptime(ark_time,"%m/%d/%Y %H:%M:%S") <= endepochDate

Re: How to pull data from a lookup within a date range?

batsonpm — Tue, 29 Sep 2020 20:39:01 GMT

Not getting any results with those. I have tested, and verified that the strptime() functions that were suggested are working. I think the problem is that when I do the lookup, I am getting multiple matches for each lookup.

For example the result I get without the where statement produces the following results:

User, Server, ark_time, table1_time
Some.Guy@nowhere.com, server1234 server2234 server3234 server4234 server5234, 07/11/2018 14:29:00 07/24/2018 09:44:49 07/24/2018 11:13:51 07/10/2018 15:45:41 07/10/2018 15:45:41, 07/24/2018 09:26:00

It looks like when it does the lookup, it grabs all matching data for the user account (User) and puts it all into one record. So I get multiple servers and multiple dates related to those servers, but they are placed in different fields. It doesn't seem like it works like when you compare table to table, where there would be multiple records for the same search. It looks like it is putting all the data it finds into fields for the single record.

Is there a way to do the lookup differently? Or is there another function I need to use to break out the multiple entries in the fields into records that I can then search for the date range?

Thanks!

Re: How to pull data from a lookup within a date range?

somesoni2 — Wed, 25 Jul 2018 19:53:15 GMT

Try this

sourcetype=table1
 | lookup account_admin User as email OUTPUTNEW "Server Connection" AS servcon "Service Account" AS servacct Time AS ark_time | mvexpand ark_time
 | where strptime(ark_time, "%m/%d/%Y %H:%M:%S") > strptime(table1_time, "%m/%d/%Y %H:%M:%S") and strptime(ark_time, "%m/%d/%Y %H:%M:%S") <= relative_time(strptime(table1_time, "%m/%d/%Y %H:%M:%S"),"+72h")

Re: How to pull data from a lookup within a date range?

batsonpm — Wed, 25 Jul 2018 20:15:49 GMT

That's closer! So now it is selecting the records within the times, but I have multiple servers in those records.
I'm going down this path, not sure if this is the right way:

| lookup account_admin User as email OUTPUTNEW "Server Connection" AS servcon "Service Account" AS servacct Time AS ark_time 
| mvexpand ark_time
| where strptime(ark_time, "%m/%d/%Y %H:%M:%S") > strptime(table1_time, "%m/%d/%Y %H:%M:%S") and strptime(ark_time, "%m/%d/%Y %H:%M:%S") <= relative_time(strptime(table1_time, "%m/%d/%Y %H:%M:%S"),"+72h")
| rex field=servcon "address: (?<Server>[^)]+)"
| eval fields1= mvzip(mvzip (Server, servacct),ark_time)
| mvexpand fields1
| rex field=fields1 "(?<serveraccessed>\w+),(?<serviceacct>\w+),(?<timeaccessed>\d+\D\d+\D\d+\D\S\d+\D\d+\D\d+)"

fields1 = server1234,PROD_7,07/25/2018 15:45:35
But it doesn't look like the last rex is working.

Update: got the rex to work with the above syntax. Still learning the whole regex/rex stuff.

Re: How to pull data from a lookup within a date range?

batsonpm — Tue, 29 Sep 2020 20:39:43 GMT

With your additional suggestion of MVEXPAND, I learned about those functions and was able to finally make it work as I wanted. Thanks so much for the help!!

This query splits the multi-value fields of 'Server', 'serviceAcct', and 'ark_time' into separate fields that can then be compared/sorted.
'Server' is pulled from a field (servcon) that looks like this: " (Action: Connect)(Connection to address: servername1234)"
The multi-value fields are combined into combinedFields
combinedFields is then mvexpanded to created additional records

The data goes from this:
User, Server, serviceAcct, ark_time, Orig_time
Some.Guy@nowhere.com, server1234 server2234 server3234 server4234 server5234, PROD_4, PROD_4, PROD_5, PROD_4, PROD_3, 07/11/2018 14:29:00 07/24/2018 09:44:49 07/24/2018 11:13:51 07/10/2018 15:45:41 07/10/2018 15:45:41, 07/24/2018 09:26:00

To this:
User, Server, serviceAcct, ark_time, Orig_time
Some.Guy@nowhere.com, server1234, PROD_4, 07/11/2018 14:29:00, 07/24/2018 09:26:00
Some.Guy@nowhere.com, server2234, PROD_4, 07/24/2018 09:44:49 , 07/24/2018 09:26:00
Some.Guy@nowhere.com, server3234, PROD_5, 07/24/2018 11:13:51 , 07/24/2018 09:26:00
etc

Here is the completed query for future reference:

| rename OVW_Value1 as serverRequested, OVW_Value2 as userName, OVW_Value3 as requestDate, OVW_Value4 as requestHour, OVW_Value5 as Orig_time, USR_EMAIL1 as email
| lookup ark_admin User as email OUTPUTNEW "Server Connection" AS servcon "Service Account" AS serviceAcct Time AS ark_time
| rex field=servcon "address: (?<Server>[^)]+)"
| eval EndTime = strftime(relative_time(strptime(Orig_time, "%m/%d/%Y %H:%M:%S"),"+72h"),"%m/%d/%Y %H:%M:%S")
| eval combinedFields= mvzip (mvzip (Server,serviceAcct),ark_time)
| mvexpand combinedFields
| rex field=fields1 "(?<serverAccessed>\w+),(?<serviceAcctUsed>\w+),(?<serverTimeAccessed>\d+\D\d+\D\d+\D\S\d+\D\d+\D\d+)"
| where strptime(serverTimeAccessed, "%m/%d/%Y %H:%M:%S") > strptime(Orig_time, "%m/%d/%Y %H:%M:%S") and strptime(serverTimeAccessed, "%m/%d/%Y %H:%M:%S") <= relative_time(strptime(Orig_time, "%m/%d/%Y %H:%M:%S"),"+72h")
| table email userName serverRequested serverTimeAccessed serviceAcctUsed serverAccessed Orig_time EndTime
| sort +serverTimeAccessed