Hi, I have this search query where i aggregate using the stats and sum by few fields...
When I run the query in splunk portal i see the data in the events tab but not in the stats tab. So I used the fillnull to see which fields are causing the problem. I noticed that these fields where i am using eval are causing the issue as i see 0 inside these columns after using fillnull
| eval status_codes_only=if( (status_code>=200 and status_code<300) or status_code>=400,1,0)
| search status_codes_only=1
| rex mode=sed field=ClintReqRcvdTime "s/: /:/"
| eval date_only=strftime(strptime(ClintReqRcvdTime, "%a %d %b %Y %H:%M:%S :%3N %Z"), "%m/%d/%Y")
| eval year_only=strftime(strptime(ClintReqRcvdTime, "%a %d %b %Y %H:%M:%S :%3N %Z"), "%Y")
| eval month_only=strftime(strptime(ClintReqRcvdTime, "%a %d %b %Y %H:%M:%S :%3N %Z"), "%b")
| eval week_only=floor(tonumber(strftime(strptime(ClintReqRcvdTime, "%a %d %b %Y %H:%M:%S :%3N %Z"), "%d"))/7+1)
| eval TwoXXonly=if(status_code>=200 and status_code <300,1,0)
| eval FourXXonly=if(status_code>=400 and status_code <500,1,0)
| eval FiveXXonly=if(status_code>=500 and status_code <600,1,0)
| fillnull date_only,year_only,month_only,week_only,organization,clientId,proxyBasePath,api_name,environment,Total_2xx,Total_4xx,Total_5xx
| stats sum(TwoXXonly) as Total_2xx,sum(FourXXonly) as Total_4xx,sum(FiveXXonly) as Total_5xx by date_only,year_only,month_only,week_only,organization,clientId,proxyBasePath,api_name,environment
| table date_only,year_only,month_only,week_only,organization,clientId,proxyBasePath,api_name,environment,Total_2xx,Total_4xx,Total_5xx
when i look at the field that i used to get the date_only, year_only, week_only column - i see data something like this in the events
Wed 11 Jun 2025 22:57:34:396 EDT
Wed 11 Jun 2025 22:56:43:254 EDT
Wed 11 Jun 2025 22:56:34:466 EDT
Wed 11 Jun 2025 22:56:28:404 EDT
I put a \s before and after the : because your example showed the space before, but your sed was replacing a space after.
Put the \s* where the space can be.
If you want to post examples, use the code tag option in the editor </> so you can see exactly what you are posting.
Like this...
sometimes in this field i get an extra space (bold) so we had to add this line and an extra space in the calculated field also - i have to take 2 scenarios and for that we added this line
| rex mode=sed field=ClintReqRcvdTime "s/: /:/"
Wed 4 Jun 2025 17:16:02 :161 EDT
Mon 2 Jun 2025 02:52:50 :298 EDT
Mon 9 Jun 2025 16:11:05 :860 EDT
Tue 10 Jun 2025 14:32:26:243 EDT
Wed 11 Jun 2025 13:10:32:515 EDT
Wed 11 Jun 2025 17:37:10:008 EDT
in the calc field when i use the format - do i have to specify the space for :161 like
eval date_only=strftime(strptime(ClintReqRcvdTime, "%a %d %b %Y %H:%M:%S :%3N %Z"), "%m/%d/%Y")
Your sed command is wrong for your example data. The space is before the colon in your example. but your sed is replacing the space after the colon.
Your example data as posted seems to have two spaces before the colon, at least if I copy/paste your data there are two spaces.
Note also that you could do the fixup and strptime once, e.g.
| makeresults format=csv data="ClintReqRcvdTime
Wed 4 Jun 2025 17:16:02 :161 EDT
Mon 2 Jun 2025 02:52:50 :298 EDT
Mon 9 Jun 2025 16:11:05 :860 EDT"
``` This is what you want - above is just constructing an example dataset ```
| eval t=strptime(replace(ClintReqRcvdTime, "\s*:\s*", ":"), "%a %d %b %Y %H:%M:%S:%Q %Z")
| eval date_only=strftime(t, "%m/%d/%Y")
| eval year_only=strftime(t, "%Y")
| eval month_only=strftime(t, "%b")
| eval week_only=floor(tonumber(strftime(t, "%d"))/7+1)
as an example - there is only 1 space - it might be copy paste error that has 2 spaces - but it is only 1 space in it. Its only some times i get this extra space
this is how i get the values in it
Wed 4 Jun 2025 17:16:02 :161 EDT - sometimes extra space
Wed 4 Jun 2025 17:16:02 :161 EDT - sometimes extra No extra space
why do we have 2 \s*: and \s* - i think we just need 1 \s*:
t=strptime(replace(ClintReqRcvdTime, "\s*:\s*", ":"), "%a %d %b %Y %H:%M:%S:%Q %Z")
I put a \s before and after the : because your example showed the space before, but your sed was replacing a space after.
Put the \s* where the space can be.
If you want to post examples, use the code tag option in the editor </> so you can see exactly what you are posting.
Like this...
The strptime function will return null when the format string does not match the value in the field. Other than meta-characters ('%a', etc.) the format string must match *exactly*, including spaces. That means including spaces in the format string if they are expected in the data.
That said, the sed command should be removing the extra spaces so no accommodation in strptime should be needed.
Thank you Rich, If i just remove the extra space that is in the strp function i should be ok
eval date_only=strftime(strptime(ClintReqRcvdTime, "%a %d %b %Y %H:%M:%S :%3N %Z"), "%m/%d/%Y")
let me test this - thanks alot