Missing tables

[Manni_SM]

Hi @rubenmarin  @marcus_sommer 

All of sudden only Session related Tables were missing in Datamodel. No code change was there in operational monitor app.  below is the code

SUB sessionSummary

TempTable_test:

NoConcatenate 

load * ,

Text(SubField(UserId,'\',-1)) as UserId_Key,

Text(SubField(UserId,'\',-1)) as UserId_Key_entitlement

Resident 

LogContent where ObjectId='08406b9d-58d2-4997-b8b7-e5927cabc186';

Drop Table LogContent;

 rename table TempTable_test to LogContent;

  session_count:

  Load count(_proxySessionPackage) as count resident LogContent

  Where [Session Count]=1;

  LET session_count = Peek('count');

  Drop table session_count;

  IF session_count > 0 Then

    TRACE Working on Session Summary for $(session_count) session entries.;

    SessionSummary:

    NoConcatenate Load

      _proxySessionPackage, // LINK

      LogEntryPeriodStart as [Session Start],

      LogTimeStamp as [Session Finish],

      Hostname as [Session Hostname],

      [App Name] as [Session App Name],

      [Session Duration],

      [Session Count]

    RESIDENT LogContent

    WHERE [Session Count] = 1 ;

  // Add Session_Engine fields for calculation of "Cost of a Session"

    Session_Cost:

    NoConcatenate Load

      _proxySessionPackage, // Key to link the session

      [Session Selections],

      [Session CPU Spent (ms)],

      [Session KBytes Sent+Received]

    Resident LogContent

    Where [Session CPU Spent (ms)] >=0;

Rename Table LogContent to LogContent_Prep;

Left JOin(LogContent_Prep)

Load  

_proxySessionPackage,

'Keep' as Records_to_Keep

Resident SessionSummary;

LogContent:

NoConcatenate

Load

*

Resident LogContent_Prep

Where Records_to_Keep = 'Keep';

Drop Table LogContent_Prep;

ActiveuserGroupBucket:

LOAD Distinct

Text(SubField(UserId,'\',-1)) as UserId_Key,

  If(Count([_proxySessionPackage])<=2

    ,'Infrequent',

    If(Count([_proxySessionPackage])<=5

    ,'Regular',

     If(Count([_proxySessionPackage])<=10

      ,'Enthusiast',

       If(Count([_proxySessionPackage])<=20,

       'Motivated','HiglyMotivated'))))  as ActiveuserGroupBucket

Resident LogContent

Where floor(num(LogEntryPeriodStart))>=Floor(Today()-30)

group by UserId_Key;

//Sessions in last 60 days   suresh added 57 -129 lines 

User_Session_60Days_Prep:

Load distinct

  UserId_Key,

LogEntryPeriodStart,

[_proxySessionPackage]

Resident LogContent

Where num(LogEntryPeriodStart)>num(today()-60); //Formatting verification to make sure that condition works

User_Session_60Days:

Load

UserId_Key,

    count([_proxySessionPackage]) as Bucket_60

Resident User_Session_60Days_Prep

Group by UserId_Key;

Drop Table User_Session_60Days_Prep;

//Sessions in last 90 days

User_Session_90Days_Prep:

Load Distinct

UserId_Key,

LogEntryPeriodStart,

[_proxySessionPackage]

Resident LogContent

Where num(LogEntryPeriodStart)>num(today()-90); //Formatting verification to make sure that condition works

User_Session_90Days:

Load

 UserId_Key,

Count([_proxySessionPackage]) as Bucket_90

Resident User_Session_90Days_Prep

Group by UserId_Key;

Drop Table User_Session_90Days_Prep;

//Sessions in last 180 days

User_Session_180Days_Prep:

Load Distinct

UserId_Key,

LogEntryPeriodStart,

[_proxySessionPackage]

Resident LogContent

Where num(LogEntryPeriodStart)>num(today()-180); //Formatting verification to make sure that condition works

User_Session_180Days:

Load

        UserId_Key,

Count([_proxySessionPackage]) as Bucket_180

Resident User_Session_180Days_Prep

Group by UserId_Key;

Drop Table User_Session_180Days_Prep;

 NoConcatenate

User_Session_Final:

Load

*,

Text(SubField(UserId,'\',-1)) as UserId_Key

Resident User_Session_180Days;

left join (User_Session_Final)

 Load

*

,

Text(SubField(UserId,'\',-1)) as UserId_Key

Resident User_Session_90Days;

left join (User_Session_Final)

Load

*,

Text(SubField(UserId,'\',-1)) as UserId_Key

Resident User_Session_60Days;

Drop Tables User_Session_60Days, User_Session_90Days,User_Session_180Days;

   drop field UserId from User_Session_Final;

    

[marcus_sommer]

If no code/app were changed and no OS/machine/QV release/migration/configuration has touched the most likely reason for missing data is that they didn't exists anymore - at least not in the specified place + structure.

Before going further I would re-start everything and repeating the task to prevent to look for any temporary issues. 

[Manni_SM]

thanks @marcus_sommer   i tried the way you explained 

when i disable this code showing all session related tables   but when i enable session tables  not visible in datamodel

TempTable_test:

NoConcatenate 

load * ,

Text(SubField(UserId,'\',-1)) as UserId_Key,

Text(SubField(UserId,'\',-1)) as UserId_Key_entitlement

Resident 

LogContent where ObjectId='08406b9d-58d2-4997-b8b7-e5927cabc186';

Drop Table LogContent;

 rename table TempTable_test to LogContent;

[Manni_SM]

@rubenmarin  any suggestions?

[rubenmarin]

I thought Marcus answer was a good one, if it was working and suddenly it stops to work, go step by step to see what have changed, in example: is that ObjectId still beign loaded? maybe it has been published in a new object (instead of updating the old one) and what you search has now a different ObjectId.

[Manni_SM]

object id is same @rubenmarin  above mentioned part of code commented then  it working fine  

[marcus_sommer]

It's not possible from the provided snippets to detect the issue. Your task based of a cascade of multiple steps and in each one may anything respectively the underlying data being different to before.

I would look within document-log to see what really happens - starting with CTRL + F to go the relevant parts before thinking to evaluate all single steps. Further helpful could be if you have also access to an elder log-file and you could compare it against the current one (copy & paste into a AI tool or just using MS Word).

Like hinted I wouldn't surprised if you find that the source-data aren't there anymore.

[rubenmarin · Accepted solution]

Hi, that code loads from LogContent, so you should check if that ObjectId exists in that table, and if not, look where that data is lost, maybe some previous condition is filtering that data.

Or try to comment only the 'where' part to confirm if this filter is the cause.

You can also add an 'exit script' or a 'store' before that code to check the data of LogContent, that it has date for that objectId and UserId_Key and UserId_Key_entitlement don't exist (or it will return a duplicated field error when loading TempTable_test.