Need help in mapping fields by using list()

[pramit46]

I have a several multivalued fields fetched from different sourcetypes and I'm using list() to print the values. Problem is, it lists out all the values of that field irrespective of the other values in that row. Due to this I cannot map the values within a row.

For example: I am printing: PersonName, Age, MaritalStatus, Location.

All are multivalued fields which can have null (or no) values, as well.

Now list() gives me, ('-' denotes no value)

PersonName | Age | MaritalStatus | Location

---

A|10|M|New York

B|20|U|London

C|30|M|Mumbai

D|-|-|Sydney
E|-|-|Sydney

Whereas, the actual data is:

PersonName | Age | MaritalStatus | Location

---

A|10|M|New York

B|20|-|London

C|-|U|Mumbai

D|30|-|Sydney

E|-|M|Sydney

How can I print the data properly?

[pramit46]

This query should check the value 'A' against the PersonName field in S1 & S2 and print data.

sourcetype=S1
OR
sourcetype=S2
|where (S1_PersonName = "A"
OR
S2_PersonName = "A")
|stats
list(sourcetype) AS sourcetypes,
list(S1_PersonName) AS PersonName,
list(S1_Age) AS Age,
list(S2_MaritalStatus) AS MaritalStatus,
list(S2_Location) AS Location,

|eval combined_fieldvalues= toString(Age)+":"+toString(MaritalStatus) // I also wanted to concatenate 'Age' & 'MaritalStatus' for each row, but it appends all the values of 'MaritalStatus' after all the values of 'Age' and gives a big single field.

[MuS]

Hi pramit46,

like @somesoni2 said the current search would help.
Nevertheless, based on your provided examples

PersonName | Age | MaritalStatus | Location  
A|10|M|New York 
B|20|-|London 
C|-|U|Mumbai 
D|30|-|Sydney 
E|-|M|Sydney

I would run a search like this:

your base search to get the events you need | stats list(Age) AS Age list(MaritalStatus) AS MaritalStatus list(Location) AS Location by PersonName

You can have a look at this run everywhere search command and try & error with the grouping either by series or by group

index=_internal earliest=-15m source=*metrics.log kbps | stats list(kbps) AS kbps list(group) AS group list(max_age) AS max_age by series

hope this helps ...

cheers, MuS

[MuS]

keep trying, best way to build the search query you need: run your base search without any stats or charting command until you have all events available you need to build your stats, then start using stats on the events and any other needed commands.
You can concatenated values like this:

eval Concat=Age.",".MaritalStatus

[pramit46]

PersonName | Age | MaritalStatus | Location | Concat
A|10|M|New York|10,M
B|20|-|London|20,NULL
C|-|U|Mumbai|NULL,U
D|30|-|Sydney|30,NULL

[pramit46]

@MuS, I tried that after you suggested, but I guess, the way the above query works is to find a field in the logs, list down all the values in a column, and then move to the next field. May be that is the reason the 'fillnull' option does not make any difference.
I need to find the Age, MaritalStatus and Location where Name='A' ('B' or whatever). Not sure if I'm using a right query to fetch the data. 😞

Also, Can I have a column which has a concatenated values per row? like below:

[MuS]

did you try fillnull on those null fields?

[pramit46]

Thanks a lot for helping MuS. But I want to print atleast some identifier even if I do not find any values in that field for that row. (say, NULL
If I can do that, then I can simply read a row and put the delimited data into an object which is my ultimate goal.
Can you please help me achieving that?

[pramit46]

@somesoni2,
That example was a hypothetical situation. Unfortunately, I cannot provide the actual data in here. But I can give you a pseudo query which looks exactly like my query which I tried. Even if I could print "NULL" or a simple " " for a null value, that would also help. The only thing I want is to have each row in a column holding related values instead of merging them together.
How can I print "NULL" or " " for a null value in a multivalued field while using list()?

[somesoni2]

Can you post your current search query?