topic Re: Field comparison with multi-value columns in Reporting

Field comparison with multi-value columns

Cranie — Fri, 12 Feb 2021 17:40:24 GMT

\Hi,

I have some data which looks likes this from a Splunk report:

Server	Prod1-Ver	Prod1-Latest	Prod2-Ver	Prod2-Latest
server1.com	11.7.1.2	11.7.1.2	8.2	8.3
server2.com	11.3.1.0	11.3.1.2\|11.5.1.1	6.8	6.10\|7.9
server3.com	11.7.0.2	11.7.1.1	7.4	6.10\|7.9

I want to be able to compare the Prod-Ver to the corresponding Prod-Latest. Some of the latest ones will have different products for different point releases or, in the above example, 11.3 or 11.5 can be used, in that example, I need to check the 11.3.1.0 against the 11.3.1.2 and not the 11.5.1.1.

I figure I will need to use mvexpand and have multiple rows, then exclude the ones not matching the higher point level.

Is there an easier way of doing it?

to do what I suggested, I think I would need to have 4 versions of the same line (more when there are other products) to cover all combinations, i.e. server2.com line in above.

Thanks.

Re: Field comparison with multi-value columns

bowesmana — Fri, 12 Feb 2021 22:33:50 GMT

If you have Splunk 8, then you can use mvmap to perform tests on multivalue fields without having to go through the mvexpand route.

https://docs.splunk.com/Documentation/Splunk/8.1.2/SearchReference/MultivalueEvalFunctions#mvmap.28X.2CY.29

Re: Field comparison with multi-value columns

tread_splunk — Sat, 13 Feb 2021 13:12:29 GMT

I made a bit of progress using mvexpand (I haven't experimented with mvmap yet)...

I haven't worked out the logic for doing the version/latest comparison based on major / minor versions 🙂

Re: Field comparison with multi-value columns

Cranie — Mon, 15 Feb 2021 11:00:40 GMT

Thanks for the code, will try that now.

As for the reason, we have a product we use, DataStage (DS) and there are many supported Service Tier components. We have a DS inventory that collects DS, DB2 and WebSphere versions (among others).

For DB2 and WebSphere there are multiple supported major versions. So, for example, we have:

DS 11.5

This is supported on DB2 11.1 and 11.5 and WebSphere may be 9.0.0 and 9.0.5.

I now need to check the version to max supported version and see where there are discrepancies, this will then be enhanced with patch versions for the sub products, allowing us to manage the patches across the estate.

The original data collection is a little bit of a mess in terms of how it is collected and put into Splunk (3 columns and multivalue ones to work around our engineering limitations.

I'll check and confirm if this does what I want - thank you again for the help.

Re: Field comparison with multi-value columns

Cranie — Mon, 15 Feb 2021 12:54:47 GMT

Will check this out too, thank you, I will check with is the better solution, I have managed it using the for loop mentioned above. (not sure if there is a "best" method of doing this. Thanks for the replies.

Re: Field comparison with multi-value columns

Cranie — Mon, 15 Feb 2021 13:54:17 GMT

This is the portion of code I have adapted from yours (my Splunk skills are not the best):

This works as expected and shows the versions with the respective max version, thank you again.

Re: Field comparison with multi-value columns

tread_splunk — Mon, 15 Feb 2021 14:22:03 GMT

Good work! What are you planning to do with the situation where there are more than x2 products? I'm wondering whether there is a way for the SPL to deal with it.

Re: Field comparison with multi-value columns

tread_splunk — Mon, 15 Feb 2021 17:40:24 GMT

Not the prettiest, but I was fiddling around with an approach to dynamically construct your search criteria for when you have multiple, unknown number of products. They need to have a consistent naming convention (e.g. end in -Latest as per your demo data), and I've only experimented with Major/Minor number i.e. 11.3 not 11.3.2, but I think the general pattern applies. There's a bit of duplicated code regarding the construction of the field called 'search'. I had hoped I'd be able to do it without the duplication but for now I'm relying on it

| makeresults | eval _raw="Server Prod1-Ver Prod1-Latest Prod2-Ver Prod2-Latest server1.com 11.7.1.2 11.7.1.2 8.2 8.3 server2.com 11.3.1.0 11.3.1.2|11.5.1.1 6.8 6.10|7.9 server3.com 11.7.0.2 11.7.1.1 7.4 6.10|7.9" | multikv forceheader=1 | fields - _time _raw linecount | eval search="" | foreach *_Latest [ eval <<MATCHSTR>>_Latest=split(<<MATCHSTR>>_Latest,"|") | mvexpand <<MATCHSTR>>_Latest | rex field=<<MATCHSTR>>_Latest "(?<<<MATCHSTR>>_LatestMaj>\d+).(?<<<MATCHSTR>>_LatestMin>\d+)" | rex field=<<MATCHSTR>>_Ver "(?<<<MATCHSTR>>_VerMaj>\d+).(?<<<MATCHSTR>>_VerMin>\d+)" | eval search=search." OR (<<MATCHSTR>>_LatestMaj=<<MATCHSTR>>_VerMaj AND <<MATCHSTR>>_LatestMin=<<MATCHSTR>>_VerMin)"] | where [| makeresults | eval _raw="Server Prod1-Ver Prod1-Latest Prod2-Ver Prod2-Latest server1.com 11.7.1.2 11.7.1.2 8.2 8.3" | multikv forceheader=1 | eval search="" | foreach *_Latest [ eval search=search." OR (<<MATCHSTR>>_LatestMaj=<<MATCHSTR>>_VerMaj AND <<MATCHSTR>>_LatestMin=<<MATCHSTR>>_VerMin)"] | eval search=substr(search,4) | return $search]

Re: Field comparison with multi-value columns

tread_splunk — Tue, 16 Feb 2021 13:29:29 GMT

Much better approach (in my view) than my previous attempt...

I added some additional test data. The transpose, untable, xyseries combination converts your table into a list with 4 columns ... server, product, version and latest-version. Now you can use straightforward evals and wheres to manipulate / filter. You'll need to adapt the filter (penultimate line "where LatestMaj=VerMaj AND VerMin!=LatestMin") to deal with your versioning. As per your previous response.

Bonus (perhaps unnecessary)...if you want to put the formatting back to resemble your original table format you can add the. following lines...

| eval fnameVer=ProductName."_Ver",fnameLatest=ProductName."_Latest" | eval {fnameLatest}=Latest,{fnameVer}=Ver | fields - fname* Ver Latest ProductName | stats values(*) as * by server

...which should leave you with a table in your original format only showing entries with out-of-date software.

Re: Field comparison with multi-value columns

tread_splunk — Mon, 01 Mar 2021 17:02:45 GMT

Hi @Cranie

Did you get the results you wanted in the end?

Re: Field comparison with multi-value columns

Cranie — Tue, 11 May 2021 08:00:36 GMT

Apologies for the late reply. I struggled to log on in work place. Yes, I managed to do what I have needed and implemented the same in a more complex query for my reports. Thanks all for the help and suggestions! Really appreciate the community help.