Getting Data In

CSV Input, duplicates values

miguel1423
Explorer

I am monitoring my PKI certificates with a PowerShell script which returns the number of valide days for each certificats in a Excel document on my splunk server.

My script is running each days to update my value "validity", the problem is I have multiple entry for each certificat with the validity diffrents on my dashboard.

How can I only update the value "validity" after that my scritp run and not have a new entry ?

My quealt textry:
index = "index_pki" sourcetype = "splunk_csv" | statistics count by ReqID, CN, Template, Validity, NotAfter, NotBefore, San, Tumbprint | where validity <30 | sort Validity

And the printscrenn

Thanks a lot

Tags (2)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

You just need to dedup your results for the most recent value for each certificate, or you can ask for latest() for each field in your stats command.

This way should work, assuming that ReqID and CN are the key.

index = "index_pki" sourcetype = "splunk_csv" 
| fields _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint
| stats max(_time) as _time latest(*) as * by ReqID, CN
| where Validity < 30 
| sort Validity
| table _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint

This way should also work, assuming that ReqID and CN are the key.

index = "index_pki" sourcetype = "splunk_csv" 
| fields _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint
| dedup  ReqID CN
| where Validity < 30 
| sort Validity
| table _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint

Note - I did not correct any field names, but assumed your spelling was correct.

View solution in original post

0 Karma

to4kawa
SplunkTrust
SplunkTrust
 index = "index_pki" sourcetype = "splunk_csv" 
| stats max(Validity) as Validity by ReqID, CN, Template,NotAfter, NotBefore, San, Tumbprint
| where Validity < 30 
| sort Validity

How's this? stats by clause makes unique field values.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

You just need to dedup your results for the most recent value for each certificate, or you can ask for latest() for each field in your stats command.

This way should work, assuming that ReqID and CN are the key.

index = "index_pki" sourcetype = "splunk_csv" 
| fields _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint
| stats max(_time) as _time latest(*) as * by ReqID, CN
| where Validity < 30 
| sort Validity
| table _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint

This way should also work, assuming that ReqID and CN are the key.

index = "index_pki" sourcetype = "splunk_csv" 
| fields _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint
| dedup  ReqID CN
| where Validity < 30 
| sort Validity
| table _time ReqID  CN  Template  Validity  NotAfter  NotBefore  San  Tumbprint

Note - I did not correct any field names, but assumed your spelling was correct.

View solution in original post

0 Karma

miguel1423
Explorer

Hello,

Thank you for your answer, your second solution works pretty well 😃 but what does "field" do ? | fields _time ReqID CN Template Validity NotAfter NotBefore San Tumbprint

Regards

DalJeanis
SplunkTrust
SplunkTrust

The fields command limits the results to exactly the fields that you list, making sure that those fields are extracted but saving the system from having to attempt to extract anything else. (Technically, the underscore field _time does not have to be listed, but I put it there anyway for documentation purposes.)

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!