topic Re: splunk query to combine 2 tables with multiple values in Splunk Enterprise Security

splunk query to combine 2 tables with multiple values

sdkp03 — Thu, 24 Jun 2021 02:13:05 GMT

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!

Re: splunk query to combine 2 tables with multiple values

kamlesh_vaghela — Thu, 24 Jun 2021 04:14:09 GMT

@sdkp03

Can you please try this?

My Sample Search :

Thanks
KV
▄︻̷̿┻̿═━一

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

Re: splunk query to combine 2 tables with multiple values

sdkp03 — Tue, 03 Aug 2021 00:04:13 GMT

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.

Re: splunk query to combine 2 tables with multiple values

ITWhisperer — Tue, 03 Aug 2021 08:45:14 GMT

| 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")

Re: splunk query to combine 2 tables with multiple values

sdkp03 — Tue, 03 Aug 2021 11:28:15 GMT

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.

Re: splunk query to combine 2 tables with multiple values

ITWhisperer — Tue, 03 Aug 2021 11:33:47 GMT

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

Re: splunk query to combine 2 tables with multiple values

sdkp03 — Wed, 04 Aug 2021 06:49:52 GMT

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.

Re: splunk query to combine 2 tables with multiple values

ITWhisperer — Wed, 04 Aug 2021 07:16:39 GMT

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

Re: splunk query to combine 2 tables with multiple values

sdkp03 — Wed, 04 Aug 2021 07:22:15 GMT

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

Re: splunk query to combine 2 tables with multiple values

ITWhisperer — Wed, 04 Aug 2021 07:31:03 GMT

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

Re: splunk query to combine 2 tables with multiple values

sdkp03 — Wed, 04 Aug 2021 07:33:13 GMT

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!

Re: splunk query to combine 2 tables with multiple values

ITWhisperer — Wed, 04 Aug 2021 07:37:31 GMT

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

Re: splunk query to combine 2 tables with multiple values

sdkp03 — Wed, 04 Aug 2021 23:51:46 GMT

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

Re: splunk query to combine 2 tables with multiple values

ITWhisperer — Thu, 05 Aug 2021 07:10:19 GMT

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,""),",+",","),",")

Re: splunk query to combine 2 tables with multiple values

sdkp03 — Fri, 06 Aug 2021 08:39:36 GMT

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