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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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