topic Re: How do you retrieve date from the following string using regex? in Splunk Search

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

ramesh12345 — Wed, 27 Feb 2019 10:33:15 GMT

Hi,

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

How do I write this in regex?

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

tiagofbmm — Wed, 27 Feb 2019 10:38:07 GMT

Use a rex first and to split the parts of it and then concatenate it to your new field

| makeresults | eval time="Test-20190212-0912" | rex field=time "Test-(?<year>\d{4})(?<month>\d{2})(?<day>\d{2})"
| eval time=year+"-"+month+"-"+day

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

vnravikumar — Wed, 27 Feb 2019 10:39:17 GMT

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

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

ramesh12345 — Tue, 29 Sep 2020 23:21:37 GMT

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?

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

tiagofbmm — Wed, 27 Feb 2019 10:52:43 GMT

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

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

ramesh12345 — Tue, 29 Sep 2020 23:21:42 GMT

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?

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

tiagofbmm — Wed, 27 Feb 2019 14:24:49 GMT

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

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

ramesh12345 — Tue, 29 Sep 2020 23:30:37 GMT

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

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

ramesh12345 — Tue, 29 Sep 2020 23:30:41 GMT

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

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

tiagofbmm — Wed, 27 Feb 2019 15:14:24 GMT

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

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

ramesh12345 — Thu, 28 Feb 2019 10:03:30 GMT

Yes the data is like that only.

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

ramesh12345 — Thu, 28 Feb 2019 10:40:02 GMT

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

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

tiagofbmm — Thu, 28 Feb 2019 10:42:56 GMT

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

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

ramesh12345 — Thu, 28 Feb 2019 11:26:33 GMT

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

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

tiagofbmm — Thu, 28 Feb 2019 11:43:00 GMT

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")

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

ramesh12345 — Thu, 28 Feb 2019 12:25:57 GMT

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

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

tiagofbmm — Tue, 29 Sep 2020 23:31:11 GMT

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

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

ramesh12345 — Thu, 28 Feb 2019 14:14:16 GMT

Could you please send me the final query.

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

tiagofbmm — Tue, 29 Sep 2020 23:31:24 GMT

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

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

tiagofbmm — Thu, 28 Feb 2019 14:39:54 GMT

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