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

[ramesh12345]

Hi,

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

How do I write this in regex?

[vnravikumar · Accepted solution]

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

[ramesh12345]

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?

[tiagofbmm]

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

[ramesh12345]

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?

[tiagofbmm]

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

[ramesh12345]

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

[ramesh12345]

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

[tiagofbmm]

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

[ramesh12345]

Yes the data is like that only.

[ramesh12345]

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

[tiagofbmm]

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

[ramesh12345]

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

[tiagofbmm]

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

[ramesh12345]

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

[tiagofbmm]

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

[ramesh12345]

Could you please send me the final query.

[tiagofbmm]

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

[tiagofbmm]

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

[ramesh12345]

Thank you Very much for your help.

[ramesh12345]

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