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

[coldwolf7]

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

[coldwolf7]

That worked great,

Thanks

[ITWhisperer · Accepted solution]

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.