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
| 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 |
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"
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.
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
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]
I am still confused. Could you clarify the following:
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
```
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.
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?
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
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:
(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 |
| 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 |
Awesome. Thank you yuanliu. I appreciate it!
Thank you! I appreciate it.