Splunk Search

Correlation based on 3 fields and find events in between, one of the fields in multivalue

adonio
Ultra Champion

Hello Splunkers,
battling with this all morning and seeking your assistance.
i have a CSV data set from a car workshop as below, the field "replaced_parts" is a product number and can take any number of values, here i changed to part name for convenience.

date,make,model,car_id,replaced_parts,
4/4/2014,VW,Jetta,123,battery,
4/5/2014,RR,Phantom,234,"head light" "rear light",
4/6/2014,Renault,Clio,345,engine,
4/4/2015,VW,Jetta,123,battery alternator,
4/5/2015,RR,Phantom,234,bumper",
4/6/2015,Renault,Clio,345,transmission,
4/6/2016,Renault,Clio,345,engine,

i am trying to find whether the same car_id had the same part replaced in consecutive visits to the shop as well as other parts that might had impact on that replaced part. for example car # 123 should appear and car # 234 should not.
also, i am trying to capture whether the same car was in another visit and replaced another part/s and place that value/s in a new field.
example desired result would be:

last_date|car_id |make  |model|commun_replaced_parts|maybe_related_part
4/4/2015|123   | VW       |Jetta   |battery                                |alternator,
4/6/2016|345   |Renault|Clio    |engine                                 |transmission

thanks in advance for your help

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

| gentimes start=-1 | eval temp="4/4/2014,VW,Jetta,123,battery,#4/5/2014,RR,Phantom,234,\"head light\" \"rear light\",#4/6/2014,Renault,Clio,345,engine,#4/4/2015,VW,Jetta,123,battery alternator,#4/5/2015,RR,Phantom,234,bumper,##4/6/2015,Renault,Clio,345,transmission,#4/6/2016,Renault,Clio,345,engine," | makemv temp delim="#" | mvexpand temp | table temp | rex field=temp "(?<date>[^,]+),(?<make>[^,]+),(?<model>[^,]+),(?<carid>[^,]+),(?<replaced_parts>[^,]+),"  
| rename COMMENT as "Above portion generates the sample data"
| eval date=strptime(date,"%m/%d/%Y") | eval replaced_parts=if(match(replaced_parts,"\""),replace(replace(replaced_parts,"\"\s+\"",","),"\"",""),replace(replaced_parts,"\s+",",")) | makemv replaced_parts delim="," | mvexpand replaced_parts 
| rename COMMENT as "Above portion expands the replaced_parts in case there are multiple parts "
| eventstats dc(date) as dates by make model carid replaced_parts | eval common_parts=if(dates>1,replaced_parts,null()) | eval alternate_parts=if(dates=1,replaced_parts,null()) | stats max(date) as last_date max(dates) as dates values(*_parts) as *_parts by carid model make | where dates=2 | table  last_date  carid make model common_parts alternate_parts | eval last_date=strftime(last_date,"%m/%d/%Y")

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

| gentimes start=-1 | eval temp="4/4/2014,VW,Jetta,123,battery,#4/5/2014,RR,Phantom,234,\"head light\" \"rear light\",#4/6/2014,Renault,Clio,345,engine,#4/4/2015,VW,Jetta,123,battery alternator,#4/5/2015,RR,Phantom,234,bumper,##4/6/2015,Renault,Clio,345,transmission,#4/6/2016,Renault,Clio,345,engine," | makemv temp delim="#" | mvexpand temp | table temp | rex field=temp "(?<date>[^,]+),(?<make>[^,]+),(?<model>[^,]+),(?<carid>[^,]+),(?<replaced_parts>[^,]+),"  
| rename COMMENT as "Above portion generates the sample data"
| eval date=strptime(date,"%m/%d/%Y") | eval replaced_parts=if(match(replaced_parts,"\""),replace(replace(replaced_parts,"\"\s+\"",","),"\"",""),replace(replaced_parts,"\s+",",")) | makemv replaced_parts delim="," | mvexpand replaced_parts 
| rename COMMENT as "Above portion expands the replaced_parts in case there are multiple parts "
| eventstats dc(date) as dates by make model carid replaced_parts | eval common_parts=if(dates>1,replaced_parts,null()) | eval alternate_parts=if(dates=1,replaced_parts,null()) | stats max(date) as last_date max(dates) as dates values(*_parts) as *_parts by carid model make | where dates=2 | table  last_date  carid make model common_parts alternate_parts | eval last_date=strftime(last_date,"%m/%d/%Y")

adonio
Ultra Champion

@somesoni2, thank you very much!
works nice like the RR

0 Karma
Get Updates on the Splunk Community!

Splunk Lantern | Getting Started with Edge Processor, Machine Learning Toolkit ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...