topic Re: Converting a field from a string to a number in Splunk Search

Converting a field from a string to a number

jason_hotchkiss — Thu, 15 Jul 2021 15:07:15 GMT

So, long story short...

I am trying to determine the event count by source, which host is producing the most events in that source, and who owns the host (custom_field).

Any suggestions on how to accomplish this would be helpful. Thank you.

This is what I have tried so far:

| tstats count as events where index=wineventlog sourcetype=* by _time host custom_field source
| search custom_field=unit1 OR custom_field=unit_2 OR custom_field=unit_3

Then I run a stats command to collect the event count, then list the event count by the custom_field

| stats
sum(events) as total_events
list(events) as event_counts
list(source) as source
list(host) as host
by custom_field

I understand that event_counts is now a string. However, I would like to be able to use these numbers to determine which source is producing the most events by each custom_field.

I have tried:

| convert num(event_counts)
| eval num_events = tonumber(event_counts)

But these don't work unless I use

| mvexpand event_counts

This then skews the results to where they don't make any sense. I want to convert the event_count field to a number so I can make a chart or a timechart from it as well to analyze the growth over time.

Thanks in advanced.

Re: Converting a field from a string to a number

kamlesh_vaghela — Thu, 15 Jul 2021 15:33:10 GMT

@jason_hotchkiss

Can you please share your requirement what you want to achieve from tstats , stats and then timechart commands? If you share more details then we can suggest optimised solution.

Re: Converting a field from a string to a number

jason_hotchkiss — Thu, 15 Jul 2021 16:13:40 GMT

From tstats I am trying to count events by source host custom_field _time

From stats I am trying to determine total events for each source and the host using that source.

From timechart I am trying to determine the total count of events for each host by custom_field

Re: Converting a field from a string to a number

kamlesh_vaghela — Thu, 15 Jul 2021 16:38:44 GMT

@jason_hotchkiss

After your initial search,

| tstats count as events where index=wineventlog sourcetype=* by _time host custom_field source | search custom_field=unit1 OR custom_field=unit_2 OR custom_field=unit_3

I would like you to try with eventstats command, using this search you will have sum of events by source and custom_field.

| eventstats sum(events) as event_counts by source,custom_field

You can change it as your requirement,

| eventstats sum(events) as event_counts by host,source

Another reason using eventstats, in case you want to use timechart then it requires _time files but after stats command you mentioned in question _time won't be there. with event stats you will have _time with you.

Then you can try

| chart sum(events) as event_counts over custom_field by host

| timechart sum(events) as event_counts by custom_field

From timechart I am trying to determine the total count of events for each host by custom_field

Timechart allow only single by clause so may be you can try above one

I hope this will help you. Just try it and let me know if you need any help.

Re: Converting a field from a string to a number

jason_hotchkiss — Tue, 20 Jul 2021 14:18:36 GMT

This has been most helpful. I was able to use your examples to create my dashboard. Thank you for your help.