Splunk Search

How to Split multi valued row in to different rows

vinaykataaig
Explorer

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:

alt text

And this is how I wanted to be:

alt text

0 Karma

mayurr98
Super Champion

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"
0 Karma

masonmorales
Influencer

@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.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...