Manage application log data-time series based approach(centralized log reporting)

(Ram Duraisamy) #1

Use case is, I'm managing devops for a company, Most of the time team is spending more time to analysis the log data. Since our application is running across multiple(aprox. more than 20 box) prod servers. Further analysis we thought to try out a POC with Neo4j using the approach of time series pattern after gone through the blog (https://www.graphgrid.com/modeling-time-series-data-with-neo4j/)

I have also created sandbox sample data available here (https://10-0-1-221-39778.neo4jsandbox.com/browser/)

After generated meta and sample data when try to fetch the below query no data found result.
As I'm new to cypher query; Can you one please help me to move forward?
Below is my fetch query

//Fetching log data after inserted tring to fetch log data but getting no record found, I
match (y:Year {value:2019})
match (y)-[:CONTAINS]->(m:Month {value:3 })
match (m)-[:CONTAINS]->(d:Day) where d.value>=19
match (d)-[:CONTAINS]->(h:Hour)
match (h)-[:CONTAINS]->(mm:Minute)
match (i:AppLog)<-[:REQ_ON]-(mm)
return y, m, d,h, mm, count(i)


//Create Time Tree with Day Depth
WITH range(2010, 2019) AS years, range(1,12) AS months
FOREACH(year IN years |
  CREATE (y:Year {value: year})
  FOREACH(month IN months |
    CREATE (m:Month {value: month})
    MERGE (y)-[:CONTAINS]->(m)
    FOREACH(day IN (CASE
                      WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31)
                      WHEN month = 2 THEN
                        CASE
                          WHEN year % 4 <> 0 THEN range(1,28)
                          WHEN year % 100 = 0 AND year % 400 = 0 THEN range(1,29)
                          ELSE range(1,28)
                        END
                      ELSE range(1,30)
                    END) |
      CREATE (d:Day {value: day})
      MERGE (m)-[:CONTAINS]->(d))))


//Connect Years Sequentially
MATCH (year:Year)
WITH year
ORDER BY year.value
WITH collect(year) AS years
FOREACH(i in RANGE(0, length(years)-2) |
    FOREACH(year1 in [years[i]] |
        FOREACH(year2 in [years[i+1]] |
            CREATE UNIQUE (year1)-[:NEXT]->(year2))));

//Connect Months Sequentially
MATCH (year:Year)-[:CONTAINS]->(month)
WITH year, month
ORDER BY year.value, month.value
WITH collect(month) AS months
FOREACH(i in RANGE(0, length(months)-2) |
    FOREACH(month1 in [months[i]] |
        FOREACH(month2 in [months[i+1]] |
            CREATE UNIQUE (month1)-[:NEXT]->(month2))));

//Connect Days Sequentially
MATCH (year:Year)-[:CONTAINS]->(month)-[:CONTAINS]->(day)
WITH year, month, day
ORDER BY year.value, month.value, day.value
WITH collect(day) AS days
FOREACH(i in RANGE(0, length(days)-2) |
    FOREACH(day1 in [days[i]] |
        FOREACH(day2 in [days[i+1]] |
            CREATE UNIQUE (day1)-[:NEXT]->(day2))));			

//meta data 
Create (:Env {name:'dev', shortName:'d'})
Create (:Env {name:'qa', shortName:'q' })
Create (:Env {name:'uat', shortName:'t'})
Create (:Env {name:'prod', shortName:'p' })

Create (:LogLevel {name:'DEBUG'})
Create (:LogLevel {name:'INFO'})
Create (:LogLevel {name:'WARN'})
Create (:LogLevel {name:'ERROR'})
Create (:LogLevel {name:'FATAL'})

Create (:ServerType {name:'cm', shortName:'m'})
Create (:ServerType {name:'cd', shortName:'d'})

//Brand map
Create (:Brand {name:'dewalt', shortName:'dw', tag:'dewalt.com'})
//Log Path
Create (:LogPath{name:'sbdinc-dewalt-na', server:'tow-p-ecmsd01', brand:'dw' })

//sample data
WITH [
{f0:"15136 09:18:50 WARN  HybrisRecepientsList is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20180318.091709.txt"}
,{f0:"15136 09:20:50 WARN  HybrisFromAddress is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20180319.091709.txt"}
,{f0:"15136 10:18:50 WARN  HybrisSenderName is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20170319.091709.txt"}
,{f0:"15136 09:18:50 WARN  HybrisSuccessSubject is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190319.091709.txt"}
,{f0:"15136 09:18:50 WARN  HybrisUnSuccessSubject is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190320.091709.txt"}
,{f0:"15136 09:18:50 WARN  HybrisSuccessEmailTemplate is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"15136 09:18:50 WARN  HybrisUnsuccessEmailTemplate is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"15136 09:20:50 WARN  HybrisEmailNotificationSettings is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"15136 09:18:50 WARN  HybrisEmailNotificationSettingsEANZ is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"15136 09:18:50 WARN  HybrisJobErrorsReportFileNamePref is not specified in the AppSettings.config file",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:15 INFO  Starting products syncronization scheduled task. HybrisSettingId=157B6814-70B1-4A9D-A5CC-BADAB0E11D3C, HybrisLanguageHeader=en",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:15 WARN  HybrisAPIHelper : fieldName 'Skip Artifacts in Product Sync' has empty value",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:15 INFO  START SynchronizeArtifacts Pipeline",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:15 INFO  PLACEHOLDER ReadHybrisManufacturers Pipeline",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:16 INFO  START ReadHybrisClassifications Pipeline",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:16 WARN  HybrisAPIHelper : fieldName 'Is EANZ Sync' has empty value",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:16 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_DEWALT",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0160",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0437",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0232",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0474",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0091",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0220",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0139",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0009",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:17 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0274",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:18 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0464",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:18 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0209",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:18 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0328",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:18 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0040",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:18 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0007",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:18 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0273",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:18 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0010",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0398",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0239",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0329",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0422",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0053",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0019",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0352",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:19 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0231",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:20 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0387",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:20 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0095",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:20 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0084",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:27:20 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0312",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:30:20 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0161",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:31:20 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0484",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
,{f0:"ManagedPoolThread #7 09:32:21 INFO  HybrisAPIHelper Pipeline : HybrisApiRequestUrl : https://toolnet.sbdinc.com/sbdws410/rest/catalogs/sbdProductNADeWALTCatalog/catalogversions/Online/categories/DW_0233",path:"\\\\tow-p-ecmsm01\\e$\\inetpub\\wwwroot\\sbdinc-dewalt-na\\_etc\\_sitecore\\data\\logs\\commerceconnectpipeline.log.20190318.091709.txt"}
] as data
 UNWIND (data) as row  
 With row, split(row.path, '\\_etc\\_sitecore\\data\\logs\\') as etcPart, split(row.f0, ' ') as msgPart
 with msgPart, row,  etcPart, split(etcPart[0], '\\') as brandSplit, split(etcPart[1], '.') as fileSplited,  case when msgPart[1] starts with  '#' Then true  else false end as hasHash 
 with msgPart, row,hasHash, brandSplit ,fileSplited, split(brandSplit[2], '-') as serverPart, case when hasHash then 1 else 0 end as baseIdx
 with msgPart, row, hasHash, brandSplit, serverPart, baseIdx, msgPart[baseIdx] as thread, msgPart[baseIdx+1] as time, msgPart[baseIdx+2] as loglevel,
 case when hasHash = true then length(msgPart[0])+1 else 0 end + length(msgPart[ baseIdx])+1+ length(msgPart[baseIdx+1])+1+6 as msgLen,
 brandSplit[length(brandSplit)-1] as brand, case when length(fileSplited)>=4 then  fileSplited[length(fileSplited)-3] else fileSplited[length(fileSplited)-2] end   as fileDatePart    
 with row, serverPart, thread, time, loglevel, fileDatePart, substring(row.f0, msgLen) as msg, brandSplit, brand
match (y:Year {value:toInt(left(fileDatePart, 4))})
match (y)-[:CONTAINS]->(m:Month {value:toInt(substring(fileDatePart, 4,2))})
match (m)-[:CONTAINS]->(d:Day {value:toInt(right(fileDatePart, 2))})
match (env:Env {shortName:serverPart[1]})
match(lp:LogPath {name:brand, server:brandSplit[2]}) 
match (b:Brand {shortName:lp.brand}) 
match (ll:LogLevel {name:loglevel})
match (st:ServerType {shortName:substring(serverPart[2], 4,1)})
match (s:Server {name:brandSplit[2]})
with d, s, b, env, msg, serverPart, time, brand, fileDatePart, thread, ll  
merge (d)-[:CONTAINS]->(h:Hour {value:toInt(left(time ,2))})
merge (h)-[:CONTAINS]->(min:Minute {value:toInt(substring(time , 3,2))})
CREATE (l:AppLog {seconds:toInt(right(time , 2)), thread:thread,  message:msg})
CREATE(l)-[:REQ_ON]->(min)
CREATE(l)-[:MAP_BRAND]->(b)
CREATE(l)-[:MAP_SERVER]->(s)
CREATE(l)-[:MAP_ENV]->(env)
CREATE(l)-[:MAP_LOGLEVEL]->(ll)


//Fetching log data after inserted tring to fetch log data but getting no record found, I
match (y:Year {value:2019})
match (y)-[:CONTAINS]->(m:Month {value:3 })
match (m)-[:CONTAINS]->(d:Day) where d.value>=19
match (d)-[:CONTAINS]->(h:Hour)
match (h)-[:CONTAINS]->(mm:Minute)
match (i:AppLog)<-[:REQ_ON]-(mm)
return y, m, d,h, mm, count(i)

//Our final query 
with datetime()-duration ({hours:4 })-duration({minutes:30})  as cdt
MATCH (y:Year) where y.value>= cdt.year  WITH y,cdt
MATCH (y)-[:CONTAINS]->(m:Month) where m.value>=cdt.month  WITH y, m, cdt
MATCH (m)-[:CONTAINS]->(d:Day) where d.value>= cdt.day  WITH y, m, d, cdt
MATCH (d)-[:CONTAINS]->(h:Hour ) where h.value>= cdt.hour  WITH y, m, d,h, cdt
MATCH (h)-[:CONTAINS]->(mm:Minute ) where mm.value >= cdt.minute    WITH y, m, d,h, cdt
MATCH p=(mm)-[:CONTAINS*]->()<-[:REQ_ON]-(i:AppLog ),(i)-[:MAP_LOGLEVEL]->(ll), (i)-[:MAP_ENV]->(env), (i)-[:MAP_BRAND]->(b), (i)-[:MAP_SERVER]->(s) 
where s.name contains 'ecmsm' and i.message contains 'END ReadHybrisProductClassifications'
return  y.value as Year, m.value as Month,d.value as day,h.value as Hour,i.seconds as Second , ll.name as logLevel,  env.name as Env ,s.name as serverName, b.name as Brand,  i.message  limit 200

0 Likes