topic Re: How to sort multi-value fields in Splunk Search

topic Re: How to sort multi-value fields in Splunk Search https://community.splunk.com/t5/Splunk-Search/How-to-sort-this-multi-value-fields-based-on-the-latest/m-p/587530#M204615 <P>ITWhisperer expresses exactly how I see the problem.  It is so much cleaner (and easier) to fix the multivalue order in the search that produces values as opposed to tweak display after mixed up values.</P><P>This said, you can use a combination of <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#mvmap.28X.2CY.29" target="_blank" rel="noopener">mvmap</A>, <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#mvzip.28X.2CY.2C.22Z.22.29" target="_blank" rel="noopener">mvzip</A>, <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#mvsort.28X.29" target="_self">mvsort</A>, and <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#split.28X.2C.22Y.22.29" target="_blank" rel="noopener">split</A> to get what you wanted.  I'll present two versions, one specific, one generic.</P><P>If you are certain that database_* is already correct, this slightly simpler filter helps to illustrate how the thought process goes:</P><P> </P><LI-CODE lang="markup">| makeresults | eval hostname = "server101" | eval id = "123|124" | eval database_timestamp = "Mar 03, 2022 12:59:46 PM|Feb 23, 2022 1:19:24 PM" | eval database_status = "Online|Offline (30 days ago)" | eval server_timestamp = "Feb 22, 2022 1:19:24 PM|Mar 01, 2022 12:59:46 PM" | eval server_status = "Offline (31 days ago)|Online" | fields hostname id database_timestamp database_status server_timestamp server_status | makemv delim="|" database_timestamp | makemv delim="|" database_status | makemv delim="|" server_timestamp | makemv delim="|" server_status | makemv delim="|" id ``` below is multivalue manipulation ``` | eval server_lag=mvmap(server_timestamp, now()-strptime(server_timestamp, "%b %d, %Y %I:%M:%S %p")) ``` establish reverse time sequence ``` | eval server_status=mvzip(server_lag, mvzip(server_timestamp, server_status, "|"), "|") ``` zip affected fields with server_lag ``` | eval server_status=mvsort(server_status) ``` sort by reverse time using server_lag ``` | eval server_timestamp=mvmap(server_status, mvindex(split(server_status, "|"), 1)) ``` extract timestamp field from zip ``` | eval server_status=mvmap(server_status, mvindex(split(server_status, "|"), 2)) ``` extract status field from zip ``` | fields - server_lag</LI-CODE><P> </P><P>A more general approach is to use <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Foreach" target="_blank" rel="noopener">foreach</A> to reorder all fields that may fall into disorder.</P><P> </P><LI-CODE lang="markup">| makeresults | eval hostname = "server101" | eval id = "123|124" | eval database_timestamp = "Mar 03, 2022 12:59:46 PM|Feb 23, 2022 1:19:24 PM" | eval database_status = "Online|Offline (30 days ago)" | eval server_timestamp = "Feb 22, 2022 1:19:24 PM|Mar 01, 2022 12:59:46 PM" | eval server_status = "Offline (31 days ago)|Online" | fields hostname id database_timestamp database_status server_timestamp server_status | makemv delim="|" database_timestamp | makemv delim="|" database_status | makemv delim="|" server_timestamp | makemv delim="|" server_status | makemv delim="|" id ``` below is multivalue manipulation ``` | foreach *_status ``` iterate through all fields that may fall into disorder ``` [eval <<MATCHSTR>>_lag=mvmap(<<MATCHSTR>>_timestamp, now()-strptime(<<MATCHSTR>>_timestamp, "%b %d, %Y %I:%M:%S %p")) ``` establish reverse time sequence ``` | eval <<MATCHSTR>>_status=mvzip(<<MATCHSTR>>_lag, mvzip(<<MATCHSTR>>_timestamp, <<MATCHSTR>>_status, "|"), "|") ``` zip affected fields with server_lag ``` | eval <<MATCHSTR>>_status=mvsort(<<MATCHSTR>>_status) ``` sort by reverse time using server_lag ``` | eval <<MATCHSTR>>_timestamp=mvmap(<<MATCHSTR>>_status, mvindex(split(<<MATCHSTR>>_status, "|"), 1)) ``` retrieve timestamp field from zip ``` | eval <<MATCHSTR>>_status=mvmap(<<MATCHSTR>>_status, mvindex(split(<<MATCHSTR>>_status, "|"), 2)) ``` retrieve status field from zip ``` ] | fields - *_lag</LI-CODE><P> </P><P> </P><P> </P><P>With sample data, the output is</P><TABLE><TBODY><TR><TD>hostname</TD><TD><DIV class="">id</DIV></TD><TD><DIV class="">database_timestamp</DIV></TD><TD><DIV class="">database_status</DIV></TD><TD><DIV class="">server_timestamp</DIV></TD><TD><DIV class="">server_status</DIV></TD><TD>_time</TD></TR><TR><TD>server101</TD><TD><DIV class="">123</DIV><DIV class="">124</DIV></TD><TD><DIV class="">Mar 03, 2022 12:59:46 PM</DIV><DIV class="">Feb 23, 2022 1:19:24 PM</DIV></TD><TD><DIV class="">Online</DIV><DIV class="">Offline (30 days ago)</DIV></TD><TD><DIV class="">Mar 01, 2022 12:59:46 PM</DIV><DIV class="">Feb 22, 2022 1:19:24 PM</DIV></TD><TD><DIV class="">Online</DIV><DIV class="">Offline (31 days ago)</DIV></TD><TD>2022-03-03 20:36:28</TD></TR></TBODY></TABLE><P> </P><P>As you can see, the "solution" is bend-over manipulation.  Back to ITWhisperer's point: It is better to fix the data problem than to manipulate display.  For example, the above manipulations do not include "id" field.  There has to be some relationship between values of id and the rest.  Why is the first id smaller?</P> Thu, 03 Mar 2022 20:47:50 GMT yuanliu 2022-03-03T20:47:50Z How to sort this multi-value fields based on the latest timestamp and status? https://community.splunk.com/t5/Splunk-Search/How-to-sort-this-multi-value-fields-based-on-the-latest/m-p/587479#M204593 <P>Hi Splunkers,</P> <P>I need help on how to sort this multi-value fields based on the latest timestamp and status.</P> <P>Here's my dummy query for this.<BR /><BR />| makeresults<BR />| eval hostname = "server101"<BR />| eval id = "123|124"<BR />| eval database_timestamp = "Mar 03, 2022 12:59:46 PM|Feb 23, 2022 1:19:24 PM"<BR />| eval database_status = "Online|Offline (30 days ago)"<BR />| eval server_timestamp = "Feb 22, 2022 1:19:24 PM|Mar 01, 2022 12:59:46 PM"<BR />| eval server_status = "Offline (31 days ago)|Online"<BR />| fields hostname id database_timestamp database_status server_timestamp server_status<BR />| makemv delim="|" database_timestamp<BR />| makemv delim="|" database_status<BR />| makemv delim="|" server_timestamp<BR />| makemv delim="|" server_status<BR />| makemv delim="|" id</P> <P>Below is the sample output and expected output.</P> <TABLE border="1" width="726px" cellspacing="0" cellpadding="0"> <TBODY> <TR> <TD width="121.975px">Current Output:</TD> <TD width="165.1px"> </TD> <TD width="132.163px"> </TD> <TD width="162.387px"> </TD> <TD width="143.375px"> </TD> </TR> <TR> <TD width="121.975px"> </TD> <TD width="165.1px"> </TD> <TD width="132.163px"> </TD> <TD width="162.387px"> </TD> <TD width="143.375px"> </TD> </TR> <TR> <TD width="121.975px">hostname</TD> <TD width="165.1px">database_timestamp</TD> <TD width="132.163px">database_status</TD> <TD width="162.387px">server_timestamp</TD> <TD width="143.375px">server_timestamp</TD> </TR> <TR> <TD width="121.975px">server101</TD> <TD width="165.1px">Mar 03, 2022 12:59:46 PM<BR />Feb 23, 2022 1:19:24 PM</TD> <TD width="132.163px">Online<BR />Offline (30 days ago)</TD> <TD width="162.387px">Feb 22, 2022 1:19:24 PM<BR />Mar 01, 2022 12:59:46 PM</TD> <TD width="143.375px">Offline (31 days ago)<BR />Online</TD> </TR> <TR> <TD width="121.975px"> </TD> <TD width="165.1px"> </TD> <TD width="132.163px"> </TD> <TD width="162.387px"> </TD> <TD width="143.375px"> </TD> </TR> <TR> <TD width="121.975px">Expected Output:</TD> <TD width="165.1px"> </TD> <TD width="132.163px"> </TD> <TD width="162.387px"> </TD> <TD width="143.375px"> </TD> </TR> <TR> <TD width="121.975px"> </TD> <TD width="165.1px"> </TD> <TD width="132.163px"> </TD> <TD width="162.387px"> </TD> <TD width="143.375px"> </TD> </TR> <TR> <TD width="121.975px">hostname</TD> <TD width="165.1px">database_timestamp</TD> <TD width="132.163px">database_status</TD> <TD width="162.387px"><FONT color="#0000FF">server_timestamp</FONT></TD> <TD width="143.375px"><FONT color="#0000FF">server_status</FONT></TD> </TR> <TR> <TD width="121.975px">server101</TD> <TD width="165.1px">Mar 03, 2022 12:59:46 PM<BR />Feb 23, 2022 1:19:24 PM</TD> <TD width="132.163px">Online<BR />Offline (30 days ago)</TD> <TD width="162.387px"><FONT color="#0000FF">Mar 01, 2022 12:59:46 PM </FONT><BR /><FONT color="#0000FF">Feb 22, 2022 1:19:24 PM</FONT></TD> <TD width="143.375px"><FONT color="#0000FF">Online </FONT><BR /><FONT color="#0000FF">Offline (31 days ago)</FONT></TD> </TR> </TBODY> </TABLE> <P> </P> Thu, 03 Mar 2022 20:54:23 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-this-multi-value-fields-based-on-the-latest/m-p/587479#M204593 kelz 2022-03-03T20:54:23Z Re: How to sort multi-value fields https://community.splunk.com/t5/Splunk-Search/How-to-sort-this-multi-value-fields-based-on-the-latest/m-p/587506#M204605 <P>Rather than sorting them after they have been added to multivalue fields, can you sort them beforehand?</P> Thu, 03 Mar 2022 17:54:32 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-this-multi-value-fields-based-on-the-latest/m-p/587506#M204605 ITWhisperer 2022-03-03T17:54:32Z Re: How to sort multi-value fields https://community.splunk.com/t5/Splunk-Search/How-to-sort-this-multi-value-fields-based-on-the-latest/m-p/587530#M204615 <P>ITWhisperer expresses exactly how I see the problem.  It is so much cleaner (and easier) to fix the multivalue order in the search that produces values as opposed to tweak display after mixed up values.</P><P>This said, you can use a combination of <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#mvmap.28X.2CY.29" target="_blank" rel="noopener">mvmap</A>, <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#mvzip.28X.2CY.2C.22Z.22.29" target="_blank" rel="noopener">mvzip</A>, <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#mvsort.28X.29" target="_self">mvsort</A>, and <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions#split.28X.2C.22Y.22.29" target="_blank" rel="noopener">split</A> to get what you wanted.  I'll present two versions, one specific, one generic.</P><P>If you are certain that database_* is already correct, this slightly simpler filter helps to illustrate how the thought process goes:</P><P> </P><LI-CODE lang="markup">| makeresults | eval hostname = "server101" | eval id = "123|124" | eval database_timestamp = "Mar 03, 2022 12:59:46 PM|Feb 23, 2022 1:19:24 PM" | eval database_status = "Online|Offline (30 days ago)" | eval server_timestamp = "Feb 22, 2022 1:19:24 PM|Mar 01, 2022 12:59:46 PM" | eval server_status = "Offline (31 days ago)|Online" | fields hostname id database_timestamp database_status server_timestamp server_status | makemv delim="|" database_timestamp | makemv delim="|" database_status | makemv delim="|" server_timestamp | makemv delim="|" server_status | makemv delim="|" id ``` below is multivalue manipulation ``` | eval server_lag=mvmap(server_timestamp, now()-strptime(server_timestamp, "%b %d, %Y %I:%M:%S %p")) ``` establish reverse time sequence ``` | eval server_status=mvzip(server_lag, mvzip(server_timestamp, server_status, "|"), "|") ``` zip affected fields with server_lag ``` | eval server_status=mvsort(server_status) ``` sort by reverse time using server_lag ``` | eval server_timestamp=mvmap(server_status, mvindex(split(server_status, "|"), 1)) ``` extract timestamp field from zip ``` | eval server_status=mvmap(server_status, mvindex(split(server_status, "|"), 2)) ``` extract status field from zip ``` | fields - server_lag</LI-CODE><P> </P><P>A more general approach is to use <A href="https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Foreach" target="_blank" rel="noopener">foreach</A> to reorder all fields that may fall into disorder.</P><P> </P><LI-CODE lang="markup">| makeresults | eval hostname = "server101" | eval id = "123|124" | eval database_timestamp = "Mar 03, 2022 12:59:46 PM|Feb 23, 2022 1:19:24 PM" | eval database_status = "Online|Offline (30 days ago)" | eval server_timestamp = "Feb 22, 2022 1:19:24 PM|Mar 01, 2022 12:59:46 PM" | eval server_status = "Offline (31 days ago)|Online" | fields hostname id database_timestamp database_status server_timestamp server_status | makemv delim="|" database_timestamp | makemv delim="|" database_status | makemv delim="|" server_timestamp | makemv delim="|" server_status | makemv delim="|" id ``` below is multivalue manipulation ``` | foreach *_status ``` iterate through all fields that may fall into disorder ``` [eval <<MATCHSTR>>_lag=mvmap(<<MATCHSTR>>_timestamp, now()-strptime(<<MATCHSTR>>_timestamp, "%b %d, %Y %I:%M:%S %p")) ``` establish reverse time sequence ``` | eval <<MATCHSTR>>_status=mvzip(<<MATCHSTR>>_lag, mvzip(<<MATCHSTR>>_timestamp, <<MATCHSTR>>_status, "|"), "|") ``` zip affected fields with server_lag ``` | eval <<MATCHSTR>>_status=mvsort(<<MATCHSTR>>_status) ``` sort by reverse time using server_lag ``` | eval <<MATCHSTR>>_timestamp=mvmap(<<MATCHSTR>>_status, mvindex(split(<<MATCHSTR>>_status, "|"), 1)) ``` retrieve timestamp field from zip ``` | eval <<MATCHSTR>>_status=mvmap(<<MATCHSTR>>_status, mvindex(split(<<MATCHSTR>>_status, "|"), 2)) ``` retrieve status field from zip ``` ] | fields - *_lag</LI-CODE><P> </P><P> </P><P> </P><P>With sample data, the output is</P><TABLE><TBODY><TR><TD>hostname</TD><TD><DIV class="">id</DIV></TD><TD><DIV class="">database_timestamp</DIV></TD><TD><DIV class="">database_status</DIV></TD><TD><DIV class="">server_timestamp</DIV></TD><TD><DIV class="">server_status</DIV></TD><TD>_time</TD></TR><TR><TD>server101</TD><TD><DIV class="">123</DIV><DIV class="">124</DIV></TD><TD><DIV class="">Mar 03, 2022 12:59:46 PM</DIV><DIV class="">Feb 23, 2022 1:19:24 PM</DIV></TD><TD><DIV class="">Online</DIV><DIV class="">Offline (30 days ago)</DIV></TD><TD><DIV class="">Mar 01, 2022 12:59:46 PM</DIV><DIV class="">Feb 22, 2022 1:19:24 PM</DIV></TD><TD><DIV class="">Online</DIV><DIV class="">Offline (31 days ago)</DIV></TD><TD>2022-03-03 20:36:28</TD></TR></TBODY></TABLE><P> </P><P>As you can see, the "solution" is bend-over manipulation.  Back to ITWhisperer's point: It is better to fix the data problem than to manipulate display.  For example, the above manipulations do not include "id" field.  There has to be some relationship between values of id and the rest.  Why is the first id smaller?</P> Thu, 03 Mar 2022 20:47:50 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-this-multi-value-fields-based-on-the-latest/m-p/587530#M204615 yuanliu 2022-03-03T20:47:50Z