splunk query to combine 2 tables with multiple values

[sdkp03]

I have a static lookup file which has 2 columns. Example: name, type. Please note this static lookup has no reference to date timestamp. Now am trying to combine to frame this as a table which will append date as another column. I would like to use the resulting table to compare against another result set to confirm that there was an event logged for each type on dailybasis and report only the missing ones.

Example :

Name | Type | Date

AAA | BBB | 22/06/2021

AAA | BBB | 23/06/2021

AAA | BBB | 24/06/2021

CCC | DDD | 22/06/2021

CCC | DDD | 23/06/2021

CCC | DDD | 24/06/2021

EEE | FFF | 22/06/2021

EEE | FFF | 23/06/2021

EEE | FFF | 24/06/2021

GGG | HHH | 22/06/2021

GGG | HHH | 23/06/2021

GGG | HHH | 24/06/2021

Query I have been trying to use is [inputlookup test.csv | fields Name, Type] appendcols [[| gentimes start=-3 | eval Dates=strftime(starttime,"%Y%m%d") | table Dates]. I have been trying to use multiple commands but with no luck 😞 It appends just the first row. since my date table has 3 rows, it will append dates in first 3 rows.  Thanks for any help in achieving this!

[kamlesh_vaghela]

@sdkp03 

Can you please try this?

| inputlookup test.csv | fields Name Type
| join [| gentimes start=-3 | eval Dates=strftime(starttime,"%Y-%m-%d") | table Dates | stats delim="," values(Dates) As Dates]
| eval Dates=split(Dates,",")
|mvexpand Dates

My Sample Search :

| makeresults | eval _raw="Name | Type
AAA | BBB
CCC | DDD
EEE | FFF
GGG | HHH
"| multikv forceheader=1
| table Name Type
| join [| gentimes start=-3 | eval Dates=strftime(starttime,"%Y-%m-%d") | table Dates | stats delim="," values(Dates) As Dates]
| eval Dates=split(Dates,",")
|mvexpand Dates

 Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[sdkp03]

Thanks for that I now have a table with below  columns:

FN | FT | date | Dates

AAA | BBB | 03-08-2021 | 01-08-2021, 02-08-2021,03-08-2021

AAA | CCC | 02-08-2021 | 01-08-2021, 02-08-2021,03-08-2021 

XXX | BBB | 02-08-2021 |  01-08-2021, 02-08-2021,03-08-2021

XXX | CCC | 03-08-2021 |  01-08-2021, 02-08-2021,03-08-2021

I am trying to compare date and Dates column and want to print missing dates as output. I tried using join and eventstats count command but somehow am not able to achieve without loosing FT/FN type. Can you please assist with this.

[ITWhisperer]

| makeresults | eval _raw="name type date Dates
AAA BBB 03-08-2021 01-08-2021,02-08-2021,03-08-2021
AAA CCC 02-08-2021 01-08-2021,02-08-2021,03-08-2021 
XXX BBB 02-08-2021 01-08-2021,02-08-2021,03-08-2021
XXX CCC 04-08-2021 01-08-2021,02-08-2021,03-08-2021"
| multikv fields name type date Dates forceheader=1
| table name type date Dates


| eval found=if(match(Dates,date),"found","not found")

[sdkp03]

Apologies didn't mention the expected output result format:

FN   |  FT  |      date          |                                    Dates                             |     Missing dates
AAA BBB 03-08-2021 01-08-2021,02-08-2021,03-08-2021 01-08-2021,02-08-2021
AAA CCC 02-08-2021 01-08-2021,02-08-2021,03-08-2021 01-08-2021,03-08-2021
XXX BBB 02-08-2021 01-08-2021,02-08-2021,03-08-2021 01-08-2021,03-08-2021
XXX CCC 03-08-2021 01-08-2021,02-08-2021,03-08-2021 01-08-2021,02-08-2021

I am looking in for query to retrieve last column.

[ITWhisperer]

| eval missing_dates=trim(replace(replace(Dates,date,""),",,",","),",")

[sdkp03]

Its weird it works with one query and doesn't work for different query. Now when I try to use same logic, its purely replacing Dates with date!!!! Can you please explain the logic.

[sdkp03]

Figured out the reason for inconsistency. Please see below result, when date doesn't follow the sequence replace doesn't work as expected. First row works as expected since date has a pattern, but second row onwards it fails because date has broken sequence as reports were missing on specific dates:

BBBB AAAA 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03
BBBB AAAA 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03
BBBB AAAA 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03
BBBB AAAA 2021-07-29,2021-07-30,2021-08-01,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03

[ITWhisperer]

I am not sure what you are showing here or what you mean by broken pattern

[ITWhisperer]

The inner replace() replaces the date in the Dates string with an empty string

replace("01-08-2021,02-08-2021,03-08-2021","02-08-2021","") gives "01-08-2021,,03-08-2021".

The next replace() replaces the double comma (,,) with a single comma (,)

replace("01-08-2021,,03-08-2021",",,",",") gives "01-08-2021,03-08-2021"

If the date was 03-08-2021

replace ("01-08-2021,02-08-2021,03-08-2021","03-08-2021","") gives "01-08-2021,02-08-2021," so the trim() removes trailing (and leading) commas

[sdkp03]

But, replace("01-08-2021,02-08-2021,03-08-2021","01-08-2021,03-08-2021","") prints 01-08-2021,02-08-2021,03-08-2021. This is causing the issue in my case. What would you think is the best way to handle this!

[ITWhisperer]

So you are now trying this with multiple dates not the single date you had in the original question?

[sdkp03]

Yes that's right. When I did expand the date range, this is the issue I have landed with.

[ITWhisperer · Accepted solution]

Your example data had spaces in some of the Dates instead of commas so I added a replace to correct that. I am not sure if this is necessary or just a result of copy/paste.

| makeresults | eval _raw="name type date Dates
BBBB AAAA 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03
BBBB AAAA 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03
BBBB AAAA 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03
BBBB AAAA 2021-07-29,2021-07-30,2021-08-01,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03 2021-07-28,2021-07-29,2021-07-30,2021-07-31,2021-08-01,2021-08-02,2021-08-03"
| multikv fields name type date Dates forceheader=1
| table name type date Dates



| eval Dates=replace(Dates," ",",")
| eval date=replace(date,",","|")
| eval missing_dates=trim(replace(replace(Dates,date,""),",+",","),",")

[sdkp03]

This worked like charm. Thanks so much for your help.