Reporting

Field comparison with multi-value columns

Cranie
Explorer

\Hi,

 

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

ServerProd1-VerProd1-LatestProd2-VerProd2-Latest
server1.com11.7.1.211.7.1.28.28.3
server2.com11.3.1.011.3.1.2|11.5.1.16.86.10|7.9
server3.com11.7.0.211.7.1.17.46.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.

Labels (1)
0 Karma
1 Solution

tread_splunk
Splunk Employee
Splunk Employee

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

| 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 
| foreach *_Latest 
    [ eval <<FIELD>>=split(<<FIELD>>,"|") 
    | mvexpand <<FIELD>>]

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

View solution in original post

0 Karma

tread_splunk
Splunk Employee
Splunk Employee

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

| 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 
| foreach *_Latest 
    [ eval <<FIELD>>=split(<<FIELD>>,"|") 
    | mvexpand <<FIELD>>]

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

0 Karma

Cranie
Explorer

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

| foreach RHELSupportedMax DB2SupportedMax 
    [ eval <<FIELD>>=split(<<FIELD>>,"|") 
    | mvexpand <<FIELD>>]

| eval RHELVersionMain=RHELVersion
| rex mode=sed field=RHELVersionMain "s/\..*//"
| eval RHELVersionMainMax=RHELSupportedMax
| rex mode=sed field=RHELVersionMainMax "s/\..*//"
| rex mode=sed field=RHELVersionMainMax "s/\..*//"


| eval DB2VersionMain=DB2Version
| rex mode=sed field=DB2VersionMain "s/\./&@/2"
| rex mode=sed field=DB2VersionMain "s/\.@.*//"


| eval DB2SupportedMaxMain=DB2SupportedMax
| rex mode=sed field=DB2SupportedMaxMain "s/\./&@/2"
| rex mode=sed field=DB2SupportedMaxMain "s/\.@.*//"


| where RHELVersionMain = RHELVersionMainMax
| where DB2VersionMain = DB2SupportedMaxMain

 

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

0 Karma

tread_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

tread_splunk
Splunk Employee
Splunk Employee

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]

 

0 Karma

tread_splunk
Splunk Employee
Splunk Employee

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

| 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
server4.com	11.3.1.0	11.5.1.1	6.8	6.8
server5.com	11.5.1.0	11.5.1.1	6.8	6.8
server6.com	11.5.1.0	11.5.1.1" 
| multikv forceheader=1 
| fields - _time _raw linecount 
| transpose 0 header_field=Server column_name=product 
| search product!=_* 
| untable product server version 
| rex field=product "(?<ProductName>.*)_(?<ProductVariant>.*)" 
| eval serverProd=server.":".ProductName 
| fields - server ProductName 
| xyseries serverProd ProductVariant version 
| rex field=serverProd "(?<server>.*):(?<ProductName>.*)" 
| fields - serverProd 
| table server ProductName Ver Latest 
| eval Latest=split(Latest,"|") 
| mvexpand Latest 
| rex field=Latest "(?<LatestMaj>\d+).(?<LatestMin>\d+)" 
| rex field=Ver "(?<VerMaj>\d+).(?<VerMin>\d+)" 
| where LatestMaj=VerMaj AND VerMin!=LatestMin 
| fields - LatestM* VerM* 

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.

0 Karma

Cranie
Explorer

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. 

0 Karma

tread_splunk
Splunk Employee
Splunk Employee

Hi @Cranie 

Did you get the results you wanted in the end?

0 Karma

Cranie
Explorer

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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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...

 

0 Karma

Cranie
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...