Splunk Enterprise

Mutlivalue Field Problem- Is there any way to do this without mvexpand?

coldwolf7
Explorer

Hello, 

I need some help. I have a index where I pull all of the HR info for our employees then I have a CSV I bring in using the LOOKUP command, the CSV has al the of the machine info for each user.  The CSV file has no Multi value fields. So if a user has multiple computers there would be a separate line in the CSV for that. what is happening when I run this code the splunk is creating Multi value field for all the machine info when a person has multiple computers.  I have tried a used MVexpand. But I have to do it on 14 different fields and then I get a Memory error. Which I can not increase the memory we have certain restrictions on that. Even if I do one MVexpand same memory error. the report will produce over 140000 entries. SO below is my basic code. that code will produce the multivalue fields which I do not want. I need the report when i user has multi machines it creates a completely separate line for that. this code does not include the MVexpand. Is there any way to do that without using mvexpand

 

(index=wss_desktop_os sourcetype="associate"LOGON_ID="*")  LOCATION IN ("CA1*", "CA2*", "CA3*", "CA4*", "CA5*", "CA6*")

| stats values(SPVR_FLL_NM) AS Supervisor, values(EMP_STA_TX) AS "Employee Status" values(SPVR_EMAIL_AD) AS "Supervisor Email", values(L2_LN1_AD) AS Address, values(L2_CTY_NM) AS City, values(SITE_COUNTRY) AS Country, values(DEPARTMENT) AS Department, values(DIV_LG_NM) AS Division, values(L2_FLR_NO) AS Floor, values(FLL_NM) AS FullName, values(LOCATION) AS Location, values(L2_CNY_CD) AS Region,  values(L2_CNY_NM) AS SiteCountry, values(LOB) AS ORG, values(L2_STPV_NM) AS State, values(WRK_EMAIL_AD) AS Email by LOGON_ID

| lookup local=true PrimaryUser.csv PrimaryUser AS LOGON_ID OUTPUT host AS host BuildNumber Cores DeviceType InstallDate LastBootUpTime LastReported Locale Manufacturer Model OSVer PCSystemType SerialNumber TotalPhysicalMemoryKB TotalVirtualMemoryKB

| where host NOT null

 

 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

You could try something like this

``` Tag the events with a unique id ```
| streamstats count as row
``` Determine how many host lines each event has ```
| eval _offset=mvcount(host)
``` Running total of events to be created ```
| streamstats sum(_offset) as _offset
``` Move running total to next event ```
| streamstats window=1 current=f min(_offset) as _previous
``` Set the first event total to zero ```
| fillnull value=0 _previous
``` Append an additional event ```
| appendpipe
    [stats max(_offset) as _offset max(_previous) as _previous
    ``` But only if there is more than one host in the last event ```
    | where _previous < _offset - 1
    | eval _previous=_offset-1]
``` Create empty events - this is where you may still run into limits ```
| makecontinuous _previous span=1s
``` Copy data down to additional events ```
| filldown
``` Create an index for each new event ```
| streamstats count as _index by row
``` Reset each field to the corresponding multi-value ```
| foreach host BuildNumber Cores DeviceType InstallDate LastBootUpTime LastReported Locale Manufacturer Model OSVer PCSystemType SerialNumber TotalPhysicalMemoryKB TotalVirtualMemoryKB
    [| eval <<FIELD>>=mvindex(<<FIELD>>,_index-1)]
``` Remove work fields ```
| fields - row _index _previous _offset

As it says in the comments, you may still run into memory limits.

View solution in original post

coldwolf7
Explorer

That worked great,

Thanks

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You could try something like this

``` Tag the events with a unique id ```
| streamstats count as row
``` Determine how many host lines each event has ```
| eval _offset=mvcount(host)
``` Running total of events to be created ```
| streamstats sum(_offset) as _offset
``` Move running total to next event ```
| streamstats window=1 current=f min(_offset) as _previous
``` Set the first event total to zero ```
| fillnull value=0 _previous
``` Append an additional event ```
| appendpipe
    [stats max(_offset) as _offset max(_previous) as _previous
    ``` But only if there is more than one host in the last event ```
    | where _previous < _offset - 1
    | eval _previous=_offset-1]
``` Create empty events - this is where you may still run into limits ```
| makecontinuous _previous span=1s
``` Copy data down to additional events ```
| filldown
``` Create an index for each new event ```
| streamstats count as _index by row
``` Reset each field to the corresponding multi-value ```
| foreach host BuildNumber Cores DeviceType InstallDate LastBootUpTime LastReported Locale Manufacturer Model OSVer PCSystemType SerialNumber TotalPhysicalMemoryKB TotalVirtualMemoryKB
    [| eval <<FIELD>>=mvindex(<<FIELD>>,_index-1)]
``` Remove work fields ```
| fields - row _index _previous _offset

As it says in the comments, you may still run into memory limits.

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...