Hi,
I am reposting this question because when I posted first time i didnt use the code button (101 010). sorry for the inconvenience
I have a query which is giving the output from multiple sources. I am using stats values() but the output is not coming how I expected.
I am getting output like below, Projectname and pid are same but they were different records with different time periods and different K_Satuts, Reason status, IIS Status etc., but all values of Status are showing in one cell like below but I want each Status in separate row based on time period.
starttime endtime ProjectName Pid sample K_Status
Mar 23, 2017 10:41:13 Mar 23, 2017 10:41:16 A 1 X a2345fvwwe3
xy2234vfs3344
kjsdhe23434jhj
I am expecting my output like below. though it is same project and pid but it started at different time periods and having different K_status.
starttime endtime ProjectName pid sample K_Status
Mar 23, 2017 10:41:13 Mar 23, 2017 10:41:16 A 1 X a2345fvwwe3
Mar 23, 2017 10:41:15 Mar 23, 2017 10:41:21 A 1 X xy2234vfs3344
Mar 23, 2017 10:45:15 Mar 23, 2017 10:47:21 A 1 X kjsdhe23434jhj
I am using the below query. Can anyone help me how to do this
my Query:
(index=iis sourcetype=iis host=A cs_method="GET") OR
(index=th sourcetype=throt host=A) OR
(index=iis sourcetype=iis host=xyz cs_method=GET cs_uri_stem="mriweb.dll" OR cs_uri_stem="auth_fail") OR
(index=ibm sourcetype=ivw host=abc respondentID!=1 )
| rex field=respID ".._(?.)"
| eval ProjectName=coalesce(ProjectName, project_name)
| eval ProjectName= upper(ProjectName)
| eval pid=coalesce(pid, pid1)
| search ProjectName= AND pid=*
| eval "ThIIS Status"=if((searchmatch("index=iis sourcetype=iis host=A cs_method=\"GET\"")), Description, null())
| eval "Thr Status"=if(searchmatch("index=th sourcetype=throt host=A"), T_status, null())
| eval "K_Status"=if(searchmatch("index=th sourcetype=throt host=A"), kid, null())
| eval "Reason Status"=if(searchmatch("index=iis sourcetype=iis host=xyz cs_method=GET"), Reason, null())
| eval BT=if(isnull(kid), "B_Throt", Description)
|eval IIS_S=if(BT="B_Throt",sc_status+"-"+"B_Throt",Description)
| eval "IIS Status"=if(searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), IIS_S, null())
| eval "S Status"=if(searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), s, null())
| eval "IVW Status"=if(searchmatch("index=ibm sourcetype=ivw host=ABC"), Queue, null())
| stats min(_time) AS startTime max(_time) AS endTime values(Status) AS Status by ProjectName pid
| eval startTime=strftime(startTime,"%b %d, %Y %T")
| eval endTime=strftime(endTime,"%b %d, %Y %T")
| sort _time
| table startTime endTime ProjectName pid "S Status" K_Status "ThIIS Status" "Thr Status" "IIS Status" "IVW Status" "Reason Status"
Thanks in advance
hi,
can any one help me to do my request. I tried different methods but no luck.
Your help in this matter would be greatly appreciated
Thanks
Give this a try
(index=iis sourcetype=iis host=A cs_method="GET") OR
(index=th sourcetype=throt host=A) OR
(index=iis sourcetype=iis host=xyz cs_method=GET cs_uri_stem="mriweb.dll" OR cs_uri_stem="auth_fail") OR
(index=ibm sourcetype=ivw host=abc respondentID!=1 )
| rex field=respID ".._(?.)"
| eval ProjectName=upper(coalesce(ProjectName, project_name))
| eval pid=coalesce(pid, pid1)
| search ProjectName= AND pid=*
| eval BT=if(isnull(kid), "B_Throt", Description)
|eval IIS_S=if(BT="B_Throt",sc_status+"-"+"B_Throt",Description)
| eval Status=case((searchmatch("index=iis sourcetype=iis host=A cs_method=\"GET\"")), Description,
searchmatch("index=th sourcetype=throt host=A"), T_status,
searchmatch("index=th sourcetype=throt host=A"), kid,
searchmatch("index=iis sourcetype=iis host=xyz cs_method=GET"), Reason,
searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), IIS_S,
searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), s,
searchmatch("index=ibm sourcetype=ivw host=ABC"), Queue, 1=1, null())
| stats min(_time) AS startTime max(_time) AS endTime values(Status) AS Status by ProjectName pid Status
| eval startTime=strftime(startTime,"%b %d, %Y %T")
| eval endTime=strftime(endTime,"%b %d, %Y %T")
| table startTime endTime ProjectName pid Status
Thank you so much for your reply.
its working but not as I expected. when I used your query all statuses of each pid is showing in same column but i want each status in separate column as below.
startTime endTime Proj pid Sam W_1 Kid T_Status I_Status V_Status
03/31/2017 4:23:36 AM 03/31/2017 4:25:36 AM A 1 S1 1dc a3fd Success Yes complete
03/31/2017 4:30:36 AM 03/31/2017 4:40:00 AM A 1 M1 1dc a3fd Success No complete
03/31/2017 5:23:36 PM 03/31/2017 6:00:00 PM A 1 V1 1dc a3fd Success Yes Quit
03/31/2017 4:24:36 AM 03/31/2017 4:25:00 AM B 2 S3 1dc a3fd Success Yes complete
03/31/2017 5:23:36 PM 03/31/2017 6:00:00 PM B 1 M1 1dc a3fd Success Yes timeout
Thanks
hi,
any help on this
Thanks
please tell me whether it is possible or not
Thanks
See if this works out for you
(index=iis sourcetype=iis host=A cs_method="GET") OR
(index=th sourcetype=throt host=A) OR
(index=iis sourcetype=iis host=xyz cs_method=GET cs_uri_stem="mriweb.dll" OR cs_uri_stem="auth_fail") OR
(index=ibm sourcetype=ivw host=abc respondentID!=1 )
| rex field=respID ".._(?.)"
| eval ProjectName=coalesce(ProjectName, project_name)
| eval ProjectName= upper(ProjectName)
| eval pid=coalesce(pid, pid1)
| search ProjectName= AND pid=*
| eval "ThIIS Status"=if((searchmatch("index=iis sourcetype=iis host=A cs_method=\"GET\"")), Description, null())
| eval "Thr Status"=if(searchmatch("index=th sourcetype=throt host=A"), T_status, null())
| eval "K_Status"=if(searchmatch("index=th sourcetype=throt host=A"), kid, null())
| eval "Reason Status"=if(searchmatch("index=iis sourcetype=iis host=xyz cs_method=GET"), Reason, null())
| eval BT=if(isnull(kid), "B_Throt", Description)
|eval IIS_S=if(BT="B_Throt",sc_status+"-"+"B_Throt",Description)
| eval "IIS Status"=if(searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), IIS_S, null())
| eval "S Status"=if(searchmatch("index=iis sourcetype=iis host=A cs_method=GET"), s, null())
| eval "IVW Status"=if(searchmatch("index=ibm sourcetype=ivw host=ABC"), Queue, null())
| eval Status=coalesce('ThIIS Status','Thr Status','K_Status', 'Reason Status', 'IIS Status', 'S Status', 'IVW Status')
| stats min(_time) AS startTime max(_time) AS endTime values(*Status) AS *Status by ProjectName pid Status
| eval startTime=strftime(startTime,"%b %d, %Y %T")
| eval endTime=strftime(endTime,"%b %d, %Y %T")
| sort _time
| table startTime endTime ProjectName pid "S Status" K_Status "ThIIS Status" "Thr Status" "IIS Status" "IVW Status" "Reason Status"
No luck, this is giving all statuses in one column, but I want each status in separate column
Thank you