How to merge two searches where initial output is fed to lookup table?

[Sven1]

I am working to merge two searches. The first search outputs one or more account names:

index=x sourcetype=y
| table account

The second search (below), for each account name, filters lookup csv table 'account lookup' on that account name and counts the number of dates in an adjacent column in the lookup table that are within the last seven days. 

| inputlookup append=T account_lookup where account=Account_A
| where time > relative_time(now(),"-7d")
| stats count as "Accounts Updated in Last 7 Days"]

My searches and attempts to apply related information have not yet revealed how I can pass the account names outputted in the first search into the lookup that is in the second search.  

Many thanks for any help. 

Sven

[yuanliu]

I think you meant to use lookup instead of inputlookup, like

index=x sourcetype=y
| table account
| lookup account_lookup account
| where where time > relative_time(now(),"-7d")
| stats dc(account) as "Accounts Updated in Last 7 Days"

[Sven1]

Thank you very much yuanliu.  Please let me clarify that for each account identified in the first part of the search, I want to:

(1) filter the 'account_lookup' table on that account

(2) filter the 'account_lookup' table on the]dates in an adjacent 'Date' column that are within seven days of the current date, and then

(3) count the number of matching rows and return that as "Records updated in the last 7 days"

Here, a "Record" is the same as a row.  (I should have used the word "Record" in my original post.) Main columns in the account_lookup' table are "Account" and "Date".  The account_lookup' table will typically have multiple rows for a given account. 

The first two lines below generate a table of one or more accounts, and for each account, the above steps are to be taken. The end result will be a table listing each account and the associated number of rows for that account that have been updated within the last 7 days. 

index=x sourcetype=y
| table account
| lookup account_lookup account
| where time > relative_time(now(),"-7d")
| stats count as"Records updated in the last 7 days" values(account) AS account

Variations of the above search have not produced the desired result. Thanks again for any help in nailing this down.

[yuanliu]

The above missed two points.  First, time field (which you now indicate is named Date) is more likely a string than real time. (This also mean that the second search in the description could not possibly work.)  Second, that Date could have multiple values.  Correcting for these, you can try (assuming that Date field has the format "YYYY-MM-DD" which "%F" will match):

index=x sourcetype=y
| table account ``` it is better to not use table at this stage; use fields if RAM and performance is a concern or nothing at all ```
| lookup account_lookup account output Date
| eval Date = mvmap(Date, strptime(Date, "%F")) ``` "%F" is the same as "%Y-%m-%d" ```, latest = max(Date)
| where latest > relative_time(now(),"-7d")
| stats count as"Records updated in the last 7 days" values(account) AS account

[Sven1]

Thank you.  I appreciate it.  I have tried variations of the search you sent and am still working to converge on the solution. 

For example, if the first line of the below search returns 'ACCT_A' - I can use the below 'append' statement to identify how many 'ACCT_A' records have been updated in the last 7 days. This SPL works. I can go into the 'account_lookup' table and verify that it works.

I really appreciate help in establishing what is needed to generalize the below search into a search that takes each value of 'account' returned by the first part of the search (whether it is one or several values) and performs the analysis represented by the append statement below. 

index=x sourcetype=y
| append [inputlookup account_lookup where account=ACCT_A
| where date > relative_time(now(),"-7d")
| stats count as "Records updated in the last 7 days" values(account) AS account]

[yuanliu]

I am still confused.  Could you clarify the following:

1. Is the time/date field in account_lookup named time, date, or Date?  You referred to it in three different forms.  I will take date this time as that is in your latest illustration.
2. Is this time/date field in the form of epoch seconds, or is it in the form of a calendar string?  If latter, the subsearch cannot possible do any filtering.  So, I will assume that the date field is in epoch.
3. The biggest confusion I had was perhaps which information is supposed to be "Records updated in the last 7 days".  Is it event count from search index=x, sourcetype=y or number of values returned from the lookup?  So far I have assumed that it is the former.  I can try to use the latter interpretation, like this:

index=x sourcetype=y
| lookup account_lookup account output date
| eval "Records updated in the last 7 days" = mvcount(mvmap(date, if(date > relative_time(now(), "-7d@d"), date, null())))
``` assumptions:
1. account_lookup contains a date field in epoch.
2. date comparison is within lookup output, unrelated to events
```

[Sven1]

Thank you.   

(1) The time/date field in account_lookup is named date. 

(2) The time/date field is in epoch seconds. 

(3) The first part of the search will return at least one and potentially several account names. For each account name, the remaining part of the search will one at a time, successively:

(a) filter the 'account_lookup' table on one account name

(b) for that one account, filter the 'account_lookup' table on the dates in an adjacent 'date' column that are within seven days of the current date, and then

(c) count the number of matching rows and return that as "Records updated in the last 7 days"

The output will have at least one row, and potentially several rows. An example output of the search is:

Account      Records updated in the last 7 days

ACCT_A       12

ACCT_G         7

ACCT_ M     15

ACCT_R         3

Again, thank you.

[yuanliu]

In that case, the search in my previous reply should add the desired "Records updated in the last 7 days" if the count is nonzero.  No stats needed.  Did you try that?

[Sven1]

I appreciate your help! Unfortunately, careful use of that SPL, copied below, does not work yet. 

index=x sourcetype=y
| lookup account_lookup account output date
| eval "Records updated in the last 7 days" = mvcount(mvmap(date, if(date > relative_time(now(), "-7d@d"), date, null())))

The "Records updated in the last 7 days" field is blank.  

The date field within 'account_lookup.csv' is a 10-digit number.  I noticed that If I run the following, the 'date' field formatting reverts to a 10-digit number.  However, the formatting of an additional date/time field "initial_date" remains intact. 

index=x sourcetype=y
| convert timeformat="%m/%d/%Y %H:%M:%S" ctime(date) 
| convert timeformat="%m/%d/%Y %H:%M:%S" ctime(initial_date)
| lookup account_lookup account output date
| eval "Records updated in the last 7 days" = mvcount(mvmap(date, if(date > relative_time(now(), "-7d@d"), date, null())))
| table account date initial_date

[Sven1]

Could someone with more experience with SPL than me please take a look and help me nail this down?

I am working to merge two searches. The first part of the search will return at least one and potentially several account names. For each account name, the remaining part of the search will one at a time, successively:

1. filter 'account_lookup.csv' on one account name
2. for that one account, filter 'account_lookup.csv' on the dates in an adjacent 'date' column that are within seven days of the current date, and then
3. count the number of matching rows and return that as "Records updated in the last 7 days"

(my search)
| inputlookup account_lookup.csv
| search account=ACCT_A
| eval Date = mvmap(date, strptime(date, "%Y-%m-%d")), latest = max(date)
| where latest > relative_time(now(),"-7d@d")
| stats values(account) AS account count as "Records updated in the last 7 days"

The output will have at least one row, and potentially several rows. An example final output is:

Account      Records updated in the last 7 days
ACCT_A        11
ACCT_B         8
ACCT_C         3
ACCT_D         9

Or, based on the results of the first part of the search, the output may be:

account     Records updated in the last 7 days
ACCT_A      11

I have pasted a sample ‘account_lookup.csv’ below   The results of the second part of the above search against ‘account_lookup.csv’ are what is listed above.

However, I am trying to dynamically feed the account name(s) generated from the first part of the search into the proper SPL that will generate the results described above.  Specifically: if there are 1, 2 or ‘x’ number of account names generated as output from the first part of the search, that is how many distinct rows are listed in the final output, with each row listing the number of “Records updated in the last 7 days’ for that account.

Many thanks to yuanliu for looking at this earlier.  The SPL suggested earlier, as well as different variations, would collapse the results into one row and typically list '0' records updated - no matter if the first part of the search generated one account or multiple accounts.

Now, I have had a chance again to work to get this nailed down and appreciate any guidance. 

account	date	date2
ACCT_D	1679061065	3/17/2023 13:51
ACCT_C	1679038288	3/17/2023 7:31
ACCT_A	1679015511	3/17/2023 1:11
ACCT_B	1678992734	3/16/2023 18:52
ACCT_D	1679607713	3/23/2023 21:41
ACCT_A	1679584936	3/23/2023 15:22
ACCT_B	1679562159	3/23/2023 9:02
ACCT_D	1679539382	3/23/2023 2:43
ACCT_A	1679379943	3/21/2023 6:25
ACCT_B	1679357166	3/21/2023 0:06
ACCT_D	1679334389	3/20/2023 17:46
ACCT_A	1679311612	3/20/2023 11:26
ACCT_B	1679288835	3/20/2023 5:07
ACCT_D	1679516605	3/22/2023 20:23
ACCT_C	1679493828	3/22/2023 14:03
ACCT_A	1679698821	3/24/2023 23:00
ACCT_A	1679676044	3/24/2023 16:40
ACCT_A	1679653267	3/24/2023 10:21
ACCT_B	1679630490	3/24/2023 4:01
ACCT_D	1679266058	3/19/2023 22:47
ACCT_A	1679243281	3/19/2023 16:28

[yuanliu · Accepted solution]

| lookup account_lookup account
| eval "Records updated in the last 7 days" = mvcount(mvmap(date, if(date > relative_time(now(), "-7d@d"), date, null())))

I don't understand why the above is not working for you.  It certainly works for me.  Let me show my emulation:

| makeresults
| eval account = mvappend("ACCT_A", "ACCT_B", "ACCT_C", "ACCT_D")
| mvexpand account
``` data emulation above ```
| lookup account_lookup account
| eval "Records updated in the last 7 days" = mvcount(mvmap(date, if(date > relative_time(now(), "-7d@d"), date, null())))
``` assumptions:
1. account_lookup contains a date field in epoch.
2. date comparison is within lookup output, unrelated to events
```

With your latest lookup sample, the output is

Records updated in the last 7 days	_time	account	date	date2
7	2023-03-24 19:41:06	ACCT_A	1679015511 1679584936 1679379943 1679311612 1679698821 1679676044 1679653267 1679243281	3/17/2023 1:11 3/23/2023 15:22 3/21/2023 6:25 3/20/2023 11:26 3/24/2023 23:00 3/24/2023 16:40 3/24/2023 10:21 3/19/2023 16:28
4	2023-03-24 19:41:06	ACCT_B	1678992734 1679562159 1679357166 1679288835 1679630490	3/16/2023 18:52 3/23/2023 9:02 3/21/2023 0:06 3/20/2023 5:07 3/24/2023 4:01
2	2023-03-24 19:41:06	ACCT_C	1679038288 1679493828	3/17/2023 7:31 3/22/2023 14:03
6	2023-03-24 19:41:06	ACCT_D	1679061065 1679607713 1679539382 1679334389 1679516605 1679266058	3/17/2023 13:51 3/23/2023 21:41 3/23/2023 2:43 3/20/2023 17:46 3/22/2023 20:23 3/19/2023 22:47

[Sven1]

Awesome.  Thank you yuanliu.    I appreciate it!

[Sven1]

Thank you! I appreciate it.