Splunk Search

How do you retrieve date from the following string using regex?

ramesh12345
Explorer

Hi,

Test-20190212-0912 from this string. I want to retrieve date like this 2019-02-12

How do I write this in regex?
alt text

Tags (2)
0 Karma
1 Solution

vnravikumar
Champion

Hi @ramesh12345

Try this

| makeresults 
| eval str="Test-20190212-0912" 
| rex field=str "-(?P<year>\d{4})(?P<month>\d{2})(?P<date>\d{2})" 
| eval result= year."-".month."-".date

View solution in original post

0 Karma

vnravikumar
Champion

Hi @ramesh12345

Try this

| makeresults 
| eval str="Test-20190212-0912" 
| rex field=str "-(?P<year>\d{4})(?P<month>\d{2})(?P<date>\d{2})" 
| eval result= year."-".month."-".date
0 Karma

ramesh12345
Explorer

i have other small problem

index="os" sourcetype="Service" status=* (Group="Data/Config" OR Group="Secure") AND (Section="Site Problem" OR Section="Local health") AND (Component="connectivity" OR Component="health")|dedup _time,CaseNumber|where Created_ON=Updated_ON| eval days = (Now() - _time) /86400| eval days_ago = case(days 60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")| chart count by days_ago,Group|sort days_ago

most cases have both (Group="Data/Config" OR Group="Secure").
initially (Group is "Data/Config") then after some time it change to (Group="Secure").when i count by group,it is showing initial Group name for that case.it is not showing to current group.

i want to display count by group with latest group name(i.e case that have latest group that comes under particular group not previous group)

How to do this?

0 Karma

tiagofbmm
Influencer

Just transform the old group to the new one and keep doing what you were doing

| eval Group=if(Group="Data/Config", "Secure", Group)

Don't forget to accept answers and upvote comments that actually helped you solve your problems

0 Karma

ramesh12345
Explorer

index="os" sourcetype="Service" status=* (Group="Data/Config" OR Group="Secure") AND (Section="Site Problem" OR Section="Local health") AND (Component="connectivity" OR Component="health")|dedup _time,CaseNumber|where Created_ON=Updated_ON|eval Group=if(Group="Data/Config", "Secure", Group)|eval days = (Now() - _time) /86400| eval days_ago = case(days 60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")| chart count by days_ago,Group|sort days_ago

when i add your line to my query it is not displaying any data

i am finding the created case with the help of Created_ON=Updated_ON.at that time Group name is one,after that it is changing.so when i execute the query it is searching the condition Created_ON=Updated_ON at that time which group is there,it is displaying that group.

so i need to find the data with in particular date created cases with present Group.

How to do this?

0 Karma

tiagofbmm
Influencer

eval days_ago = case(days 60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")

There is a comparison missing on the first clause. Can you confirm first that the result of

index="os" sourcetype="Service" status=* (Group="Data/Config" OR Group="Secure") AND (Section="Site Problem" OR Section="Local health") AND (Component="connectivity" OR Component="health") 
| dedup _time,CaseNumber 
| where Created_ON=Updated_ON 
| eval NewGroup=if(Group="Data/Config", "Secure", Group) 
| eval days = (Now() - _time) /86400 
| eval days_ago = case(days 60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day") 

Has the NewGroup lilke you expect it to be?

And only then apply | chart count by days_ago, NewGroup|sort days_ago

0 Karma

ramesh12345
Explorer

For NewGroup also it is displaying same two Groups,when i add this | chart count by days_ago, NewGroup|sort days_ago to the query it is not displaying any data

0 Karma

ramesh12345
Explorer

From my point of you Created_ON=Updated_ON is the problem,because this statement is used to find the created cases count.when it is checking the condition that time Group it is displaying.

Initially
Ex:Created_ON 2019-02-27 12:00:00
Updated_ON 2019-02-27 12:00:00
Group:Data/Config

Next Updated_ON:2019-02-27 14:30:00
Group:Secure
But it is displaying First Group only.i want current group

0 Karma

tiagofbmm
Influencer

Tell me if this is what your data looks like:

Created, Updated, CaseNumber, Group
2019-02-27 12:00:00, 2019-02-27 12:00:00, 1, Data/Config
2019-02-27 14:30:00 , :2019-02-27 14:30:00 , 2, Secure

If this is your format, since you are deduping the Case_Number, I don't see how can you get, for the same CaseNumber, a change in the Group...

0 Karma

ramesh12345
Explorer

Yes the data is like that only.

0 Karma

ramesh12345
Explorer

Can you please send me the final query.i just deduped the _time.removed CaseNumber as well.but it is showing only one group.

0 Karma

tiagofbmm
Influencer

Final query?

Is that it?

index="os" sourcetype="Service" status=* (Group="Data/Config" OR Group="Secure") AND (Section="Site Problem" OR Section="Local health") AND (Component="connectivity" OR Component="health")
| dedup _time
| where Created_ON=Updated_ON
| eval NewGroup=if(Group="Data/Config", "Secure", Group)
| eval days = (Now() - _time) /86400
| eval days_ago = case(days>60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")
| chart count by days_ago,NewGroup
|sort days_ago

0 Karma

ramesh12345
Explorer

Still it is displaying same One group.not displaying with latest Group.

0 Karma

tiagofbmm
Influencer

Run this search, it's a dummy one, you can run it everywhere, and tell me if this is the scenario you are seeing before chart:

| makeresults
| eval Created_ON="2019-02-27 12:00:00", Updated_ON="2019-02-27 12:00:00", CaseNumber=1, Group="Data/Config", Section="Site Problem",Component="health"
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=2, Group="Data/Config" , Section="Site Problem",Component="health" ]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=3, Group="Data/Config", Section="Site Problem",Component="health"]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=4, Group="Data/Config", Section="Site Problem",Component="health"]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=2, Group="Secure", Section="Site Problem",Component="health"]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=6, Group="Secure", Section="Site Problem",Component="health"]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=4, Group="Secure", Section="Site Problem",Component="health"]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=77, Group="Secure", Section="Site Problem",Component="health"]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=22, Group="Secure", Section="Site Problem",Component="health"]
| where Created_ON=Updated_ON
| eval NewGroup=if(Group="Data/Config", "Secure", Group)
| eval days = (Now() - _time) /86400
| eval days_ago = case(days>60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")

0 Karma

ramesh12345
Explorer

Exactly i want to display the latest group as i added image you can see.

If it i s one group one color other one other color

0 Karma

tiagofbmm
Influencer

I don't see the point of | where Created_ON=Updated_ON. If they are tickets, this is probably true only at creation time, so I took it off on the search. This will give you the latest value of Group per CaseNumber. You mentioned it starts as Data/Config but then changed to Secure, so while a case number has only Data/Config, it will remain like that. If there is both Data/COnfig and later Secure for a case number, then it will keep Secure.

| makeresults
| eval Created_ON="2019-02-27 12:00:00", Updated_ON="2019-02-27 12:00:00", CaseNumber=1, Group="Data/Config", Section="Site Problem",Component="health", _time=Updated_ON
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=2, Group="Data/Config" , Section="Site Problem",Component="health", _time=Updated_ON ]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=3, Group="Data/Config", Section="Site Problem",Component="health", _time=Updated_ON]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=4, Group="Data/Config", Section="Site Problem",Component="health", _time=Updated_ON]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 15:30:00", CaseNumber=2, Group="Secure", Section="Site Problem",Component="health", _time=Updated_ON]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=6, Group="Secure", Section="Site Problem",Component="health", _time=Updated_ON]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=4, Group="Secure", Section="Site Problem",Component="health", _time=Updated_ON]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=77, Group="Secure", Section="Site Problem",Component="health", _time=Updated_ON]
| append
[| makeresults
| eval Created_ON="2019-02-27 14:30:00", Updated_ON="2019-02-27 14:30:00", CaseNumber=22, Group="Secure", Section="Site Problem",Component="health", _time=Updated_ON]
| eval NewGroup=if(Group="Data/Config", "Secure", Group)
| eval days = (now() - strptime(_time,"%Y-%m-%d %H:%M:%S"))/86400
| eval days_ago = case(days>60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")
| eventstats last(Group) as LatestGroup by CaseNumber
| chart count by days_ago,LatestGroup
| sort days_ago

0 Karma

ramesh12345
Explorer

Could you please send me the final query.

0 Karma

tiagofbmm
Influencer

index="os" sourcetype="Service" status=* (Group="Data/Config" OR Group="Secure") AND (Section="Site Problem" OR Section="Local health") AND (Component="connectivity" OR Component="health")
| eval NewGroup=if(Group="Data/Config", "Secure", Group) | eval days = (now() - strptime(_time,"%Y-%m-%d %H:%M:%S"))/86400 | eval days_ago = case(days>60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day") | eventstats last(Group) as LatestGroup by CaseNumber | chart count by days_ago,LatestGroup | sort days_ago

0 Karma

tiagofbmm
Influencer

@ramesh12345 don't forget to upvote and accept the answer. This thread has been long and other questions arose. Upvote the useful comments please

0 Karma

ramesh12345
Explorer

Thank you Very much for your help.

0 Karma

ramesh12345
Explorer

for less than one day it is displaying other date created data also.means it is displaying the with the Updated_ON field.If Created_ON=2019-02-26 Updated_ON=2019-02-26 today.but is displaying that CaseNumber as well.

Note:Group is changed 2019-02-26 this date only

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