topic Re: Mutlivalue Field Problem- Is there any way to do this without mvexpand? in Splunk Enterprise

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

coldwolf7 — Tue, 09 Aug 2022 20:14:43 GMT

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

Re: Mutlivalue Field Problem

ITWhisperer — Tue, 09 Aug 2022 20:12:50 GMT

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.

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

coldwolf7 — Tue, 09 Aug 2022 21:41:31 GMT

That worked great,

Thanks