Splunk Search

help on a complex lookup data matching in order to calculate a new field

jip31
Builder

Hi

I use the search below in order to catch a field called "flag_patch_version" from a csv file called "patchlevel.csv"

| inputlookup host.csv 
| lookup patchlevel.csv "Computer" as host 
| stats count by host flag_patch_version 
| where isnotnull(flag_patch_version) 
| rename host as Hostname, flag_patch_version as "Current Patch level"
| fields - count 
| table Hostname "Current Patch level" 
| sort +"Current Patch level" limit=10

I have a second csv file called "patch_in_late.csv" you can see in https://www.cjoint.com/c/IGxhVGCNdNb
What I need is to add a column called "patch_in_late_count" in my main search which compare the current patch level of a specific hostname to the current patch level in "patch_in_late.csv"
The syntax of the patch level is W10P*041.$w$
The number in bold is incremented every month with + 1
If for July, the patch level is W10P
041.$w$, it will be W10P042*.$w$ for august
By comparison, I mean that I need to count the number of patch in late between the 2 data sources for the current month
Example:
Imagine that the current patch level for a specific machine is W10P041.$w$ and the patch level for the current month is W10P045.$w$ in "patch_in_late.csv", then I need to display in "patch_in_late_count" colum the difference between the two patches, that is to say 4 (45 - 41)
Is anybody can help me on this complex development please??

Tags (1)
0 Karma
1 Solution

dmarling
Builder

I was able to download the file you linked. This is possible with the current formatting in that file, but it would be simpler and more sustainable if you did not create a column for each class (W7, W8, and W10) and instead put that in a single column called expectedversion or something. The version class can be extracted from the name to create the class field. Here's how I transformed that lookup file that you linked:

| inputlookup patch_in_late.csv 
| eval latepatch=mvappend(W10,W7,W8) 
| fields + latepatch month 
| mvexpand latepatch 
| rex field=latepatch "^(?<versiontype>W\d+)P(?<version>\d+)" 
| eval versionlate=tonumber(version)

That transforms your table from this: alt text

To this:
alt text

Now that you have the table adjusted you can perform a join to the first search to create the "patch_in_late_count" field you are looking for. Here's a run anywhere example demonstrating this:

| makeresults count=1 
| eval host="host1" 
| eval "Current Patch level" ="W10P039.$w$" 
| eval month=strftime(now(), "%B")
| rex field="Current Patch level"  "^(?<versiontype>W\d+)P(?<version>\d+)" 
| eval version=tonumber(version) 
| eval joiner=versiontype.month 
| join type=left joiner
    [| inputlookup patch_in_late.csv 
    | eval latepatch=mvappend(W10,W7,W8) 
    | fields + latepatch month 
    | mvexpand latepatch 
    | rex field=latepatch "^(?<versiontype>W\d+)P(?<version>\d+)" 
    | eval versionlate=tonumber(version) 
    | eval joiner=versiontype.month 
    | table joiner versionlate latepatch]
| eval patch_in_late_count=if((versionlate-version)>0, versionlate-version, "Up to date!")
| table host "Current Patch level"  latepatch patch_in_late_count
| rename latepatch as "Expected Patch Level"

And here's how I think it will fit with the query you provided as long as the lookup file you attached is indeed the same data you will be using in your search:

| inputlookup host.csv 
| lookup patchlevel.csv "Computer" as host 
| stats count by host flag_patch_version 
| where isnotnull(flag_patch_version) 
| rename host as Hostname, flag_patch_version as "Current Patch level" 
| fields - count 
| table Hostname "Current Patch level" 
| sort +"Current Patch level" limit=10 
| eval month=strftime(now(), "%B") 
| rex field="Current Patch level" "^(?<versiontype>W\d+)P(?<version>\d+)" 
| eval version=tonumber(version) 
| eval joiner=versiontype.month 
| join type=left joiner 
    [| inputlookup patch_in_late.csv 
    | eval latepatch=mvappend(W10,W7,W8) 
    | fields + latepatch month 
    | mvexpand latepatch 
    | rex field=latepatch "^(?<versiontype>W\d+)P(?<version>\d+)" 
    | eval versionlate=tonumber(version) 
    | eval joiner=versiontype.month 
    | table joiner versionlate latepatch] 
| eval patch_in_late_count=if((versionlate-version)>0, versionlate-version, "Up to date!") 
| table Hostname "Current Patch level" latepatch patch_in_late_count 
| rename latepatch as "Expected Patch Level"
If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

dmarling
Builder

I was able to download the file you linked. This is possible with the current formatting in that file, but it would be simpler and more sustainable if you did not create a column for each class (W7, W8, and W10) and instead put that in a single column called expectedversion or something. The version class can be extracted from the name to create the class field. Here's how I transformed that lookup file that you linked:

| inputlookup patch_in_late.csv 
| eval latepatch=mvappend(W10,W7,W8) 
| fields + latepatch month 
| mvexpand latepatch 
| rex field=latepatch "^(?<versiontype>W\d+)P(?<version>\d+)" 
| eval versionlate=tonumber(version)

That transforms your table from this: alt text

To this:
alt text

Now that you have the table adjusted you can perform a join to the first search to create the "patch_in_late_count" field you are looking for. Here's a run anywhere example demonstrating this:

| makeresults count=1 
| eval host="host1" 
| eval "Current Patch level" ="W10P039.$w$" 
| eval month=strftime(now(), "%B")
| rex field="Current Patch level"  "^(?<versiontype>W\d+)P(?<version>\d+)" 
| eval version=tonumber(version) 
| eval joiner=versiontype.month 
| join type=left joiner
    [| inputlookup patch_in_late.csv 
    | eval latepatch=mvappend(W10,W7,W8) 
    | fields + latepatch month 
    | mvexpand latepatch 
    | rex field=latepatch "^(?<versiontype>W\d+)P(?<version>\d+)" 
    | eval versionlate=tonumber(version) 
    | eval joiner=versiontype.month 
    | table joiner versionlate latepatch]
| eval patch_in_late_count=if((versionlate-version)>0, versionlate-version, "Up to date!")
| table host "Current Patch level"  latepatch patch_in_late_count
| rename latepatch as "Expected Patch Level"

And here's how I think it will fit with the query you provided as long as the lookup file you attached is indeed the same data you will be using in your search:

| inputlookup host.csv 
| lookup patchlevel.csv "Computer" as host 
| stats count by host flag_patch_version 
| where isnotnull(flag_patch_version) 
| rename host as Hostname, flag_patch_version as "Current Patch level" 
| fields - count 
| table Hostname "Current Patch level" 
| sort +"Current Patch level" limit=10 
| eval month=strftime(now(), "%B") 
| rex field="Current Patch level" "^(?<versiontype>W\d+)P(?<version>\d+)" 
| eval version=tonumber(version) 
| eval joiner=versiontype.month 
| join type=left joiner 
    [| inputlookup patch_in_late.csv 
    | eval latepatch=mvappend(W10,W7,W8) 
    | fields + latepatch month 
    | mvexpand latepatch 
    | rex field=latepatch "^(?<versiontype>W\d+)P(?<version>\d+)" 
    | eval versionlate=tonumber(version) 
    | eval joiner=versiontype.month 
    | table joiner versionlate latepatch] 
| eval patch_in_late_count=if((versionlate-version)>0, versionlate-version, "Up to date!") 
| table Hostname "Current Patch level" latepatch patch_in_late_count 
| rename latepatch as "Expected Patch Level"
If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

jip31
Builder

I just to create the csv like you said
can you confirm its ok?
https://www.cjoint.com/c/IGzgTkkzoic

0 Karma

dmarling
Builder

You want to have 1 row per expectedversion. The lookup file you attached has 1 row per month. Ideally you want it to be this:

month   expectedversion
July    W76P96.$w$
July    W86P078.$w$
July    W10P040.$w$
August  W76P97.$w$
August  W86P079.$w$
August  W10P041.$w$
September   W76P98.$w$
September   W86P080.$w$
September   W10P042.$w$

That will allow you to then make your join without having to do the transformation stuff I was doing:

 | inputlookup host.csv 
 | lookup patchlevel.csv "Computer" as host 
 | stats count by host flag_patch_version 
 | where isnotnull(flag_patch_version) 
 | rename host as Hostname, flag_patch_version as "Current Patch level" 
 | fields - count 
 | table Hostname "Current Patch level" 
 | sort +"Current Patch level" limit=10 
 | eval month=strftime(now(), "%B") 
 | rex field="Current Patch level" "^(?<versiontype>W\d+)P(?<version>\d+)" 
 | eval version=tonumber(version) 
 | eval joiner=versiontype.month 
 | join type=left joiner 
     [| inputlookup patch_in_late.csv 
     | rex field=expectedversion "^(?<versiontype>W\d+)P(?<version>\d+)" 
     | eval versionlate=tonumber(version) 
     | eval joiner=versiontype.month 
     | table joiner versionlate latepatch] 
 | eval patch_in_late_count=if((versionlate-version)>0, versionlate-version, "Up to date!") 
 | table Hostname "Current Patch level" latepatch patch_in_late_count 
 | rename latepatch as "Expected Patch Level"
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

jip31
Builder

Hi dmarling
I have an issue with because the latepatch column is empty:

| inputlookup patch_in_late.csv 
     | eval latepatch=mvappend(W10,W7,W8) 
     | fields + latepatch month 
     | mvexpand latepatch   
     | rex field=latepatch "^(?<versiontype>W\d+)P(?<version>\d+)" 
 | eval versionlate=tonumber(version)

do you know why please?
And I wonder if its not better to adapt this piece of code with the csv format you proposed me??
That is to say :

 month    expectedversion
 July    W76P96.$w$
 July    W86P078.$w$
 July    W10P040.$w$
 August    W76P97.$w$
 August    W86P079.$w$
 August    W10P041.$w$
 September    W76P98.$w$
 September    W86P080.$w$
 September    W10P042.$w$
0 Karma

dmarling
Builder

Hi jip31,

The original query was using the lookup file you originally linked. If you switch formats to the one I recommended the query I put in that comment will function which I will repost below:

  | inputlookup host.csv 
  | lookup patchlevel.csv "Computer" as host 
  | stats count by host flag_patch_version 
  | where isnotnull(flag_patch_version) 
  | rename host as Hostname, flag_patch_version as "Current Patch level" 
  | fields - count 
  | table Hostname "Current Patch level" 
  | sort +"Current Patch level" limit=10 
  | eval month=strftime(now(), "%B") 
  | rex field="Current Patch level" "^(?<versiontype>W\d+)P(?<version>\d+)" 
  | eval version=tonumber(version) 
  | eval joiner=versiontype.month 
  | join type=left joiner 
      [| inputlookup patch_in_late.csv 
      | rex field=expectedversion "^(?<versiontype>W\d+)P(?<version>\d+)" 
      | eval versionlate=tonumber(version) 
      | eval joiner=versiontype.month 
      | table joiner versionlate latepatch] 
  | eval patch_in_late_count=if((versionlate-version)>0, versionlate-version, "Up to date!") 
  | table Hostname "Current Patch level" latepatch patch_in_late_count 
  | rename latepatch as "Expected Patch Level"

The file you linked previously doesn't have column headers on the last two columns so it really wouldn't function properly as a lookup file. I suggest just modifying the lookup file to the two column approach that you correctly listed.

If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

jip31
Builder

Ok thanks dmrling it seems almost working
I have just an issue with "expected patch level" field which is empty
https://www.cjoint.com/c/IGFgh4HSIxh

0 Karma

dmarling
Builder

I see what I did wrong. Sorry about that. The "latepatch" field doesn't exist anymore in the lookup file and I forgot to change that. This should fix that.

 | inputlookup host.csv 
   | lookup patchlevel.csv "Computer" as host 
   | stats count by host flag_patch_version 
   | where isnotnull(flag_patch_version) 
   | rename host as Hostname, flag_patch_version as "Current Patch level" 
   | fields - count 
   | table Hostname "Current Patch level" 
   | sort +"Current Patch level" limit=10 
   | eval month=strftime(now(), "%B") 
   | rex field="Current Patch level" "^(?<versiontype>W\d+)P(?<version>\d+)" 
   | eval version=tonumber(version) 
   | eval joiner=versiontype.month 
   | join type=left joiner 
       [| inputlookup patch_in_late.csv 
       | rex field=expectedversion "^(?<versiontype>W\d+)P(?<version>\d+)" 
       | eval versionlate=tonumber(version) 
       | eval joiner=versiontype.month 
       | table joiner versionlate expectedversion ] 
   | eval patch_in_late_count=if((versionlate-version)>0, versionlate-version, "Up to date!") 
   | table Hostname "Current Patch level" expectedversion 
 patch_in_late_count 
   | rename expectedversion as "Expected Patch Level"
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

jip31
Builder

many thanks for your wonderful help!!
have a nice day

0 Karma

jip31
Builder

hi dmarling!

oh my god! its even more complex than I thought!
I m going to do test in afew days and I keep you aware
have a nice day

0 Karma

ayush1906
Path Finder

I was unable to access your second lookup file. Security issue on your link.

Seeing your requirement , you can append your second lookup column using Appendcols

Appendcols Splunk Documentation Link

For finding the difference you can use Eval after doing append.

Hope this helps,
anyhelp email you can drop mail @ ayush_ece[at]outlook.com

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!