Creating a deduped-union of 1-to-N mapped pairs

[SonnyB]

Creating a deduped-union of 1-to-N mapped pairs

We need to create a deduped-union of pairs in the data, to create the biggest-possible set for the 1-to-N type of mapping in the data. Looking for syntax/solution/suggestions.

Here is the data-structure example, which will make it clear:
a name-field, which is co-related with number of pairs of other two fields (of the server) like this:

Input:

Name-field Server-field Server-attribute

A........................s1................a1
..........................s2................a2
..........................s3................a3

B........................s2................a2
..........................s4................a4
..........................s5................a5.
A........................s2................a2
..........................s6................a6

..........................s7................a7

Result should be:

Name-field Server-field Server-attribute

A........................s1................a1
..........................s2................a2
..........................s3................a3
..........................s6................a6
..........................s7................a7

B........................s2................a2
..........................s4................a4

..........................s5................a5

As you observe above: For Name-field=”A”, in the result, it has returned the proper-union set of the 5 unique pairs on the RHS (without duplicating a pair). The second Name-field=”B”, was the only occurrence found in the input, so the result is the same set of pairs.

Any Splunk commands’ syntax/construction help for the above task is appreciated.

[mrobichaud_splu]

This solved the blank values issues in the first column...

search ... 
| filldown 'Name-field'

You can then dedup and sort the table...

| dedup 'Name-field' 'Server-field' 'Server-attribute'
| sort 0 'Name-field' 'Server-field'

If you really need to null out the Name-field...

| streamstats current=f window=1 global=f  last('Name-field') as previous
| eval 'Name-field'=if('Name-field'==previous, "", 'Name-field')
| fields - previous

You may need to reverse the table afterward ...

| reverse 

[mrobichaud_splu]

Are you also saying that the Name-field is blank in the some rows ( the second for example)? Do you need to use the "filldown" command, before running dedup on all three fields?

[woodcock]

Like this:

... | stats values(Server-field) AS Server-fields values(Server-attribute) AS Server-attributes by Name-field

[woodcock]

Given that you need to export to csv, use this variation:

 ... | eval Server-field-attribute = Server-field . "," . Server-attribute | stats values(Server-field-attribute) AS Server-field-attribute BY Name-field | mvexpand limit=0 Server-field-attribute | rex field=Server-field-attribute "(?<Server-field>[^,]*),(?<Server-attribute>[^,]*)" | fields Name-field Server-field Server-attribute

That will definitely work but I think this will too and is better:

... | dedup Name-field Server-field Server-attribute | sort 0 Name-field Server-field

[SonnyB]

woodcock: Sir, the issue with that is the: When there is a 1-to-N mapping also between the Server-field and Server-attribute (i.e. one Server-field is mapped to many Server-attributes), then you see a null (gap) in the resulting table below the one Server-field... and all its related set of Server-attribute values, then end-up showing on the new rows (as the only value per row). We need the Server-field value replicated on every row for such unique pairs [and simularly we would like to repeat the Name-field also on the LHS for every row]. IOW: The resulting .csv /table matrix should have value shown in every cell of its every row]Thanks for your quick try and post.

[woodcock]

Actually, what you probably desire is this (but it is one or the other):

... | eval Server-field-attribute = Server-field . "," . Server-attribute | stats values(Server-field-attribute) AS Server-field-attribute BY Name-field