topic How to Split multi valued row in to different rows in Splunk Search

How to Split multi valued row in to different rows

vinaykataaig — Wed, 04 Sep 2019 19:34:25 GMT

I have a below query which shows the recent windows patches installed in the servers, So most of the servers got installed with multiple patches. When i sort the result by Server name it is giving the result in single row joining all the multiple patch names and dates installed. Could some one tell me how to break the values and show them in different lines as 2nd image. Below is my query and results. Thyank you!!

Query:

index=AAA sourcetype="AAA:change"  | search ci_patching_group IN ("CHS*" "GISC*" "JPN*") | search name IN (*)
| eval filterdate = strptime(start_date,"%Y-%m-%d")
| eval Currentdate = relative_time(now(),"-0d@d")
| where filterdate < Currentdate
| eval servers=Upper(name) | search servers IN (*)
| eval start=strptime(start_date, "%Y-%m-%d %H:%M:%S.%N") 
| eval day = strftime(start, "%a") 
| eval month = strftime(start, "%B") | search month="*" | table servers number ci_patching_group
| join servers [search index="BBB" sourcetype="windowsupdatelog" | search "*Patch Deployment*" OR "*AGENT_INSTALLING_SUCCEEDED*" OR "*successfully installed*"| search host IN (*)
| rex field=_raw "^(?:[^:\n]*:){9}\s+(?P.+)" 
| eval servers = Upper(mvindex(split(host,"."),-0
| eval start=strftime(_time, "%Y-%m-%d %H:%M:%S:%N %A")
| eval day = strftime(start, "%a") 
| eval month = Upper(date_month) 
| stats values(ApplicablePatch) as ApplicablePatch values(start) as start by servers month ] | table ci_patching_group ApplicablePatch start servers month
| chart values(ci_patching_group) as ci_patching_group values(ApplicablePatch) as InstalledPatch values(start) as start by servers month  | mvexpand ApplicablePatch | fillnull value="Not Patched"

This is what I see when I run this query:

And this is how I wanted to be:

Re: How to Split multi valued row in to different rows

masonmorales — Wed, 04 Sep 2019 20:41:48 GMT

@vinaykataaig The first screenshot is so small it's not really readable. Can you post a larger version? Also, some sample events from each index/sourcetype would be helpful.

Re: How to Split multi valued row in to different rows

mayurr98 — Wed, 04 Sep 2019 20:57:32 GMT

try something like this :

index=AAA sourcetype="AAA:change" 
| search ci_patching_group IN ("CHS*" "GISC*" "JPN*") 
| search name IN (*) 
| eval filterdate = strptime(start_date,"%Y-%m-%d") 
| eval Currentdate = relative_time(now(),"-0d@d") 
| where filterdate < Currentdate 
| eval servers=Upper(name) 
| search servers IN (*) 
| eval start=strptime(start_date, "%Y-%m-%d %H:%M:%S.%N") 
| eval day = strftime(start, "%a") 
| eval month = strftime(start, "%B") 
| search month="*" 
| table servers number ci_patching_group 
| join servers 
    [ search index="BBB" sourcetype="windowsupdatelog" 
    | search "*Patch Deployment*" OR "*AGENT_INSTALLING_SUCCEEDED*" OR "*successfully installed*" 
    | search host IN (*) 
    | rex field=_raw "^(?:[^:\n]*:){9}\s+(?P.+)" 
    | eval servers = Upper(mvindex(split(host,"."),-0 
    | eval start=strftime(_time, "%Y-%m-%d %H:%M:%S:%N %A") 
    | eval day = strftime(start, "%a") 
    | eval month = Upper(date_month) 
    | stats values(ApplicablePatch) as ApplicablePatch values(start) as start by servers month ] 
| table ci_patching_group ApplicablePatch start servers month 
| rex field=ApplicablePatch mode=sed "s/(\d{4}\-\d{2})/|\1/g" 
| makemv ApplicablePatch delim="|" 
| mvexpand ApplicablePatch 
| chart values(ci_patching_group) as ci_patching_group values(ApplicablePatch) as InstalledPatch values(start) as start by servers month 
| fillnull value="Not Patched"