Splunk Search

Extract a specific field values from a table in Splunk

asing13
Explorer

Dear All,

My question might seem naive and pardon me for that.
I want to create an alert for data not being processed. The below was my query. An alert would be triggered, if the number of event are greater than 5000 or if the number of events are greater than 1000 and the change in events are more than 1000 between 2 hours. Used delta to get the difference. I am able to give the conditions and I am able to alert as well. 

However, I wanted to check if we could extract a specific column from a specific row and column number and create another table. For example, please look at below table.

asing13_0-1606768905293.png

I want to create a new table with number of event at 15:00:00 , number of event at 14:00:00 ,  Change in number of events.

 

Kindly let me know how to achieve the same.

Labels (3)
0 Karma

Richfez
SplunkTrust
SplunkTrust

Yes, streamstats.

earliest=-2h*h latest=now() index=_internal blocked=true name IN (<that list>)
| bin span=1h _time
| stats count() AS MyEventCount by _time
| streamstats window=2 earliest(MyEventCount) AS previous_count, latest(MyEventCount) AS latest_count
| eval difference = previous_count - latest_count

I did change it from a `| timechart ...` to a `| bin _time | stats count...` but both accomplish the same goal, so you can really use either.

Streamstats in this case chops up that data into 2-event chunks, taking the earlier myEventCount as the previous count, the later one as latest_count.  The last we just do an eval to create a new field difference with the ... difference.  🙂

Also 100% guarantee I did my math backwards there.  I get 'em wrong so often, I no longer try, I just warn folks to switch that subtraction around if necessary.

 

asing13
Explorer

Dear @Richfez ,

First of all, I would like to thank you for the quick response !

I tried the above mentioned query and this came as the output :

asing13_0-1606815087375.png

Seems to be working perfectly but one issue which I noticed is the time stamp. It gives time 2 hours back.

 

I tried to create a query, which is not how I should have done it but it seems to give me the required output: 

earliest=-1h@h latest=now index=_internal blocked=true name IN (parsingqueue,typingqueue,aggqueue,indexqueue,auditqueue,exec)
| stats count
| append
[ search earliest=-2h@h latest=-1h@h index=_internal blocked=true name IN (parsingqueue,typingqueue,aggqueue,indexqueue,auditqueue,exec)
| stats count]
| append
[| makeresults
| eval count=relative_time(now(), "@h")
| convert timeformat="%m\%d\%y %l:%M.%S%p" ctime(count)
| fields - _time]
| transpose header_field=a
| fields - column
| rename "row 1" as "event1","row 2" as "event2", "row 3" as Time
| eval Event_This_Hour=(tonumber(event1))
| eval Event_Last_Hour=(tonumber(event2))
| eval change=Event_This_Hour-Event_Last_Hour
| rename change as "Change_In_Number_Of_Events"
| table Time,Event_This_Hour,Event_Last_Hour,"Change_In_Number_Of_Events"

If it is not much of a pain to you, I would like to request you to take a look at this and provide your valuable suggestions:

asing13_2-1606815311297.png

Once again, thanks alot @Richfez  !!!!

 

Regards,
Abhishek Singh

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!