topic Re: Correlation based on 3 fields and find events in between, one of the fields in multivalue in Splunk Search

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

adonio — Mon, 23 Apr 2018 17:24:32 GMT

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

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

somesoni2 — Mon, 23 Apr 2018 18:58:19 GMT

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

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

adonio — Mon, 23 Apr 2018 19:10:22 GMT

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