Splunk Search

How to write a search that sorts by field with a date format in its values?

lbogle
Contributor

Hello Splunkers,
I have a search that's coming up nicely but I need to refine the search further by sorting by a field name of Last Audit that happens to have dates in it in the format of 1/1/2011. I'd like to have my existing query but include something along the lines of "Last Audit"=>(1/1/2011). Basically, give me the results from anything since 1/1/2011. What do I need to add to create that functionality?
Thanks!

Tags (3)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try something like this (remove some filters as they were redundant)

index=test_assets  "System Type"="Laptop" OR "System Type"="Workstation" OR "Sub Type"=Workstation OR "Sub Type"=Laptop | rename "Asset Tag Number" as "Asset Tag" | rename "Sub Type" as "System Type" | eventstats values("Last Audit") as "Last Audit" by "Asset Tag" | where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2011","%m/%d/%Y")| dedup "Asset Tag"| stats dc(Asset Tag) by source

View solution in original post

lbogle
Contributor

I switched the search up a little. Check it out and let me know if you think this should work:
index=test_assets source="C:\Splunk Test Assets\Altiris_hostnames.csv" | rename "HostName" as hostname | rename "System Name" as hostname | where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2014","%m/%d/%Y") | replace "C:\Splunk Test Assets\Altiris_hostnames.csv" with Altiris in source | eval hostname=lower(hostname) | dedup hostname | stats dc(hostname)

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this (remove some filters as they were redundant)

index=test_assets  "System Type"="Laptop" OR "System Type"="Workstation" OR "Sub Type"=Workstation OR "Sub Type"=Laptop | rename "Asset Tag Number" as "Asset Tag" | rename "Sub Type" as "System Type" | eventstats values("Last Audit") as "Last Audit" by "Asset Tag" | where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2011","%m/%d/%Y")| dedup "Asset Tag"| stats dc(Asset Tag) by source

lbogle
Contributor

index=test_assets NOT "System Type"="Server" NOT "System Type"="Virtual" NOT "System Type"="Thin Client" NOT "System Type"="Blanks" "System Type"="Laptop" OR "System Type"="Workstation" OR "Sub Type"=Workstation OR "Sub Type"=Laptop | rename "Asset Tag Number" as "Asset Tag" | rename "Sub Type" as "System Type" | dedup "Asset Tag" | stats dc(Asset Tag) by source

I'm just not sure how to identify that "Last Audit" field (which contains a series of dates) in the 1/1/2001 format and search by anything newer than a certain date.

0 Karma

lbogle
Contributor

Summary: There are two asset databases represented by two .csv files that are fed into a single index. The search excludes several asset types, focuses on Laptops and Workstations and searches by a common "Asset Tag" field between them. There is a single date field in one of the .csv files I am trying to filter by so that only assets counts are done after that time range are represented.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You can apply filter for field "Last Audit" to be greater than or equal to your date. Something like this
Assuming "Last Audit" is string.

....| where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2011","%m/%d/%Y")

If post your current query, we can suggest a better solution(if any 🙂 ).

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