Splunk Enterprise Security

splunk query to combine 2 tables with multiple values

sdkp03
Path Finder

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!

Labels (1)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
Path Finder

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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")
0 Karma

sdkp03
Path Finder

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.

0 Karma

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

sdkp03
Path Finder

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.

0 Karma

sdkp03
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

sdkp03
Path Finder

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!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

sdkp03
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...