Splunk Search

How do I create a subsearch with a duration greater than a selected value of the original search?

lmzheng
Explorer

The original search is selected with the drop down box at the top of the screen.
I created a subsearch with earliest duration of 24 hours.
I then used the now() - earliest2 to get when the error first occurred within 24 hours.

However, if I use any time less than 24 hours, I receive incomplete results. Some of my table is blank. Any tips on how to clean my code up and fix my search would be great! Thanks

index="example"
| eval ErrorMessage=substr(Message,1,150)
| rex field=ErrorMessage mode=sed "s/User.:.)/User: yyyy/g"
| rex field=ErrorMessage mode=sed "s/[0-]{4,20}/XXXX/g"
| eval eventTime=substr(CreatedAt, 1,16)
| fields *
| search EventType=Errors
| eval ErrorMessage=Server+": "+Application+": " +ErrorMessage
| stats earliest(_time) as Earliest, latest(_time) as Latest, count as "Error Count" by ErrorMessage
| eval difference = round(((now() - Latest)/60), 2)
| eval Log = "Last error occured " +difference+ " minutes ago"
| append

    [search index="prod" earliest=-24h
        | eval ErrorMessage=substr(Message,1,150) 
        | rex field=ErrorMessage mode=sed "s/User.*:.*\)/User: yyyy/g" 
        | rex field=ErrorMessage mode=sed "s/[0-]{4,20}/XXXX/g" 
        | eval eventTime=substr(CreatedAt, 1,16) 
        | fields * 
        | search EventType=Errors 
        | eval ErrorMessage=Server+": "+Application+": " +ErrorMessage
        | stats earliest(_time) as Earliest2, latest(_time) as Latest, count as "Error Count" by ErrorMessage]

| stats first(Earliest) as Earliest, first(Latest) as Latest, first(Earliest2) as "Earliest2", first(Log) as Log, first("Error Count") as "Error Count" by ErrorMessage
| eval Hidden = (now()-Earliest2)/3600
| sort - "Error Count"
| convert ctime(Earliest), ctime(Latest)
| rename Earliest as "Earliest Error", "Latest" as "Latest Error"
| fields ErrorMessage, "Error Count", "Earliest Error", "Latest Error", Log, Hidden #This variable will be hidden for some color palette expressions
| head 10

1 Solution

darrenfuller
Contributor

Without data, i am a tad bit grasping at straws... but here goes:

index="example" Message=* CreatedAt=* EventType=Errors 
| eval ErrorMessage=substr(Message, 1, 150) 
| rex field=ErrorMessage mode=sed "s/User.:.)/User: yyyy/g" 
| rex field=ErrorMessage mode=sed "s/[0-]{4,20}/XXXX/g" 
| eval eventTime=substr(CreatedAt, 1, 16) 
| eval ErrorMessage=Server+": "+Application+": " +ErrorMessage 
| stats earliest(_time) AS EarliestFull latest(_time) AS LatestFull, count AS ErrorCountFull BY ErrorMessage 
| eval difference = round(((now() - Latest)/60), 2) 
| eval Log="Last error occured " +difference+ " minutes ago" 
| join type=left ErrorMessage 
    [ search index="prod" earliest=-24h Message=* CreatedAt=* EventType=Errors 
    | eval ErrorMessage=substr(Message,1,150) 
    | rex field=ErrorMessage mode=sed "s/User.*:.*\)/User: yyyy/g" 
    | rex field=ErrorMessage mode=sed "s/[0-]{4,20}/XXXX/g" 
    | eval eventTime=substr(CreatedAt, 1,16) 
    | eval ErrorMessage=Server+": "+Application+": " +ErrorMessage 
    | stats earliest(_time) AS EarliestSub, count AS ErrorCountSub BY ErrorMessage] 
| eval Hidden = (now()-EarliestSub)/3600 
| fillnull value="n/a" EarliestSub
| fillnull value=0 ErrorCountSub
| eval Hidden=if(EarliestSub="n/a", "n/a", Hidden)
| sort - "ErrorCountFull" 
| convert ctime(EarliestFull) ctime(LatestFull) ctime(EarliestSub) ctime(LatestSub)
| fields + ErrorMessage ErrorCountFull EarliestFull LatestFull Log Hidden 
| rename ErrorCountFull AS "Error Count" EarliestFull AS "Earliest Error" LatestFull AS "Latest Error" 
| head 10

Changes are as follows:

1) added the "Message=* CreatedAt=* EventType=Errors" to the filtering lines of both the outside search and the subsearch to limit to events that have values in the fields you are referencing. Also removed the "| search EventType=Errors" later since it's no longer needed
2) removed the "fields *" lines
3) Changed the append to a left join , so that the third stats should no longer be required.

4) filled in n/a on EarliestSub field, for ErrorMessages that have not occurred in the last 24h (or whatever timeframe you put to the sub search)
5) Removed lots of commas and changed some capitalizations for readability

...?

View solution in original post

0 Karma

darrenfuller
Contributor

Without data, i am a tad bit grasping at straws... but here goes:

index="example" Message=* CreatedAt=* EventType=Errors 
| eval ErrorMessage=substr(Message, 1, 150) 
| rex field=ErrorMessage mode=sed "s/User.:.)/User: yyyy/g" 
| rex field=ErrorMessage mode=sed "s/[0-]{4,20}/XXXX/g" 
| eval eventTime=substr(CreatedAt, 1, 16) 
| eval ErrorMessage=Server+": "+Application+": " +ErrorMessage 
| stats earliest(_time) AS EarliestFull latest(_time) AS LatestFull, count AS ErrorCountFull BY ErrorMessage 
| eval difference = round(((now() - Latest)/60), 2) 
| eval Log="Last error occured " +difference+ " minutes ago" 
| join type=left ErrorMessage 
    [ search index="prod" earliest=-24h Message=* CreatedAt=* EventType=Errors 
    | eval ErrorMessage=substr(Message,1,150) 
    | rex field=ErrorMessage mode=sed "s/User.*:.*\)/User: yyyy/g" 
    | rex field=ErrorMessage mode=sed "s/[0-]{4,20}/XXXX/g" 
    | eval eventTime=substr(CreatedAt, 1,16) 
    | eval ErrorMessage=Server+": "+Application+": " +ErrorMessage 
    | stats earliest(_time) AS EarliestSub, count AS ErrorCountSub BY ErrorMessage] 
| eval Hidden = (now()-EarliestSub)/3600 
| fillnull value="n/a" EarliestSub
| fillnull value=0 ErrorCountSub
| eval Hidden=if(EarliestSub="n/a", "n/a", Hidden)
| sort - "ErrorCountFull" 
| convert ctime(EarliestFull) ctime(LatestFull) ctime(EarliestSub) ctime(LatestSub)
| fields + ErrorMessage ErrorCountFull EarliestFull LatestFull Log Hidden 
| rename ErrorCountFull AS "Error Count" EarliestFull AS "Earliest Error" LatestFull AS "Latest Error" 
| head 10

Changes are as follows:

1) added the "Message=* CreatedAt=* EventType=Errors" to the filtering lines of both the outside search and the subsearch to limit to events that have values in the fields you are referencing. Also removed the "| search EventType=Errors" later since it's no longer needed
2) removed the "fields *" lines
3) Changed the append to a left join , so that the third stats should no longer be required.

4) filled in n/a on EarliestSub field, for ErrorMessages that have not occurred in the last 24h (or whatever timeframe you put to the sub search)
5) Removed lots of commas and changed some capitalizations for readability

...?

0 Karma

lmzheng
Explorer

So I tried it out and I had to make a few tweaks.

I was wondering if you could explain the type=left and it's use cases.

Append and join seem almost similar to me.

Also, the search wasn't accurate until I placed | search EventType=Errors before the eval.

By accurate, I compared it to our old dash board and the error count was extremely high.

0 Karma

darrenfuller
Contributor

A join with type=left means show all values in the original search whether there is a match in the subsearch or not. (which is why i filled null just after the join)

A join without the type... would only show rows where they exist in both searches...so an error that has occurred in the past, but not in the subsearch timeframe would not show up.

0 Karma

lmzheng
Explorer

Thank you so much 🙂 I'll try this out

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